oracle dataguard 简易配置文档

23erewr

一、主库端的准备


1、查看主库中数据库目录结构

   查看数据库文件路径

   select t1.name,t2.name from v$tablespace t1,v$datafile t2 where t1.ts#=t2.ts#;

   查看日志文件路径

   select * from v$logfile;

   查看控制文件路径

   select * from v$controlfile;


2.开启数据库归档并激活force logging 模式


  查看归档模式

  archive log list

  开启归档模式

  alter database archivelog;

  alter database noarchivelog;

  修改归档位置和归档命名规则

  alter system set log_archive_dest_1='location=/arch' scope=spfile;

  alter system set log_archive_format ='%t_%s_%r.arc' scope=spfile;

  开启force logging

  alter database force logging;

  alter database no force logging;

3.修改初始化参数文件


  create pfile from spfile;（备份初始化参数文件）


  alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(zhuku,beiku)';
  alter system set LOG_ARCHIVE_DEST_1='/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)       DB_UNIQUE_NAME=zhuku';
  alter system set LOG_ARCHIVE_DEST_2='SERVICE=beiku LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=beiku';
  alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
  alter system set LOG_ARCHIVE_DEST_STATE_2=defer;
  alter system set FAL_CLIENT='zhuku';
  alter system set FAL_SERVER='beiku';
  alter system set DB_FILE_NAME_CONVERT='/oracle/oradata/beiku/','/oracle/oradata/zhuku/' scope=spfile;
  alter system set LOG_FILE_NAME_CONVERT='/oracle/oradata/beiku/','/oracle/oradata/zhuku/' scope=spfile;
  alter system set standby_file_management=auto;


4.重启数据库使的上述修改生效  保留在open状态



二、备库端的准备

1.创建相关目录

mkdir /oracle/admin/beiku/adump  bdump  cdump  dpdump  udump
mkdir /oracle/oradata/beiku
和主库一致的备份目录以及归档日志目录  并修改权限
2.拷贝主库密钥文件

scp

3.备库初始化参数设置

备库的初始化文件可以从主库初始化参数文件修改而来

添加如下内容

*.DB_NAME='zhuku'
*.remote_login_passwordfile='EXCLUSIVE

*.DB_UNIQUE_NAME='beiku'
*.log_archive_config= 'dg_config=(beiku,zhuku)'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=beiku'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=zhuku  LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=zhuku'
*.LOG_ARCHIVE_DEST_STATE_1='ENABLE'
*.LOG_ARCHIVE_DEST_STATE_2='ENABLE'
*.LOG_ARCHIVE_FORMAT='%t_%s_%r.arc'
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.FAL_SERVER='zhuku'
*.FAL_CLIENT='beiku'
*.DB_FILE_NAME_CONVERT='/oracle/oradata/zhuku/','/oracle/oradata/beiku/'
*.LOG_FILE_NAME_CONVERT='/oracle/oradata/zhuku/','/oracle/oradata/beiku/'
*.STANDBY_FILE_MANAGEMENT='AUTO'

4.创建spfile
create spfile from pfile='$ORACLE_HOME/dbs/initracdb.ora'

5.启动数据库到mount状态
SQL> startup nomount;


三、配置监听和tns


可以通过图形化界面netca  netmgr 进行配置  主库和备库都要配置两边的tns


SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/db10g)
      (PROGRAM = extproc)
    )

   (SID_DESC =
      (GLOBAL_DBNAME = beiku)
      (ORACLE_HOME =  /oracle/db10g)
      (SID_NAME = beiku)
   )
  )


验证监听

sqlplus sys/oracle@zhuku as sysdba

sqlplus sys/oracle@beiku as sysdba


四、Rman下duplicate数据库，恢复备库

方法1
主库进行全库的备份，将备份集拷贝到备库相同的目录下
RUN {
allocate channel c1 type disk;
allocate channel c2 type disk;
sql 'alter system archive log current';
backup current controlfile for standby format='/backup/dgbackup_control_%U';
BACKUP FORMAT '/backup/dgbackup_%U_%T' skip inaccessible filesperset 5 DATABASE ;
sql 'alter system archive log current';
BACKUP FORMAT '/backup/dgbackup_arch_%U_%T' skip inaccessible filesperset 5 ARCHIVELOG ALL ;
release channel c2;
release channel c1;
}


rman target / auxiliary sys/oracle@beiku
duplicate target database for standby nofilenamecheck dorecover;

方法2  11g之后版本适用

利用RMAN在备库上恢复主库                        （备库端做）

rman target sys/oracle@zhuku auxiliary sys/oracle@beiku

RMAN> duplicate target database for standby from active database nofilenamecheck;
这条命令可以直接恢复数据文件，standby控制文件，standby日志组，非常霸道




五、开启DG

在备库上执行
SQL> select instance_name,status from v$instance;

INSTANCE_NAME         STATUS
---------------- ------------
racdb                 MOUNTED

该模式会从主数据库中复制并应用日志文件

alter system set log_archive_dest_state_2=enable;

alter database recover managed standby database disconnect from session;

取消日志应用

alter database recover managed standby database cancel


在Oracle 11g以前版本中的的Data Guard物理备用数据库，可以以只读的方式打开数据库，但此时Media Recovery利用日志进行数据同步的过程就停止了，如果物理备用数据库处于恢复的过程中数据库就不能打开查询，也就是说日志应用和只读打开两个状态是互斥的，而Oracle 11g Active Data Guard功能解决了这个矛盾，在利用日志恢复数据的同时可以用只读的方式打开数据库，用户可以在备用数据库上进行查询、报表等操作，这类似逻辑Data Guard备用数据库的功能（查询功能方面），但是，数据同步的效率更高、对硬件的资源要求更低。这样可以更大程度地发挥物理备用数据库的硬件资源的效能

取消备库的自动恢复
sys@ora11gdg@> alter database recover managed standby database cancel;

Database altered.


监控备库警告日志

监控主库警告日志


18.13. 查看日志同步情况

主库上
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

查看已经应用的日志

SQL> select sequence#,applied from v$archived_log order by sequence#;


备库上
SQL> select sequence#, first_time, next_time from v$archived_log order by sequence#;

SQL> select sequence#,applied from v$archived_log order by sequence#;

18.14. 配置主库standby redo log
查看日志文件大小，备用redolog一定要大于在线日志log容量 ，主要用于主备库的切换
select group#,bytes from v$log;
alter database add standby logfile group 5('/oracle/oradata/zhuku/redolog1.log') size 50m;
alter database add standby logfile group 6('/oracle/oradata/zhuku/redolog2.log') size 50m;
alter database add standby logfile group 7('/oracle/oradata/zhuku/redolog3.log') size 50m;
alter database add standby logfile group 8('/oracle/oradata/zhuku/redolog4.log') size 50m;

查看结果
select group#,thread#,sequence#,archived,status from v$standby_log;

18.15. 配置备库standby redo log

sql> alter database recover managed standby database cancel
sql> shutdown immediate;
sql> startup mount;
alter database add standby logfile group 5('/u01/app/oracle/oradata/racdb/redolog1.log') size 100m;
alter database add standby logfile group 6('/u01/app/oracle/oradata/racdb/redolog2.log') size 100m;
alter database add standby logfile group 7('/u01/app/oracle/oradata/racdb/redolog3.log') size 100m;
alter database add standby logfile group 8('/u01/app/oracle/oradata/racdb/redolog4.log') size 100m;

查看结果
select group#,thread#,sequence#,archived,status from v$standby_log;

启用接收日志
alter database recover managed standby database disconnect from session;


备库验证数据

切换备用数据库只读模式
alter database recover managed standby database cancel;
alter database open read only;
查看已经应用的日志
select sequence#,applied from v$archived_log order by sequence#;



主备库角色切换

• 在主库中要做数据库全备
  
  

2.确保节点1的日志已经全部传送到了备库

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

3.在节点1，确认为TO_STANDBY

SQL> select SWITCHOVER_STATUS from v$database;
SWITCHOVER_STATUS
-------------------------
TO STANDBY

4.将节点一切换成备库

SQL> alter database commit to switchover to physical standby with session shutdown ;

5.关闭并重新启动之前的主库实例
sql> shutdown immediate;
sql> startup mount
6.在备库的v$database视图中查看备库的切换状态

select switchover_status from v$database;

to primary 或者sessions active

7.切换备库到主库角色

alter database commit to switchover to primary; （to primary 状态使用该命令）

alter database commit to switchover to primary with session shutdown; （sessions active 状态使用该命令）

8.完成备库到主库的切换

alter database open;

9.如果备库以只读模式打开，先关闭数据，然后再重新启动。

sql> shutdown immediate;
sql> startup ;

10.重新启动一下新的备库上的重做日志应用服务

sql> alter database recover managed standby database disconnect from session;

或者sql> alter database recover managed standby database using current logfile disconnect from session;

11.开始发送重做数据到备库上
sql> alter system switch logfile;


模式切换：
maximize protection ---> maximize availability ----> maximize performance
当在把dataguard的保护级别按这上面的顺序减低的时候，不需要primary库在mount状态，primary在open状态就可以直接执行保护模式更改命令
maximize protection <--- maximize availability <---- maximize performance
当在把dataguard的保护级别按这上面的顺序升高的时候，需要primary库在mount状态，如果在open更改会报错误
select DATABASE_ROLE,open_mode,PROTECTION_MODE,PROTECTION_LEVEL from v$database; --查看保护模式
切换主库保护模式的语法：
ALTER DATABASE SET STANDBY DATABASE
TO MAXIMIZE {PROTECTION | AVAILABILITY | PERFORMANCE }

SQL> alter database set standby database to maximize performance;
SQL> alter database set standby database to maximize availability;
SQL> alter database set standby database to maximize protection;
以上都是在主库操作，备库会随之改变。