总觉得使用windows跑oracle是不靠谱的事情,可以这个世界上总有很多人喜欢做类似这样的事情,对于数据库比较常见的两件事情:rman和删除dg备库归档日志,在linux/unix平台上使用shell实现很简单,可是跑到win里面,就变的烦了,不是因为其麻烦,而是因为用的人少,不知道怎么下手处理该事情,我编写了简单的实现初级功能的win下面rman备份和删除备库归档日志脚本,供大家参考,也更加欢迎朋友提出来更加好的处理方法(win是真心的不懂)
rman备份脚本

--backup_oracle.bat文件
rman target / cmdfile=D:\backup\rman\backup_db.rman 
log=d:/backup/rman/logfile/rmanlog%date:~0,4%%date:~5,2%%date:~8,2%.log

--backup_db.rman文件
CONFIGURE RETENTION POLICY TO REDUNDANCY = 2;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
backup filesperset = 5 as compressed backupset database format 'd:/backup/rman/full_%U.rman';
sql 'alter system archive log current';
backup  filesperset = 50 as compressed backupset archivelog  all format 'd:/backup/rman/arch_%U.rman'  delete input;
DELETE noprompt OBSOLETE;
crosscheck backup;
delete noprompt expired backup;
backup  format 'd:/backup/rman/ctl_%U.rman' current controlfile;
backup spfile format 'd:/backup/rman/spfile_%U.rman' ;
exit;

backup_oracle.bat文件加入到计划任务即可

删除dg备库归档日志(已经应用)

--delete_dg_archivelog.bat
rem 注意修改 部署目录
cd D:\win_xifenfei
d:
rem 注意delete_archive.sql 查询是否有记录

echo delete archivelog staring > delete_archivelog.bak
sqlplus / as sysdba @delete_archive.sql
echo rman target / cmdfile=rman_checkcross.rman>>delete_archivelog.bat 
delete_archivelog.bat >>delete_dg_archivelog_%DATE:~0,4%%DATE:~5,2%%DATE:~8,2%".log
exit

--delete_archive.sql
set lines 150
col name for a150
set pagesize 0 feedback off verify off heading off echo off
spool delete_archivelog.bat
select 'del '||name from v$archived_log where APPLIED='YES' AND NAME IS NOT NULL and DEST_ID=1;
spool off
exit;

--rman_checkcross.rman
crosscheck archivelog all;
delete noprompt expired archivelog all;
exit

delete_dg_archivelog.bat加入到计划任务即可

RDBMS tnsnames.ora配置
在主备库的数据库的tns中均增加如下配置

q9dbdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9db02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

q9adgdg =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg01-dg-vip)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = q9adg02-dg-vip)(PORT = 1522))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = q9db)
    )
)

主库修改参数配置

alter system set DB_UNIQUE_NAME=q9db scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9adg1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db_adg';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9dbdg';
alter system set fal_server='q9adgdg';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

备库修改参数配置

alter system set DB_UNIQUE_NAME=q9db_adg scope=spfile;
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(q9db,q9db_adg)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+arch/q9db/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=q9db_adg';
alter system set LOG_ARCHIVE_DEST_2='SERVICE=q9db1dg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=q9db';
alter system set standby_file_management='AUTO' sid='*' scope=both;
alter system set fal_client='q9adgdg';
alter system set fal_server='q9dbdg';
alter system set service_names='q9db';
alter system set db_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;
ALTER SYSTEM SET log_file_name_convert='+DATA','+DATA' SCOPE=SPFILE;

主库增加standby redo log

alter database add standby logfile THREAD 1 group 51 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 1 group 61 '+DATA' size 512m;

alter database add standby logfile THREAD 2 group 71 '+DATA' size 512m;
…………
alter database add standby logfile THREAD 2 group 81 '+DATA' size 512m;

主库修改强制日志

SQL> ALTER DATABASE FROCE LOGGING;

RMAN备份主库

rman target /
run{
configure device type disk parallelism 16;
backup database filesperset = 10  format '/q9bak/df_%U';
backup current controlfile format '/q9bak/ctl_%U';
}

创建standby controlfile

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/q9bak/stb_ctl';

还原standby controlfile

ASMCMD> cp /q9bak/stb_ctl control01.ctl
copying /q9bak/stb_ctl -> +data/Q9DB/control01.ctl
ASMCMD> cp /q9bak/stb_ctl control02.ctl
copying /q9bak/stb_ctl -> +arch/q9db/control02.ctl

还原备库

SQL>startup mount;

rman target / nocatalog <<EOF
run{
configure default device type to disk;
configure device type disk parallelism 16;
restore database;
}
<<EOF

修改备库文件名

RMAN>catalog start with '+DATA/Q9DB/DATAFILE/';
RMAN> switch database to copy;

清理redo

--清理redo(所有的group#)
SQL> select group# from v$log; 
SQL> alter database clear logfile group 1; 
……

--清理standby redo(所有的group#)
SQL> select group# from v$standby_log; 
SQL> alter database clear logfile group 51; 
……

开启备库实时应用模式

SQL> alter database recover managed standby database using current logfile disconnect from session;

备库注册DB服务(oracle用户)

srvctl add database -d q9db_adg -o $ORACLE_HOME 
srvctl add instance -d q9db_adg -i q9db1 -n q9adg01
srvctl add instance -d q9db_adg -i q9db2 -n q9adg02
srvctl modify database -d q9db_adg -a DATA,ARCH

11G RAC TO 11G RAC ADG切换过程
主库准备工作

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PRIMARY          READ WRITE
         1 PRIMARY          READ WRITE

[oracle@q9db02 ~]$ srvctl stop instance -d q9db -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         1 PRIMARY          READ WRITE

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

备库准备工作

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         2 PHYSICAL STANDBY READ ONLY WITH APPLY
         1 PHYSICAL STANDBY READ ONLY WITH APPL

[oracle@q9adg02 ~]$ srvctl stop instance -d q9db_adg -i q9db2

SQL> select inst_id,database_role,OPEN_MODE from  gv$database;

   INST_ID DATABASE_ROLE    OPEN_MODE
---------- ---------------- --------------------
         1 PHYSICAL STANDBY READ ONLY WITH APPL


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> CREATE RESTORE POINT SWITCHOVER_START_GRP GUARANTEE FLASHBACK DATABASE;

Restore point created.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

主库切换日志,观察备库

--主库
SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

--备库
[oracle@q9adg01 trace]$ tail -f alert_q9db1.log 
Tue Jun 25 15:35:27 2013
RFS[10]: Selected log 52 for thread 1 sequence 4777 dbid 844605368 branch 817913807
Tue Jun 25 15:35:28 2013
Archived Log entry 4889 added for thread 1 sequence 4776 ID 0x3545ffea dest 1:
Tue Jun 25 15:35:28 2013
Media Recovery Waiting for thread 1 sequence 4777 (in transit)
Tue Jun 25 15:35:28 2013
RFS[11]: Selected log 72 for thread 2 sequence 1630 dbid 844605368 branch 817913807
Recovery of Online Redo Log: Thread 1 Group 52 Seq 4777 Reading mem 0
  Mem# 0: +DATA/q9db_adg/onlinelog/group_52.1564.818724635
Media Recovery Waiting for thread 2 sequence 1630 (in transit)
Recovery of Online Redo Log: Thread 2 Group 72 Seq 1630 Reading mem 0
  Mem# 0: +DATA/q9db_adg/onlinelog/group_72.1575.818724653
Tue Jun 25 15:35:30 2013
Archived Log entry 4890 added for thread 2 sequence 1629 ID 0x3545ffea dest 1:

几乎同步进行表示主备日志传输应用正常

主库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO STANDBY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;


Database altered.

备库切换

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO PRIMARY

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

继续处理主库

SQL> shutdown immediate
ORA-01092: ORACLE instance terminated. Disconnection forced
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Data Mining
and Real Application Testing options
[oracle@q9db01 ogg]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jun 25 14:13:58 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+11 bytes
Fixed Size                  2236968 bytes
Variable Size            2.5770E+10 bytes
Database Buffers         1.3422E+11 bytes
Redo Buffers              352468992 bytes
Database mounted.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

清理快照

--主库
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

--备库
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.6034E+11 bytes
Fixed Size                  2236968 bytes
Variable Size            2.7380E+10 bytes
Database Buffers         1.3261E+11 bytes
Redo Buffers              352468992 bytes
Database mounted.
SQL> DROP RESTORE POINT SWITCHOVER_START_GRP;

Restore point dropped.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;


Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


Database altered.

SQL> alter database open;

Database altered.

SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Database altered.

启动主备另外节点

--主库
[oracle@q9db01 ~]$ srvctl start instance -d q9db -i q9db2
--备库
[oracle@q9adg02 ~]$ srvctl start instance -d q9db_adg -i q9db2

补充说明:如果出现日志切换暂时不能传输

备库执行(因为重启动态监听没有马上别识别)
alter system register;
主库执行
alter system set log_archive_dest_state_2=enable;

在一个dg环境中，配置的是实时同步,需要增加主库的redo大小和组数,本来是一个很简单的问题,解决思路是：先备库增加standby redo删除老standby redo,然后主库增加redo删除老redo,备库增加新redo删除老redo,最后主库增加standby redo。但是在实施过程中，遇到了一些细节性的问题，主要是学习到了log_file_name_convert如果不配置,将导致备库redo 文件不能被删除

standby redo log管理
增加standby redo log

SQL> alter database add standby logfile group 8 ('/data/oradata/wasudb/st_redo08.log') size 200M; 
alter database add standby logfile group 8 ('/data/oradata/wasudb/st_redo08.log') size 200M
*
ERROR at line 1:
ORA-01156: recovery in progress may need access to files

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database add standby logfile group 9 ('/data/oradata/wasudb/st_redo09.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 10 ('/data/oradata/wasudb/st_redo10.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 11 ('/data/oradata/wasudb/st_redo11.log') size 200M; 

Database altered.

SQL> alter database add standby logfile group 12 ('/data/oradata/wasudb/st_redo12.log') size 200M; 

Database altered.

删除standby redo log

SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00261: log 4 of thread 1 is being archived or modified
ORA-00312: online log 4 thread 1: '/data/oradata/wasudb/st_redo04.log'


SQL> alter database drop logfile group 5;;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

--在主库多次执行switch logfile

SQL>  alter database drop logfile group 4;

Database altered.

主库redo log 管理
增加redo log

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除redo log

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.
--这里涉及到多次switch logfile,需要确定redo是inactive才能够删除

备库redo log管理
增加redo log

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m; 
alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m
*
ERROR at line 1:
ORA-01275: Operation ADD LOGFILE is not allowed if standby file management is automatic.


SQL>  alter system set standby_file_management=manual;

System altered.

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除redo log

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL>  ALTER DATABASE CLEAR LOGFILE GROUP 1;
 ALTER DATABASE CLEAR LOGFILE GROUP 1
*
ERROR at line 1:
ORA-19527: physical standby redo log must be renamed
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL> show parameter NAME_CONVERT

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
log_file_name_convert                string

SQL> alter system set log_file_name_convert='/data/oradata/wasudb','/data/oradata/wasudb' scope=spfile;

System altered.

SQL>  alter system set db_file_name_convert='/data/oradata/wasudb','/data/oradata/wasudb' scope=spfile;

System altered.

--重启数据库

SQL> show parameter file_name_convert;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      /data/oradata/wasudb, /data/or
                                                 adata/wasudb
log_file_name_convert                string      /data/oradata/wasudb, /data/or
                                                 adata/wasudb

SQL> alter database drop logfile group 1;
alter database drop logfile group 1
*
ERROR at line 1:
ORA-01624: log 1 needed for crash recovery of instance wasudb (thread 1)
ORA-00312: online log 1 thread 1: '/data/oradata/wasudb/redo01.log'

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 1;

Database altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 2;

Database altered.

SQL> alter database drop logfile group 2;

Database altered.

SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;

Database altered.

SQL> alter database drop logfile group 3;

Database altered.

主库standby redo log管理
增加standby redo

SQL> alter database add logfile group 21 ('/data/oradata/wasudb/redo21.log') size 200m;

Database altered.

SQL> alter database add logfile group 22 ('/data/oradata/wasudb/redo22.log') size 200m; 

Database altered.

SQL> alter database add logfile group 23 ('/data/oradata/wasudb/redo23.log') size 200m; 

Database altered.

SQL> alter database add logfile group 24 ('/data/oradata/wasudb/redo24.log') size 200m;

Database altered.

删除standby redo

SQL> alter database drop logfile group 4;

Database altered.

SQL> alter database drop logfile group 5;

Database altered.

SQL> alter database drop logfile group 6;

Database altered.

SQL> alter database drop logfile group 7;

Database altered.

后续工作

SQL> alter system set standby_file_management=auto;

System altered.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

至此修改dataguard环境中的redo大小和增加redo组数的操作基本完成.在这里主要修正了自己以前对log_file_name_convert的认识，我以前以为如果我的主备库redo对应的目录一致不用配置该参数,今天通过查询MOS发现从10.2开始数据库为了能实现dg的快速切换在mrp启动的时候会去尝试清理备库redo,如果备库没有redo,或者log_file_name_convert配置不正确导致不能正常执行这个清理工作,数据库就会报ORA-19527,特别是在mrp进程启动之时.对于本次出现执行CLEAR LOGFILE命令也出现该问题,确实有点过犹不及了.这里也就是提醒我们:就算redo file,datafile主备位置相同,也建议配置log_file_name_convert和db_file_name_convert参数,提高dg健壮性.