oracle dataguard 维护命令大全及注意事项

-----数据库管理员切记：查看日志

/home/oracle/app/oracle/diag/rdbms/orcl/orcl/trace 

tail -f alert_orcl.log

查询当前数据库的主备状态

select log_mode,open_mode,database_role from v$database;

select log_mode,open_mode,database_role, PROTECTION_MODE,protection_level from v$database;

备库搭建（备库只需要安装好oracle软件，不需要创建数据库。从主库拷贝密码文件、参数文件、控制文件等，然后使用下边的命令可以恢复出备库来）

startup nomount pfile='/home/oracle/app/oracle/product/12.1.0/dbhome_1/dbs/initorcl2.ora'

rman target sys/oracle@orcls auxiliary sys/oracle@orcl2

duplicate target database for standby from active database nofilenamecheck;

备库启动命令（不管哪种保护模式先启动备库）
startup nomount

alter database mount standby database;

alter database open;

alter pluggable database all open;

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

alter database recover managed standby database cancel;

alter database open;

alter pluggable database all open;

alter session set container=mydb;

alter session set container=cdb$root;

alter system switch logfile;

更改保护等级命令
alter database set standby database to maximize availability;
alter database set standby database to maximize PERFORMANCE;

仅需要在主库执行oracle用户下执行
dgmgrl
connect sys/oracle@orcl as sysdba

create configuration dg as primary database is 'orcl2' connect identifier is 'orcl2';
add database 'orcl' as connect identifier is 'orcl';

enable configuration
show configuration
remove configuration --删除后可以继续配置

dgmgrl修改参数命令

show database verbose orcl

 edit database orcl2 set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node3)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl2)(INSTANCE_NAME=orcl2)(SERVER=DEDICATED)))'

 edit database orcl set property StaticConnectIdentifier='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=node1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcl)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))'

---系统参数更改命令
alter system set log_archive_dest_1='location=/home/oracle/app/oracle/oradata/orcl/archivelog' scope=both;

alter system set db_recovery_file_dest='/home/oracle/app/oracle/fast_recovery_area';
alter system set db_recovery_file_dest_size=4560M

查询日志状态
alter system switch logfile;
--主库执行
select thread#, sequence#, status from v$log;
--备库执行
select thread#, max(sequence#) from v$archived_log where applied='YES' group by thread#;

 

 

 

 

 

<<主备库切换，操作顺序比较重要，先主后备>>--

1.停止Standby
select process, status from v$managed_standby; --查看备库是否在应用日志进行恢复
alter database recover managed standby database cancel;
shutdown immediate;
 
2.切换到只读模式
-----由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;
 
3.切换回管理恢复模式
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;
 
4.主库和备库之间角色切换
4.1 主库切换为备库
alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby with session shutdown;-- 主库有会话连接的时候
shutdown immediate
startup nomount;
alter database mount standby database;
alter database recover managed standby database disconnect from session;
 
4.2 从库切换为主库
alter database commit to switchover to primary;
shutdown immediate;
startup;
alter system switch logfile;
 
5.备库自动使用主库传过来的日志进行恢复
alter database recover automatic standby database;
 
6.更改保护模式
alter database set standby database to maximize protection;
alter database set standby database to maximize availability;
alter database set standby database to maximize performancen;
 
7.取消自动恢复模式
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
alter database recover managed standby database finish force;