Oracle 11.2.0.4 physical dataguard和snapshot dataguard切换

千家信息网最后更新 2025年01月19日Oracle 11.2.0.4 physical dataguard和snapshot dataguard切换平台：
suse 11sp4
Oracle11.2.0.4

适用于数据迁移，灾备演练，详细过程如下：
------convert physical standby to snapshot database
1、Stop Redo Apply, if it is active.
2、Ensure that the database is mounted, but not open.
3、Ensure that a fast recovery area has been configured. It is not necessary for flashback database to be enabled.
4、Issue the following SQL statement to perform the conversion:
SQL> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;


select process,status,group#,thread#,sequence#,blocks from v$managed_standby;
select FLASHBACK_ON from v$database;
sys@ORA11G> select name, LOG_MODE, FLASHBACK_ON,OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;


NAME LOG_MODE FLASHBACK_ON OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
--------- ------------ ------------------ -------------------- ---------------- -------------------- ------------------------------
ORA11G ARCHIVELOG NO READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED oradg




sys@ORA11G> alter database recover managed standby database cancel;


Database altered.


sys@ORA11G> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA11G> startup mount
ORACLE instance started.


Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 553650072 bytes
Database Buffers 1577058304 bytes
Redo Buffers 4923392 bytes
Database mounted.
sys@ORA11G> select process,status,group#,thread#,sequence#,blocks,BLOCK#,DELAY_MINS,ACTIVE_AGENTS from v$managed_standby;
sys@ORA11G> alter system set db_recovery_file_dest_size=200m;


System altered.
sys@ORA11G> ho mkdir /oracle/11.2.0.4/oradata/oradg/flash


sys@ORA11G> alter system set db_recovery_file_dest='/oracle/11.2.0.4/oradata/oradg/flash';


System altered.


sys@ORA11G> ALTER DATABASE CONVERT TO SNAPSHOT STANDBY;


Database altered.


sys@ORA11G> select name, LOG_MODE, FLASHBACK_ON,OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;


NAME LOG_MODE FLASHBACK_ON OPEN_MODE DATABASE_ROLE SWITCHOVER_STATUS DB_UNIQUE_NAME
--------- ------------ ------------------ -------------------- ---------------- -------------------- ------------------------------
ORA11G ARCHIVELOG RESTORE POINT ONLY MOUNTED SNAPSHOT STANDBY NOT ALLOWED oradg


sys@ORA11G> alter database open;


Database altered.
sys@ORA11G> select * from v$flash_recovery_area_usage;


FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 95.24 0 2
FOREIGN ARCHIVED LOG 0 0 0


sys@ORA11G> select name,space_limit,space_used,number_of_files from v$recovery_file_dest
2 ;


NAME SPACE_LIMIT SPACE_USED NUMBER_OF_FILES
-------------------------------------------------- ----------- ---------- ---------------
/oracle/11.2.0.4/oradata/oradg/flash 110100480 104857600 2


---conver to physical




sys@ORA11G> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@ORA11G> startup mount;
ORACLE instance started.


Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 553650072 bytes
Database Buffers 1577058304 bytes
Redo Buffers 4923392 bytes
Database mounted.
sys@ORA11G> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;


Database altered.


sys@ORA11G> shutdown immediate
ORA-01507: database not mounted




ORACLE instance shut down.
sys@ORA11G> startup
ORACLE instance started.


Total System Global Area 2137886720 bytes
Fixed Size 2254952 bytes
Variable Size 553650072 bytes
Database Buffers 1577058304 bytes
Redo Buffers 4923392 bytes
Database mounted.
Database opened.
sys@ORA11G> select name, LOG_MODE, FLASHBACK_ON,OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;


NAME LOG_MODE FLASHBACK_ON OPEN_MODE DATABASE_ROLE
-------------------------------------------------- ------------ ------------------ -------------------- ----------------
SWITCHOVER_STATUS DB_UNIQUE_NAME
-------------------- ------------------------------
ORA11G ARCHIVELOG NO READ ONLY PHYSICAL STANDBY
RECOVERY NEEDED oradg




sys@ORA11G> select * from v$flash_recovery_area_usage;


FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE 0 0 0
REDO LOG 0 0 0
ARCHIVED LOG 0 0 0
BACKUP PIECE 0 0 0
IMAGE COPY 0 0 0
FLASHBACK LOG 0 0 0
FOREIGN ARCHIVED LOG 0 0 0


7 rows selected.


sys@ORA11G> recover MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Media recovery complete.
sys@ORA11G>
sys@ORA11G>
sys@ORA11G>
sys@ORA11G> select name, LOG_MODE, FLASHBACK_ON,OPEN_MODE, database_role, SWITCHOVER_STATUS, db_unique_name from v$database;


NAME LOG_MODE FLASHBACK_ON OPEN_MODE DATABASE_ROLE
-------------------------------------------------- ------------ ------------------ -------------------- ----------------
SWITCHOVER_STATUS DB_UNIQUE_NAME
-------------------- ------------------------------
ORA11G ARCHIVELOG NO READ ONLY WITH APPLY PHYSICAL STANDBY
NOT ALLOWED oradg