Oracle11g DataGuard配置与管理

######################################　　　
搭建备库（DataGard）
author:guoyJoe;
createdate:2012-12-14
######################################
物理Standby创建时的操作步骤
1、创建主库的备份
2、创建Standby数据库控件文件
3、配置主备库的监听和网络服务名
4、配置主备库的初始化参数文件
5、复制备份集到Standby服务器
6、恢复Standby数据库
7、启动物理Standby数据库REDO应用
8、打开Standby数据库
9、切换主库的日志检测日志的传输与应用是否正常
*********************************************
     物理Standby实际创建过程演示
*********************************************

一、库配置和相关操
1、配置vi /etc/hosts
192.168.0.158  bxdb
192.168.0.165  dg

2、确认主库处于归档模式：archive log list;

3、将主库置为Force Logging模式:
     select force_logging from v$database;
     alter database force logging;

4、配置主库的初始化参数
*.db_unique_name=ocp_pri
*.log_archive_config='DG_CONFIG=(ocp_pri,ocp_dg)'
*.log_archive_dest_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ocp_pri'
*.log_archive_dest_2='SERVICE=standby LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ocp_dg'
*.log_archive_dest_state_2=DEFER
    --主库切为备库时要用到
*.fal_server=standby
*.fal_client=primary
*.standby_file_management=AUTO
#*.db_file_name_convert='bxdb','bxdb'
#*.log_file_name_convert='bxdb','bxdb'

5、配置主库的listener.ora与tnsnames.ora文件
vi listener.ora
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=bxdb)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=bxdb)
      (ORACLE_HOME=/u01/app/oracle/product/11g)
      (SID_NAME=bxdb)))
vi tnsnames.ora
bxdb  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bxdb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bxdb)
    )
  )
bxdb_dg  =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dg)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = bxdb)
    )
  )

6、主库备份
backup database format '/backup/rman/full_db_%U';

7、创建Standby数据库的控制文件
alter database create standby controlfile as '/backup/std01.ctl';

8、创建密码文件(关闭强密码认证)
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;
orapwd file=orapwbxdb password=oracle force=y ignorecase=y;

9、复制备份集、控制文件、参数文件到Standby服务器
scp *.ora      oracle@dg:$ORACLE_HOME/network/admin/
scp pfile.ora  oracle@dg:/backup
scp std01.ctl  oracle@dg:/backup
scp full_db*   oracle@dg:/backup

二、物理Standby数据库配置和相关操作
1、配置/etc/hosts
192.168.0.158  bxdb
192.168.0.165  dg

2、创建密码文件(关闭强密码认证)
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE;
orapwd file=orapwbxdb password=oracle force=y ignorecase=y;

3、创建日志输出文件相关目录
mkdir -p /arch
mkdir -p /u01/app/oracle/oradata/oca
mkdir  -p /u01/app/oracle/admin/oca/adump
--mkdir  -p /u01/app/oracle/diag/rdbms/bxdb_dg/bxdb/trace //自动生成无需创建
--mkdir  -p /u01/app/oracle/diag/rdbms/bxdb_dg/bxdb/cdump //自动生成无需创建

4、配置备库的listener.ora与tnsnames.ora文件
    修改listener.ora主机名或IP

5、配置备库的初始化参数
*.audit_file_dest='/u01/app/oracle/admin/bxdb/adump'
*.db_unique_name=bxdb_dg
*.log_archive_config='DG_CONFIG=(ocp_pri,ocp_dg)'
*.control_files='/opt/oracle/oradata/standby/control1.ctl'
*.log_archive_dest_1='LOCATION=/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=bxdb_dg'
*.log_archive_dest_2='SERVICE=bxdb LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=bxdb'
*.fal_server=bxdb
*.fal_client=bxdb_dg
*.standby_file_management=AUTO
#*.db_file_name_convert='bxdb','bxdb'
#*.log_file_name_convert='bxdb','bxdb'
#*.background_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/trace'
#*.core_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/cdump'
#*.user_dump_dest='/u01/app/oracle/diag/rdbms/standby/standby/trace'

6、启动物理Standby数据库到MOUNT状态
startup mount pfile='/backup/pfile.ora';

7、恢复数据库
export ORACLE_SID=bxdb
rman target /
catalog start with '/backup/';
restore database;

8、接收归档文件(在主库上操作)
在主库上启动发送日志：ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

9.应用日志
alter database recover managed standby database disconnect from session;

10.查看一下告警日志:Media Recovery Waiting for thread 1 sequence 8 (in transit)
如这个提示:recover managed standby database cancel;

11.打开数据库
alter database open;

12、建standby redo log;--创建的大小要与主库的redolog一样
alter database add standby logfile
group 20 ('/u01/app/oracle/oradata/oca/stb1redo20.log') size 100m,
group 21 ('/u01/app/oracle/oradata/oca/stb1redo21.log') size 100m,
group 22 ('/u01/app/oracle/oradata/oca/stb1redo22.log') size 100m,
group 23('/u01/app/oracle/oradata/oca/stb1redo23.log') size 100m;
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
假如状态全是:UNASSIGNED--->shutdown immediate,startup;--> ACTIVE

13、启动REDO应用
recover managed standby database using current logfile disconnect from session;

****************************************
    物理备用数据库的日常管理
****************************************
1.查看日志应用情况：
select sequence#,applied from v$archived_log;

2.通过V$STANDBY_LOG视图验证standby redo log文件组是否成功创建
告警错误1 Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION
SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
解方法1:在备库重standby redolog file,创建的大小要与主库的redolog一样!!!!!
         如果还是不行,那只能得启备库试试!!!!!!!
     
3.查询V$MANAGED_STANDBY视图，可以确定其是否处于重做应用状态
如果包含MRP0，则表示处于重做应用状态；所以必须先取消重做应用状态，然后才能关闭该备用数据库。
select process, status from v$managed_standby;

4.可通过检查，主数据库的归档日志状态，检查DATAGUARD的运行是否正常
SELECT DEST_ID,ERROR FROM V$ARCHIVE_DEST;
如果查询结果，显示STATUS＝VALID，并且ERROR为空，则表示主数据库向备用数据库传递日志状态正常。否则，可能存在问题。
常见故障（1）－网络故障：可能由于网络原因，造成日志文件传递失败。此时，可首先可利用ping命令检查网络状态，然后利用Oralce的tnsping 命令检查tnsnames.ora文件解析情况。
常见故障（2）－密码问题：主、备数据必须保持sys用户密码的一致性。即保持orapwdSID.ora文件中sys密码的一致性。如果密码不同，则可能造成传输失败。
告警错误1 returning error ORA-16191: Primary log shipping client not logged on standby
   --密码文件是否一样:两节点执行如下
  ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH;
  orapwd file=orapwbxdb password=oracle force=y ignorecase=y;
告警错误2 ORA-16047: DGID mismatch between destination setting and target database
   --查参数log_archive_dest_2

5.备用数据库，获取备用数据库中最后应用的日志的序列号。 暂记录为laseq
  SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE APPLIED='YES';
6.应先打开库再做日志应用,就不会报错
SQL> STARTUP MOUNT;
SQL>  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
SQL> ALTER DATABASE OPEN;
alter database open
*
ERROR at line 1:
ORA-10456: cannot open standby database; media recovery session may be in
progress
SQL> shutdown immediate;
SQL> startup
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
Database altered.

7.取消应用日志
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

*************************************
     物理Standby的角色转换
*************************************
switchover操作步骤
主库上操作:(主-->备)
Step 1   Verify that the primary database can be switched to the standby role.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS 
----------------- 
TO STANDBY 
Step 2   Initiate the switchover on the primary database.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Step 3   Shut down and then mount the former primary database.
SQL> SHUTDOWN ABORT;
SQL> STARTUP MOUNT;
备库上操作:(备-->主)
Step 4   Verify that the switchover target is ready to be switched to the primary role.
SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS 
----------------- 
TO_PRIMARY 
Step 5   Switch the target physical standby database role to the primary role.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY WITH SESSION SHUTDOWN;
Step 6   Open the new primary database.
SQL> ALTER DATABASE OPEN;
主库上操作:(主-->备)
Step 7   Start Redo Apply on the new physical standby database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;