20171013_数据库新环境后期操作

1 添加NTP时钟同步服务

确认ntp 服务端客户端网络是否有限制

[root@fpyhsdb1 ~]# cat /etc/ntp.conf

server 133.224.230.18
fudge 133.224.230.18 stratum 0

[root@fpyhsdb1 ~]#/etc/init.d/ntpd start

[root@fpyhsdb1 ~]#ntpd -q

ntpq  -p

2 数据库RAC时间和OS时间不一致

http://www.linuxidc.com/Linux/2015-01/112545.htm

 

###

asmca

 AU 4M 

+data01

+data02

+arch

 

###

3 默认dbca建库

sga    5x

pga    1x

memory

db_block_size

db_file_multiblock_read_count

NLS_CHARACTERSET

select userenv('language') from dual;

 

4 控制文件冗余

restore controlfile to '+DATA02' from '+DATA01/fpyhsdb/controlfile/current.256.938602855';
alter system set control_files='+DATA01/fpyhsdb/controlfile/current.256.938602855','+data02/FPYHSDB/CONTROLFILE/current.256.938608617' scope=spfile;

5 日志文件冗余

alter system set db_create_online_log_dest_1='+data01' sid='*';
alter system set db_create_online_log_dest_2='+data02' sid='*';
alter system set log_archive_dest_1='location=+arch';
alter system set log_archive_format='%t_%s_%r.arch' scope=spfile;

ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 SIZE 2048m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 SIZE 2048m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 7 SIZE 2048m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 SIZE 2048m;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;
alter database drop logfile group 4;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 1 SIZE 2048m;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 SIZE 2048m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 2048m;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 SIZE 2048m;

6 数据文件自动扩展关掉

 alter database datafile 1 autoextend off;
 alter database datafile 2 autoextend off;

7 用户密码周期

alter profile default limit password_life_time unlimited; 

8 数据库审计关闭

alter system set audit_trail=none scope=spfile sid='*';

9 数据文件上限制

 alter system set db_files=2000 scope=spfile;

10 数据字典升级

@catbundle.sql psu apply

11 添加备份

[root@jgdqdb1 ~]$ crontab -l

24 19  * * * su - grid -c "sh  /home/grid/shihfa/backup/MD/backup_dg.sh"
00 22  * * 6 su - oracle -c "sh /home/oracle/shihfa/full.sh"
00 10  * * * su - oracle -c "sh /home/oracle/shihfa/archivelog.sh"

11a 磁盘组元数据

backup_dg.sh
#/bin/bash
source /home/grid/.bash_profile
asmcmd md_backup /home/grid/shihfa/backup/MD/dg_backup_`date +%EY%m%d`
#del expired backup
find /home/grid/shihfa/backup/MD -name "dg_backup*" -ctime +30 -type f -exec rm {} \;

11b rman

[root@jgdqdb1 ~]$  pwd

/home/oracle/shihfa

[root@jgdqdb1 ~]$  ls

archive.sh  full.sh

###full.sh

source /home/oracle/.bash_profile
export FILE=`date +%Y%m%d`
log=/rmanbak/log/full-$ORACLE_SID-$FILE.log  <<EOF
run{
allocate channel ch1 type disk maxpiecesize 10G;
allocate channel ch2 type disk maxpiecesize 10G;
allocate channel ch3 type disk maxpiecesize 10G;
allocate channel ch4 type disk maxpiecesize 10G;
allocate channel ch5 type disk maxpiecesize 10G;
configure retention policy to recovery window of 14 days;
backup as compressed backupset database tag 'full_orarpt' format '/rmanbak/%d_%T_%U.full';
crosscheck backupset;
#DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
#delete noprompt archivelog all completed before 'trunc(sysdate)';
delete noprompt expired backup;
report obsolete;
DELETE NOPROMPT OBSOLETE;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
}
EOF
exit;

##archivelog.sh

source /home/oracle/.bash_profile
export FILE=`date +%Y%m%d`
rman target / log=/rmanbak/log/arch-$ORACLE_SID-$FILE.log  <<EOF
run{
allocate channel ch1 type disk maxpiecesize 10G;
allocate channel ch2 type disk maxpiecesize 10G;
allocate channel ch3 type disk maxpiecesize 10G;
allocate channel ch4 type disk maxpiecesize 10G;
allocate channel ch5 type disk maxpiecesize 10G;
configure retention policy to recovery window of 14 days;
backup as compressed backupset archivelog all  format '/rmanbak/%d_%T_%U.arch';
crosscheck backupset;
#DELETE noprompt ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';
#delete noprompt archivelog all completed before 'trunc(sysdate)';
delete noprompt archivelog all completed before 'sysdate-2';
delete noprompt expired backup;
report obsolete;
DELETE NOPROMPT OBSOLETE;
release channel ch1;
release channel ch2;
release channel ch3;
release channel ch4;
release channel ch5;
}
EOF
exit;

11c 数据泵

create directory expdp as '/expdp' ;
grant read,write on directory to public;

jgdqdb2[/home/oracle]$crontab -l   

00  18   * * * su - oracle -c "sh /home/oracle/shihfa/expdp_rm.sh"

jgdqdb2[/home/oracle/shihfa]$cat rm.par

#rm.par
directory=EXPDP
schemas=rm
content=all
parallel=16
cluster=n

jgdqdb2[/home/oracle/shihfa]$cat expdp_rm.sh

#!/bin/bash
source /home/oracle/.bash_profile
FILE=`date +%Y%m%d`
expdp system/oracle dumpfile=`date +%Y%m%d`_%U.dmp  logfile=$FILE.log parfile=/home/oracle/shihfa/rm.par

if [ "tail -n 1 `date +%Y%m%d`.log | grep successfully |awk '{print $3}'"="successfully" ]; then
  find /expdp -name "*.log" -atime +3 -exec rm {} \;
  find /expdp -name "*.dmp" -atime +3 -exec rm {} \;
  echo "`date +%Y%m%d` successful exp ,and delete the backup the day before yesterday" >> /expdp/history
else
  echo "`date +%Y%m%d` backup is not successful ! do nothing" >> /expdp/history
fi