【DataGuard】Physical standby 与 Snapshot standby 之间切换

 

环境：DataGuard Physical Standby

主库：Oracle RAC 11.2.0.3

备库：Oralce 单实例 11.2.0.3

 

Physical standby 状态查询

 

1.查询主库状态

SQL> select open_mode,database_role,switchover_status from gv$database;

OPEN_MODE       DATABASE_ROLE    SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ WRITE        PRIMARY             TO STANDBY

 

2.查询备库状态

SQL> select open_mode,database_role,switchover_status from v$database;

OPEN_MODE       DATABASE_ROLE    SWITCHOVER_STATUS

-------------------- ---------------- --------------------

READ ONLY WITH APPLY PHYSICAL STANDBY NOT ALLOWED

 

3.查询备库日志应用情况

SQL> select THREAD#,SEQUENCE#,FIRST_TIME,NEXT_TIME,APPLIED from v$archived_log;

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- ---------- --------- --------- ---------

          1      153 03-AUG-14 03-AUG-14 NO

          1      154 31-JUL-14 31-JUL-14 YES

          1      155 31-JUL-14 31-JUL-14 YES

          1      156 31-JUL-14 31-JUL-14 YES

          1      157 31-JUL-14 31-JUL-14 YES

          1      158 31-JUL-14 31-JUL-14 YES

 

4.主库切换日志

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

 

5.备库查看日志应用

   THREAD#  SEQUENCE# FIRST_TIM NEXT_TIME APPLIED

---------- ---------- --------- --------- ---------

          1      154 31-JUL-14 31-JUL-14 YES

          1      155 31-JUL-14 31-JUL-14 YES

          1      156 31-JUL-14 31-JUL-14 YES

          1      157 31-JUL-14 31-JUL-14 YES

          1      158 31-JUL-14 31-JUL-14 YES

          1      159 31-JUL-14 31-JUL-14 YES

          1      160 31-JUL-14 31-JUL-14 YES

 

说明：physical standby 状态正常

 

Physical standby 切换 Snapshot standby

 

1.      停止日志应用并关闭全部实例

SQL> alter database recover managed standby database cancel;

Database altered.

 

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

2.      登录一个实例，启动备库到mount状态，切换snapshot standby

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                      2232960 bytes

Variable Size              524291456 bytes

Database Buffers      306184192 bytes

Redo Buffers              2396160 bytes

Database mounted.

 

SQL> alter database convert to snapshot standby;

alter database convert to snapshot standby

*

ERROR at line 1:

ORA-38784: Cannot create restore point 'SNAPSHOT_STANDBY_REQUIRED_08/03/2014

20:07:46'.

ORA-38786: Recovery area is not enabled.

 

错误：因为没有使用闪回区

 

创建闪回区

SQL> alter system set db_recovery_file_dest_size=10G;

System altered.

 

SQL> alter system set db_recovery_file_dest='/u01/app/flashback';

System altered.

 

重新执行切换snapshot standby

SQL> alter database convert to snapshot standby;

Database altered.

 

3.      重新开启备库

SQL> alter database open ;

Database altered.

 

4. 查询备库状态

SQL> select open_mode, database_role from gv$database ;

OPEN_MODE       DATABASE_ROLE

-------------------- ----------------

READ WRITE        SNAPSHOT STANDBY

 

测试 Snapshot standby

 

测试expdp,impdp导入导出

[oracle@rac-dg log]$ expdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=SCOTT_local.dmp schemas=SCOTT

Export: Release 11.2.0.3.0 - Production on Sun Aug 3 20:53:32 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '+DATA'

ORA-06512: at "SYS.DBMS_LOB", line 724

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3964

ORA-06512: at line 1

 

[oracle@rac-dg log]$ impdp \'/ as sysdba\' directory=DATA_PUMP_DIR dumpfile=test999.dmp schemas=test remap_tablespace=TEST_SPACE:TEST

Import: Release 11.2.0.3.0 - Production on Sun Aug 3 20:51:20 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '+DATA'

ORA-06512: at "SYS.DBMS_LOB", line 724

ORA-06512: at "SYS.DBMS_DATAPUMP", line 3964

ORA-06512: at line 1

 

查询报错的数据文件

SQL>  select file_id,file_name from dba_data_files where file_id=201;

no rows selected

 

查询临时表空间数据文件

SQL> select file_name,file_id from dba_temp_files;

select file_name,file_id from dba_temp_files

                              *

ERROR at line 1:

ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

ORA-01110: data file 201: '+DATA'

 

说明：rman 在duplicate创建备库时没有正确创建临时表空间

 

 

重新创建临时表空间

SQL> CREATE TEMPORARY TABLESPACE temp001 tempfile '/u01/app/oracle/oradata/ENMODG/datafile/temp001.dbf' size 5G AUTOEXTEND ON  next 1M;

Tablespace created.

 

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp001;

Database altered.

 

Snapshot standby 切换回 Physical standby

 

1.      停止实例

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

2.      启动到mount状态，重新切换为physical standby。

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                      2232960 bytes

Variable Size              570428800 bytes

Database Buffers      260046848 bytes

Redo Buffers               2396160 bytes

Database mounted.

 

SQL> alter database convert to physical standby;

Database altered.

 

3.      重启全部实例

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

 

SQL> startup mount;

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area  835104768 bytes

Fixed Size                      2232960 bytes

Variable Size              570428800 bytes

Database Buffers      260046848 bytes

Redo Buffers               2396160 bytes

Database mounted.

 

SQL> alter database open read only;

Database altered.

 

4.      查询备库状态

SQL> select open_mode, database_role from gv$database ;

OPEN_MODE       DATABASE_ROLE

-------------------- ----------------

READ ONLY          PHYSICAL STANDBY

 

5.      备库开启日志应用

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

Database altered.

 

6.      查询备库状态

SQL> select open_mode, database_role from gv$database ;

OPEN_MODE       DATABASE_ROLE

-------------------- ----------------

READ ONLY WITH APPLY PHYSICAL STANDBY



          ---------------------end--------------------

                                     
                        DBA_建瑾
                        2014.8.24

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29324876/viewspace-1256932/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29324876/viewspace-1256932/