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