目录
一、恢复数据
1、不要挂载并开启数据库:
startup nomount;
2、登录rman: rman target sys/oracle@shycorcl auxiliary sys/oracle@shycorclstd
运安最新的(20201105)rman target sys/1qaz2WSX@mesorclstd auxiliary sys/1qaz2WSX@mesorcl(其中:第一个tns连接字符串mesorclstd 是主库,第二个连接字符串是备库mesorcl)
3、恢复数据 RMAN> duplicate target database for standby from active database nofilenamecheck;
二、查看备库状态
SQL>select status from v$instance;
三、检查、开启、关闭数据库同步:
1、检查Dataguard 状态
SQL>select database_role,protection_mode,protection_level,open_mode from v$database; SQL>select name,open_mode,protection_mode,database_role,switchover_status from v$database;
2、启动dataguard数据库同步-备库执行
SQL>alter database recover managed standby database using current logfile disconnect from session;
SQL> alter system switch logfile;
3、关闭datagurade数据同步-备库执行
SQL>alter database recover managed standby database cancel;
4、主备库检查dataguard 状态【查看archivelog序列号是否一致,下面两种都可以查看】:
SQL>select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log;
SQL>archive log list;【这个命令查看输出current log sequence】
5、检查dataguard日志:
主库日志:tail -100f /home/oracle/app/oracle/diag/rdbms/pri/pri/trace/alert_std.log 备库日志:tail -100f /home/oracle/app/oracle/diag/rdbms/std/std/trace/alert_std.log 其他命令:SQL>select * from v$dataguard_status; 其他命令:SQL>select * from v$diag_info
6、查看日志应用情况:
SYS@std> set pagesize 100 SYS@std> select sequence#,applied from v$archived_log order by 1;
另:ADG的工作原理(ADG备机可以工作在mounted状态下,也可以运行在read-only 状态下;而DG的备机只能运行在mounted状态下)
四、查看主备库信息
1、查看standby启动的DG进程
SQL> select process,client_process,sequence#,status from v$managed_standby;
2、查看数据库的保护模式:
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
3、查看ADG的日志信息
SQL> select * from v$dataguard_status;
五:Open Read Only standby数据库并且开启实时日志应用(备库)
1、关闭并完全打开备库
SQL>shutdown immediate SQL>startup
2、查看备库模式,是否为只读模式
SQL> select database_role,protection_mode,protection_level,open_mode from v$database;
3、查看备库状态
SQL> select process,client_process,sequence#,status from v$managed_standby;
六、主备库开启、关闭顺序
1、启动顺序
(1)监听:先启备库再起主库
#lsnrctl start
(2)先启备库:
sql>startup nomount; sql>alter database mount standby database; sql>alter database recover managed standby database using current logfile disconnect from session;
(3)再启主库: sql>startup
2、关闭顺序
(1)先关主库:
sql>shutdown immediate
(2)再关备库:
sql>alter database recover managed standby database cancel; sql>shutdown immediate;
七、主备库切换
1、原主库(A)操作,切换为新备库:
-
查看switchover_status状态: select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database; 如果switchover_status为TO_STANDBY说明可以直接转换 alter database commit to switchover to physical standby; 如果switchover_status为SESSIONS ACTIVE 则关闭会话 alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
-
shutdown immediate; ORA-01012: not logged on
-
打开数据库,但不挂载 startup nomount;
select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database;
此时查看备库,switchover_status是否为to primary状态,如果是,则可以正常切换。 如果switchover_status是not allowed,则说明主库进行switchover有问题,重新运行上面语句。
-
原主库(A)以备机模式挂载数据库 alter database mount standby database; a、主库执行下句:select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database; 查看switchover_status是否为recovery_needed,如果是,则下一步 b、查看原备库(B)语句:select switchover_status from v$database; switchover_status 是否为to primary,如果是,可以进行切换。如果不是,而是sessions active状态,说明备库有连接,进行备库数据库关闭并挂载重启(startup mount)即可恢复
2、原备库(B)查看并切换为新主库(B)
-
select DATABASE_ROLE from v$database; --备库为PHYSICAL STANDBY 状态,主库为PRIMARY状态
-
select OPEN_MODE,PROTECTION_MODE,PROTECTION_LEVEL,SWITCHOVER_STATUS from v$database; SWITCHOVER_STATUS 如果为not allowed,表示当前备库为不可以做switch的操作,需要等主库进行switchover后就可以switchover操作了。
-
alter database commit to switchover to primary; 如果出现:ORA-01093: alter database close only permitted with no sessions connected 解决: alter database commit to switchover to primary with session shutdown;
-
shutdown immediate;
-
startup;
3、新备库(A)设备库此时为mounted状态,先同步数据,再设置为Open,再同步
SQL> alter database recover managed standby database using current logfile disconnect from session; SQL> alter database recover managed standby database cancel; SQL> alter database open;
4、查看新主备库的角色
select DATABASE_ROLE from v$database;--新备库(A)为PHYSICAL STANDBY 状态,新主库(B)为PRIMARY状态
5、原备库(B)更换为新主库(B)后,此库的archivelog日志可以直接删除,不在此库的rman记录中。
可以使用RMAN>CROSSCHECK ARCHIVELOG ALL;进行查看最新的archivelog,把不在其中的,系统下删除即可。
6、新主\库备库分别执行下列语句,并查看归档日志是否一致,如果一致,则切换成功
新主备库查看序列号:
SQL>select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log;
新主库(B)切换归档
SQL>alter system switch logfile;
新主备库查看:
SQL>select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log;
如果序列号一致即成功。
八、switchover_status概念:
A 如果switchover_status为TO_PRIMARY 说明标记恢复可以直接转换为primary库; alter database commit to switchover to primary ; B 如果switchover_status为SESSION ACTIVE 就应该断开活动会话 alter database commit to switchover to primary with session shutdown; C 如果switchover_status为NOT ALLOWED 说明切换标记还没收到,此时不能执行转换。 D、状态由LOG SWITCH GAP变成了RESOLVABLE GAP,从字面理解是主备库之间存在GAP,于是执行: alter system switch logfile;手动切换归档即可(LLL) E、当主库的SWITCHOVER_STATUS状态为FAILED DESTINATION时,是因为备库不在mount状态下,在备库中:startup mount F、当主库的SWITCHOVER_STATUS状态为RESOLVABLE GAP时,可以shutdown和startup备库,问题可解决。
九、其他维护
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复 alter database recover managed standby database cancel; shutdown immediate;
-----由shutdown模式切换到只读模式------- startup nomount; alter database mount standby database; alter database open read only;
-----由应用日志模式切换到只读模式------- alter database recover managed standby database cancel; -- 取消日志应用 alter database open read only;
startup nomount; alter database mount standby database; alter database recover managed standby database disconnect from session; -- 启动日志应用 alter database recover managed standby database using current logfile disconnect from session;
alter database recover automatic standby database;
alter database set standby database to maximize protection; alter database set standby database to maximize availability; alter database set standby database to maximize performancen;
alter database recover managed standby database cancel; alter database recover managed standby database finish; alter database recover managed standby database finish force;
十、切断ADG的关联,划分为两个独立的数据库
在备机执行
切断ADG的关联,将其划分为两个独立的数据库
1:切断MRP的进程
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
2:激活备库
alter database activate standby database
3:重启(如果需要彻底的清除,可以把所有dg配置的参数去掉)
shutdown immediate startup
十一、如果主备库按上述独立后,则需要备库重新恢复数据(目前通过此方式进行)
1、rman恢复数据
SYS > startup nomount; [oracle@std ~]$ rman target sys/Hdsoft123@mesorcl auxiliary sys/Hdsoft123@mesorclstd
2、备机开启同步
SQL>alter database recover managed standby database using current logfile disconnect from session;
十一、ADG停止同步
在实际生产系统中,通常有这样的场景,例如在系统维护日,对主库进行大量的业务更新,会有大量的DML操作;
为了避免主库中的业务更新对备库造成影响,可以暂停主库对备库的日志传输,这样的话,如果主库的更新出现问题,备库还保留一份完整的数据镜像,可以执行 failover(失败切换)。
1、停止同步:
主库:
(1)、在主库上面查看备份的归档路径
SQL> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------------------------------------------------------------------------------------------
log_archive_dest_2 string SERVICE=flame ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mesorcl
SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE
(2)、通过设置 defer 参数来暂停主库对备库的日志传输
SQL> alter system set log_archive_dest_state_2 = 'defer';
(3)、执行归档日志切换测试,查看备机是否不再同步
SQL> alter system switch logfile; System altered.
备库:
停止同步数据:
SQL> alter database recover managed standby database cancel;
2、恢复数据同步
备库:
SQL>alter database recover managed standby database using current logfile disconnect from session;
主库:
恢复主库到备份的日志传输,暂停日志传输后的归档日志会全部传输到备库,不会导致备库的归档日志gap
SQL> alter system set log_archive_dest_state_2 = 'enable'; System altered.
档日志切换测试,查看备机
select unique thread#,max(sequence#) over(partition by thread#)last from v$archived_log;
文章评论