Oracle-DataGuard-Rman-auxiliary搭建备库

--干净的关闭数据库
SQL> shutdown immediate
--以mount模式启动
SQL> startup mount
--切换到归档模式
SQL> alter database archivelog;
--变更归档路径
 alter system set log_archive_dest_1='location=/alidata/arch';
--开启强制日志
SQL> alter database force logging;
--打开数据库
SQL> alter database open;
--查看归档
SQL> archive log list;
--查看是否为强制日志
SQL> select force_logging from v$database;

SQL> shutdown immediate
SQL> create spfile from pfile;
SQL> startup

SQL> create spfile from pfile;
SQL> startup nomount

RMAN> duplicate target database for standby from active database nofilenamecheck;

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

SQL> select switchover_status,database_role from v$database;
--主库显示：TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭活动的会话再检查
--备库显示：NOT ALLOWED/PHYSICAL STANDBY

SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
DATABASE_ROLE    PROTECTION_MODE      PROTECTION_LEVEL     OPEN_MODE  

------

PRIMARY          MAXIMUM PERFORMANCE  MAXIMUM PERFORMANCE  READ WRITE

SQL> shutdown immediate
SQL> startup
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
SQL> select process,client_process,sequence#,status from v$managed_standby;
SQL> alter database recover managed standby database using current logfile disconnect from session;

SQL> set line 200
SQL> select username,default_tablespace,account_status from dba_users where username='SCOTT';
SQL> alter user scott account unlock;
SQL> conn scott/tiger;
SQL> show user
SQL> create table test001 (id number(10),name varchar2(20));
SQL> begin
     for i in 1..10000 loop
     insert into test001 values (1,'ww');
     end loop;
     end;
     /
SQL> commit;

SQL> conn scott/tiger;
SQL> select * from tab;
SQL> select count(*) from scott.test001;

SQL> select switchover_status,database_role,open_mode from v$database;
--db11-zbdsora显示：TO STANDBY/PRIMARY，如果显示SESSION ACTIVE表示还有活动的会话，需要关闭会话再检查
--db12-zbdsora显示：NOT ALLOWED/PHYSICAL STANDBY

SQL> alter database commit to switchover to physical standby;
SQL> alter database commit to switchover to physical standby with session shutdown;
--如果状态显示SESSION ACTIVE，在切换的时候可以指定with session shutdown 子句强制关闭活动的会话。
SQL> shutdown immediate
SQL> startup mount

SQL> alter database commit to switchover to primary;
SQL> alter database open;

--启用mount状态下的APPLY LOG
SQL> alter database recover managed standby database disconnect from session;
--启用open状态（READ ONLY WITH APPLY）下的APPLY LOG
SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
SQL> select switchover_status,database_role,open_mode from v$database;

SQL> select thread#, low_sequence#, high_sequence# from v$archive_gap;
--如果没有发现明显的gap现象，说明此次的failover不会有数据损失情况。在备库，要进行关闭apply和结束应用动作。
SQL> alter database recover managed standby database cancel;
SQL> alter database recover managed standby database finish;
SQL> alter database commit to switchover to primary;
SQL> alter database open;
SQL> select open_mode, switchover_status from v$database;

1
将备库控制文件拷贝至db11
scp ezdb12:/u01/bak/control01.ctl /alidata/oradata/zbdsora/control01.ctl
cp /alidata/oradata/zbdsora/control01.ctl /u01/app/oracle/fast_recovery_area/zbdsora/
mv /u01/app/oracle/fast_recovery_area/zbdsora/control01.ctl /u01/app/oracle/fast_recovery_area/zbdsora/control02.ctl
1
2
3
恢复对应数据文件至db11，并启动db11-zbdsora到mount状态，应用APPLY模式

SQL> startup mount;
SQL> alter database recover managed standby database disconnect from session;
1
2
启动db11-zbdsora至OPEN状态，并应用APPLY REDO模式

SQL> alter database recover managed standby database cancel;
SQL> alter database open;
SQL> alter database recover managed standby database disconnect from session;
1
2
3
6.开启和关闭DataGuard的流程
6.1.关闭
关闭主库

SQL> shutdown immediate
1
关闭主库监听

lsnrctl stop
1
查询备库是否正在执行重做应用或实时应用。如果MRP0或MRP进程存在，则备库正在应用重做。

SQL> select process, status from v$managed_standby;
1
如果重做应用程序正在运行，停止备库的Redo日志的应用

SQL> alter database recover managed standby database cancel;
1
关闭备库

SQL> shutdown immediate
1
关闭备库监听

lsnrctl stop
1
6.2.开启
启动备库监听

lsnrctl start
1
启动备库到mount状态

SQL> startup mount;
1
开启备库的Redo日志应用

SQL> alter database recover managed standby database using current logfile disconnect from session;
1
启动主库监听

lsnrctl start
1
启动主库

SQL> startup;
1
检查日志情况的SQL语句

select al.thrd "Thread", almax "Last Seq Received", lhmax "Last Seq Applied"
 from (select thread# thrd, max(sequence#) almax
       from v$archived_log
       where resetlogs_change#=(select resetlogs_change# from v$database)
       group by thread#) al,
      (select thread# thrd, max(sequence#) lhmax
       from v$log_history
       where first_time=(select max(first_time) from v$log_history)
       group by thread#) lh
 where al.thrd = lh.thrd;