data guard 归档管理 (primary)

 

主库归档管理：

1、 用SHELL脚本，取归档已被APP的。然后删除已经APP的归档。

2、 用RMAN 备份策略管理：oracle 自带的策略：

RMAN> configure archivelog deletion policy to applied on standby;

有报错，解决方法：

SQL>alter system set "_log_deletion_policy"=ALL scope=spfile sid='*';

 

Oracle 在删除已经备份完的归档时，会看下是否已经被APP 没有APP的就不删除。

 

3、利用RMAN备份的时间点和删除时间点分离，这样就给了数据库一段时间内的归档冗余，在这段时间内备库应该可以把归档应用了这个时间点可以尽量长一些。

这个方案虽然不够严谨，但是可以解决一些实际问题。

 

下面是方案：

4点 full.sh

3点 delete.sh

 

 

 

[ora@dg-pp ~]$ cat full.sh

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

       . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

unset USERNAME

 

#--------------------------------------

# Set for Oracle10g Install 20101015;

#--------------------------------------

trap " " 0 1 2 3 5 9 15

trap clear 0

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=research

export ORACLE_TERM=xterm

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64/

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export LD_ASSUME_KERNEL=2.6.9

export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LC_CTYPE=en_US.UTF-8

export LC_ALL=en_US.UTF8

export LANG=en_US.UTF8

umask 022

#

if [ $USER = "ora" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

fi

#------------------- Set Over

 

全库备库，不删除多余的备份和归档日志。

rman target / nocatalog log=/rman/log/rman_db_full.log <<EOF

run {

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

    backup ( database

                    include current controlfile

                    filesperset=2

                    format '/rman/data/full_data_%s_%p'

                    tag="fullbackup_data");

  backup ( archivelog all

                    filesperset=4

                    format '/rman/arch/arch_%s_%p'

                    tag="fullbackup_arch");

release channel d1;

release channel d2;

release channel d3;

crosscheck backup;

}

exit

EOF

 

0级数据压缩备份，不删除多余的备份和归档。

rman target / nocatalog log=/u01/rman/log/rman_db_level0.log <<EOF

run {

allocate channel d1 type disk;

allocate channel d2 type disk;

allocate channel d3 type disk;

backup ascompressed backupset incremental level 0database filesperset 4 format

'/u01/rman/data/level0_%d_%s_%p_%u_%T.bak'include current controlfile;

sql 'alter system archive log current';

backup archivelog allfilesperset 10 format '/u01/rman/arch/arc_%d_%s_%p_%u_%T.bak';

release channel d1;

release channel d2;

release channel d3;

crosscheck backup;

}

exit

EOF

 

 

[ora@dg-pp ~]$cat delete.sh

# .bash_profile

 

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

       . ~/.bashrc

fi

 

# User specific environment and startup programs

 

PATH=$PATH:$HOME/bin

 

export PATH

unset USERNAME

 

#--------------------------------------

# Set for Oracle10g Install 20101015;

#--------------------------------------

trap " " 0 1 2 3 5 9 15

trap clear 0

export TMP=/tmp

export TMPDIR=$TMP

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1

export ORACLE_SID=research

export ORACLE_TERM=xterm

export PATH=/usr/sbin:$PATH

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/usr/X11R6/lib64/

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

export LD_ASSUME_KERNEL=2.6.9

export NLS_LANG="AMERICAN_AMERICA.ZHS16GBK"

export TNS_ADMIN=$ORACLE_HOME/network/admin

export LC_CTYPE=en_US.UTF-8

export LC_ALL=en_US.UTF8

export LANG=en_US.UTF8

umask 022

#

if [ $USER = "ora" ]; then

if [ $SHELL = "/bin/ksh" ]; then

ulimit -p 16384

ulimit -n 65536

else

ulimit -u 16384 -n 65536

fi

fi

#------------------- Set Over

 

rman target / nocatalog log=/u01/rman/log/rman_db_delete.log <<EOF

run {

crosscheck backup;

delete noprompt expired backup ;

delete noprompt obsolete;}

exit

EOF

 

 

 

附表：http://docs.oracle.com/cd/B19306_01/server.102/b14239/rman.htm

【10g版本以后，使用CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY; 可以确保standby 应用过的归档日志，再被删除。
使用范例：
When backups of archived redo log files are taken on the primary database:
1.Issue the following command on the standby database:
CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
2.Issue the following command on the primary database:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

这里有个警告， 解决方法,执行如下命令：
SQL>alter system set "_log_deletion_policy"=ALL scope=spfile sid='*';
设置该参数以后，DB 需要重启。】