利用Flashback将备库激活为读写模式试验及ORA-01665解决

背景:因需要部署灾备环境的应用,而adg备库是只读模式,需要暂时启动到读写模式,验证应用的可用性

 

环境:

主Oracle 11gr2 rac

备:Oracle 11g r2 单节点

主备采用lgwr+async模式传输

 

主库操作:

 1> alter system switch logfile;

 

备库操作:

 2>需要归档应用

   alter database recover managed standby database cancel;

 3> 如果没有,则需先开启flashback database功能

  设置闪回日志路径

alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/' scope=spfile;

alter system set db_recovery_file_dest_size=10G;

--设置保留时间，单位分钟,默认1440

alter system set db_flashback_retention_target=1440;

shutdown immediate;

startup mount;

alter database flashback on;

alter database open;

ps -ef |grep rvw

 

4>创建还原点

 create restore point restore_point_dg guarantee flashback database;

主库操作:

5>将归档目的设置为defer,禁止传输

alter system set log_archive_dest_state_3=defer;

 

将备库激活为read/wrtie

6>当前状态

select open_mode from v$database;

read only

激活备库

alter database activate standby database;

可以看到已经resetlog打开库了

而且状态变为mounted了

MOS上对activate备库的解释 ID 149286.1

再次打开数据库alter database open;

状态变为可读写了

---注意列oldest_flashback_time说明了允许返回的最早的时间点

SQL>  select oldest_flashback_scn old_flhbck_scn,oldest_flashback_time old_flhbck_tim,

     retention_target rete_trgt,flashback_size/1024/1024 flhbck_siz,

     estimated_flashback_size/1024/1024 est_flhbck_size

     from v$flashback_database_log;

OLD_FLHBCK_SCN OLD_FLHBC  RETE_TRGT FLHBCK_SIZ EST_FLHBCK_SIZE

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

      42696974 04-NOV-15       1440        800      1422.46875

SQL> set linesize 10000

SQL> select * from V$RESTORE_POINT;

       SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME                                                                        RESTORE_POINT_TIME                                                      PRE NAME

  42696974                     2 YES    419430400 04-NOV-15 04.52.15.000000000 PM                                                    YES  RESTORE_POINT_DG

 

主库操作，测试恢复备库后数据能否传输过去

7>创建测试数据

create table lsl.test1(a number);

insert into lsl.test1 values(1);

insert into lsl.test1 values(2);

insert into lsl.test1 values(3);

commit;

alter system switch logfile;

 

备库操作

备库恢复到dg备库环境

8>只能在mount状态下闪回

SQL> startup mount force;

SQL> flashback database to restore point restore_point_dg;

SQL> shutdown immediate;

SQL> startup nomount;

居然不认识了啊!!!!

SQL> alter database mount standby database;

ORA-01665: control file is not a standby control file

 后记:通过flashback还原failover备库试验,觉得这里做一次主备转换应可以避免这个错误

    alter database convert to physical standby;

 

9>主库创建备库控制文件

alter database create standby controlfile as '/home/oracle/std.ctl';

10>

拷贝到备库

[oracle@W1 ~]$ scp std.ctl 192.168.15.3:/u01/app/oracle/oradata/bbpay/current

 

11>再次可以启动到mount状态

SQL> alter database mount standby database;

 

此时备库是无法打开的

SQL> alter database open;

ERROR at line 1:

ORA-10458: standby database requires recovery

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/bbpay/system.256.882555673'

 

12>干脆先应用下日志看

主库:

alter system set log_archive_dest_state_3=enable;

备库:

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

 

备库老是报告如下错误,因为控制文件是从主库拷过来的,而在备库路径需要改变的,否则恢复找不到文件的

Additional information: 3

Errors in file /u01/app/oracle/diag/rdbms/bbpay/bbpay/trace/bbpay_dbw0_14224.trc:

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

ORA-01110: data file 15: '/u01/app/oracle/oradata/bbpay/sea.550.890407539'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

 

根据alert错误来修改备库的文件路径再恢复看看.

因为备库有些文件以前是通过db_create_file_dest创建的(优先级高),非db_file_name_convert创建的

 

SQL> alter system set standby_file_management=manual;

SQL> alter database rename file '/u01/app/oracle/oradata/bbpay/audfile.314.886776117'

 to

'/u01/app/oracle/oradata/bbpay/BBPAY/datafile/o1_mf_audfile_bxx3g02v_.dbf';

..........

SQL> alter system set standby_file_management=auto;

修改临时文件位置

而后再执行recover操作,这次OK了.

有清理在线日志的操作,开始了恢复操作

 

备库验证主库的测试数据是否续传过来

13> ok了

参考: http://blog.itpub.net/24486203/viewspace-1708777/