oracle 11r2 dg停主库,ORACLE 11gR2 DG(Physical Standby)日常维护01

环境：RHEL 6.4 + Oracle 11.2.0.4

一、主备手工切换

1.1 主库，切换成备库并启动到mount

1.2 备库，切换成主库并启动到open

1.3 新的备库启动日志应用

二、重命名数据文件

2.1 主库，对应的数据文件或者表空间offline

2.2 主库，操作系统层面重命名数据文件

2.3 主库，重命名数据文件，表空间online

2.4 备库，停止redo应用

2.5 备库，关闭数据库

2.6 备库，操作系统层面重命名数据文件

2.7 备库，启动到mount状态

2.8 备库，重命名数据文件

2.9 备库，重新启动redo应用

Reference

一、主备手工切换

1.1 主库，切换成备库并启动到mount

1.1.1 查看主库状态

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

1.1.2 切换到备库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;

1.1.3 启动到mount状态

SQL> STARTUP MOUNT

1.2 备库，切换成主库并启动到open

1.2.1 查看备库状态

SQL> SELECT OPEN_MODE, DATABASE_ROLE, SWITCHOVER_STATUS, FORCE_LOGGING, DATAGUARD_BROKER, GUARD_STATUS FROM V$DATABASE;

1.2.2 切换到主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

1.2.3 数据库open

SQL> ALTER DATABASE OPEN;

1.3 新的备库启动日志应用

1.3.1 备库mount状态直接启动日志应用

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

1.3.2 备库切换到open状态，启用Real-time query

A physical standby database instance cannot be opened if Redo Apply is active on a mounted instance of that database. Use the following SQL statements to stop Redo Apply, open a standby instance read-only, and restart Redo Apply:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

SQL> ALTER DATABASE OPEN;

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

二、重命名数据文件

2.1 主库，对应的数据文件或者表空间offline

SQL> ALTER TABLESPACE DBS_D_JINGYU OFFLINE;

2.2 主库，操作系统层面重命名数据文件

$ mv /u01/oradata01/JINGYU/datafile/o1_mf_dbs_d_ji_byqy17ph_.dbf /u01/oradata01/JINGYU/datafile/DBS_D_JINGYU01.dbf

2.3 主库，重命名数据文件，表空间online

SQL> ALTER TABLESPACE DBS_D_JINGYU RENAME DATAFILE '/u01/oradata01/JINGYU/datafile/o1_mf_dbs_d_ji_byqy17ph_.dbf' TO '/u01/oradata01/JINGYU/datafile/DBS_D_JINGYU01.dbf';

SQL> ALTER TABLESPACE DBS_D_JINGYU ONLINE;

2.4 备库，停止redo应用

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2.5 备库，关闭数据库

SQL> SHUTDOWN IMMEDIATE;

2.6 备库，操作系统层面重命名数据文件

$ mv /u01/oradata01/BOSTON/datafile/o1_mf_dbs_d_ji_22qjsbps_.dbf /u01/oradata01/BOSTON/datafile/DBS_D_JINGYU01.dbf

2.7 备库，启动到mount状态

SQL> STARTUP MOUNT

2.8 备库，重命名数据文件

2.8.1 需要先调整参数STANDBY_FILE_MANAGEMENT为MANUAL

否则重命名数据文件时会报错ORA-01275: Operation RENAME is not allowed if standby file management is automatic.

SQL> SHOW PARAMETER STANDBY_FILE_MANAGEMENT

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = MANUAL;

2.8.2 重命名数据文件

SQL> ALTER DATABASE RENAME FILE '/u01/oradata01/BOSTON/datafile/o1_mf_dbs_d_ji_22qjsbps_.dbf' TO '/u01/oradata01/BOSTON/datafile/DBS_D_JINGYU01.dbf';

2.8.3 参数调整为AUTO

SQL> ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT = AUTO;

2.9 备库，重新启动redo应用

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

Reference

Oracle® Data Guard Concepts and Administration 11g Release 2 (11.2)