Contents

  1. 前提条件
    1. 主服务器信息
    2. 备服务器信息
  2. 主服务器配制
    1. 启用归档
    2. 启用logging
    3. 检查初始化参数
    4. 配制LOG_ARCHIVE_CONFIG参数
    5. 设置对端LOG ARCHIVE
    6. 设置LOG ARCHIVE
    7. 设置fal_server
    8. 配制listener.ora & tnsnames.ora
    9. 备份主数据库
    10. 为备数据库创建控制文件和PFILE
  3. 备数据库服务器操作
    1. 确认listener启动正常
    2. 从备份中恢复数据库(可选方法）
    3. 使用RMAN自动复制主数据库至备数据库
      1. For duplicate listener.ora
    4. 应用传输进程
    5. 确认Dataguard保护模式
    6. 测试主备服务器ARCHIVELOG
    7. 主备数据库切换操作
      1. 手动从主orcl切换至备orclstb
      2. 备数据库(orclstb)执行接管操作
    8. 主数据库故障手动切换操作
    9. Read-Only Standby and Active Data Guard
    10. dup log
  4. Troubleshooting

前提条件

  1. db_unique_name 在主备服务器不能相同。
  2. hosts文件中定义各自的主机名

主服务器信息

OEL/RHEL 6.6
Server name: orcl
IP: 192.168.0.123
Oracle 11.2.0.4 software with oracle instance
Oracle SID/Global_name: orcl / card
Oracle db_unique_name: orcl

备服务器信息

OEL/RHEL 6.6
Server name: orcl-stb
IP: 192.168.0.124
Oracle 11.2.0.4 software only
Oracle SID/Global_name: orcl / ec
Oracle db_unique_name: orclstb

主服务器配制

启用归档

SQL> show parameter recovery_file_dest
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +GOWMS_DATA
db_recovery_file_dest_size           big integer 4182M

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '+GOWMS_ARCH' SCOPE=BOTH SID='*';

System altered.

SQL> show parameter recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      +GOWMS_ARCH
db_recovery_file_dest_size           big integer 4182M
SQL> 


# disable DB_RECOVERY
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=BOTH SID="*";
SQL> alter system set log_archive_dest_1='LOCATION=+ARCH/gowms/archivelog' scope = both;
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> alter system switch logfile;

确认归档配制
SQL> archive log list;

启用logging

SQL> ALTER DATABASE FORCE LOGGING;

SELECT FORCE_LOGGING  FROM v$database;

检查初始化参数

SQL> show parameter db_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      orcl

SQL> show parameter db_unique_name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      orcl

配制LOG_ARCHIVE_CONFIG参数

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstb)';

确认LOG_ARCHIVE_CONFIG配制
SQL> show parameter LOG_ARCHIVE_CONFIG

设置对端LOG ARCHIVE

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclstb NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstb';

确认以上配制
SQL> show parameter LOG_ARCHIVE_DEST_2

启用LOG_ARCHIVE_DEST_2
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

确认状态
SQL> show parameter LOG_ARCHIVE_DEST_STATE_2;

设置LOG ARCHIVE

ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

show parameter log_archive_format;
show parameter log_archive_max_processes;
show parameter remote_login_passwordfile;

设置fal_server

ALTER SYSTEM SET FAL_SERVER=orclstb;
alter system set fal_client='orcl';
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='orclstb','orcl' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='orclstb','orcl'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

show parameter fal_server;
show parameter standby_file_management;

配制listener.ora & tnsnames.ora

tnsnames.ora 

ORCLSTB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = orclstb)(PORT = 1521))
    )
    (CONNECT_DATA =
       (SERVICE_NAME = orcl)
    (UR=A)
  )
)

ORCL =
  (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    )
     (CONNECT_DATA =
         (SERVICE_NAME = orcl)
     (UR=A)
  )
)

listener.ora 

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = ORCL)
  (ORACLE_HOME = /u02/app/oracle/product/11.2.0/db_1)
  (SID_NAME = ORCL)
) )
  LISTENER =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ORCL)(PORT = 1521))
   )
ADR_BASE_LISTENER = /u02/app/oracle

确认主备都能ping通 

tnsping ORCL
tnsping ORCLSTB

备份主数据库

$ rman target / 

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   '/mnt/sdb1/rman/full_%u_%s_%p';
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 将备份到Flash Recovery Area.

为备数据库创建控制文件和PFILE

ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/orclstb.ctl';
CREATE PFILE='/tmp/initorclstb.ora' FROM SPFILE;

修改PFILE /tmp/initorclstb.ora 

*.db_unique_name='orclstb'
*.fal_server='orcl'
*.log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'
# DB_FILE_NAME_CONVERT='orcl','orclstb'
# *.log_file_name_convert='orcl','orclstb'
# *.db_create_online_log_dest_1='+DATA'
# *.db_create_online_log_dest_2='+FRA'

首先登录备数据库创建以下目录 

mkdir -p /mnt/sdb1/oracleData/orcl
mkdir -p /u02/app/oracle/fast_recovery_area/orcl
mkdir -p /mnt/sdb1/oracleData/orcl/adump

复制两个文件至备数据库 

scp /tmp/initorclstb.ora oracle@orclstb:/u02/app/oracle/product/11.2.0/db_1/dbs/
scp /tmp/orclstb.ctl oracle@orclstb:/mnt/sdb1/oracleData/orcl/control01.ctl
scp /tmp/orclstb.ctl oracle@orclstb:/mnt/sdb1/oracleData/rman/control02.ctl

复制password file和备份文件 

cd /u02/app/oracle/product/11.2.0/db_1/dbs/
scp orapworcl oracle@orclstb:/u02/app/oracle/product/11.2.0/db_1/dbs/

cd /mnt/sdb1/rman/
scp -r orcl oracle@orclstb:/mnt/sdb1/rman/

备数据库服务器操作

注：可选？！首先更改db_unique_name为对应的名称。 

alter system set db_unique_name='orclstb' SCOPE=SPFILE;
select db_unique_name from v$database;
# OR:
show parameter unique;

确认listener启动正常

lsnrctl status

从备份中恢复数据库(可选方法）

恢复控制文件 

export ORACLE_SID=orcl
sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/initorclstb.ora';

恢复数据库 

export ORACLE_SID=orcl
rman target /

RMAN> STARTUP MOUNT;
RMAN> list backup of database summary;
RMAN> RESTORE DATABASE;

Note: Recovery of database would be failed with RMAN-06054 error, We can ignore it because RMAN will ask for unknown archive log ( i.e. next archive log sequence, i.e. 10 ) who is not also available on Primary database.

Error log: RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 10 and starting SCN of 1009554

创建日志文件 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/orcl/online_redo01.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/orcl/online_redo02.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/orcl/online_redo03.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/orcl/online_redo04.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/orcl/online_redo05.log') SIZE 4G;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

在主和备数据库中创建STANDBY LOGFILE 

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/orcl/standby_redo01.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/orcl/standby_redo02.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/orcl/standby_redo03.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/orcl/standby_redo04.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/orcl/standby_redo05.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/orcl/standby_redo06.log') SIZE 4G;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

使用RMAN自动复制主数据库至备数据库

For duplicate listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = ORCL.WORLD)
      (ORACLE_HOME = /u02/app/oracle/product/11.2.0/db_1)
      (SID_NAME = ORCL)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ORCLSTB)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ADR_BASE_LISTENER = /u02/app/oracle

~$ sqlplus / as sysdba
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initorclstb.ora';

[oracle@orclstb ~]$ rman target sys@orcl auxiliary sys@orclstb
RMAN> duplicate target database for standby from active database nofilenamecheck;

应用传输进程

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

# Foreground redo apply. Session never returns until cancel. 
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

# Background redo apply. Control is returned to the session once the apply process is started.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

停止传输 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

确认Dataguard保护模式

默认为 maximum performance 模式 

SELECT protection_mode FROM v$database;

select name,open_mode,database_role from v$database;
select message from v$dataguard_status;
select dest_name,status,error from v$archive_dest where dest_name='LOG_ARCHIVE_DEST_2';

dgmgrl sys/oracle36#^@orclstb
DGMGRL>  show configuration;

测试主备服务器ARCHIVELOG

主: 

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time
FROM   v$archived_log
ORDER BY sequence#;

ALTER SYSTEM SWITCH LOGFILE;

备: 

ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS';

SELECT sequence#, first_time, next_time, applied
FROM   v$archived_log
ORDER BY sequence#;

主备数据库切换操作

手动从主orcl切换至备orclstb

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

关闭主orcl数据库，挂载主数据库为“备”数据库工作 

SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

备数据库(orclstb)执行接管操作

CONNECT / AS SYSDBA
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
SHUTDOWN IMMEDIATE;
STARTUP;

切换回去的话，重启执行一遍，反方向。

主数据库故障手动切换操作

在备orclstb数据库执行 

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;
ALTER DATABASE ACTIVATE STANDBY DATABASE;

Read-Only Standby and Active Data Guard

To switch the standby database into read-only mode, do the following. 

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE OPEN READ ONLY;

select name,open_mode,database_role from v$database;

To resume managed recovery, do the following. 

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

select name,open_mode,database_role from v$database;

dup log

[oracle@orclstb ~]$ rman target sys/oracle36#^@orcl auxiliary sys/oracle36#^@orclstb

Recovery Manager: Release 11.2.0.4.0 - Production on Fri Jul 8 01:33:11 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1444008119)
connected to auxiliary database: ORCL (not mounted)

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

Starting Duplicate Db at 08-JUL-16
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:
{  
   backup as copy reuse
   targetfile  '/u02/app/oracle/product/11.2.0/db_1/dbs/orapworcl' auxiliary format
 '/u02/app/oracle/product/11.2.0/db_1/dbs/orapworcl'   ;
}
executing Memory Script

Starting backup at 08-JUL-16
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=68 device type=DISK
Finished backup at 08-JUL-16

contents of Memory Script:
{  
   backup as copy current controlfile for standby auxiliary format  '/mnt/sdb1/oracleData/orcl/control01.ctl';
   restore clone controlfile to  '/mnt/sdb1/oracleData/rman/control02.ctl' from
 '/mnt/sdb1/oracleData/orcl/control01.ctl';
}
executing Memory Script

Starting backup at 08-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
copying standby control file
output file name=/mnt/sdb1/oracleData/rman/snapcf_orcl.f tag=TAG20160708T013323 RECID=5 STAMP=916623203
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-JUL-16

Starting restore at 08-JUL-16
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: copied control file copy
Finished restore at 08-JUL-16

contents of Memory Script:
{  
   sql clone 'alter database mount standby database';
}
executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to 
 "/mnt/sdb1/oracleData/orcl/temp01.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to 
 "/mnt/sdb1/oracleData/orcl/system01.dbf";
   set newname for datafile  2 to 
 "/mnt/sdb1/oracleData/orcl/sysaux01.dbf";
   set newname for datafile  3 to 
 "/mnt/sdb1/oracleData/orcl/undotbs01.dbf";
   set newname for datafile  4 to 
 "/mnt/sdb1/oracleData/orcl/users01.dbf";
   backup as copy reuse
   datafile  1 auxiliary format 
 "/mnt/sdb1/oracleData/orcl/system01.dbf"   datafile 
 2 auxiliary format 
 "/mnt/sdb1/oracleData/orcl/sysaux01.dbf"   datafile 
 3 auxiliary format 
 "/mnt/sdb1/oracleData/orcl/undotbs01.dbf"   datafile 
 4 auxiliary format 
 "/mnt/sdb1/oracleData/orcl/users01.dbf"   ;
   sql 'alter system archive log current';
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /mnt/sdb1/oracleData/orcl/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting backup at 08-JUL-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/mnt/sdb1/oracleData/orcl/system01.dbf
output file name=/mnt/sdb1/oracleData/orcl/system01.dbf tag=TAG20160708T013331
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/mnt/sdb1/oracleData/orcl/sysaux01.dbf
output file name=/mnt/sdb1/oracleData/orcl/sysaux01.dbf tag=TAG20160708T013331
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/mnt/sdb1/oracleData/orcl/undotbs01.dbf
output file name=/mnt/sdb1/oracleData/orcl/undotbs01.dbf tag=TAG20160708T013331
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/mnt/sdb1/oracleData/orcl/users01.dbf
output file name=/mnt/sdb1/oracleData/orcl/users01.dbf tag=TAG20160708T013331
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 08-JUL-16

sql statement: alter system archive log current

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=916623257 file name=/mnt/sdb1/oracleData/orcl/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=916623257 file name=/mnt/sdb1/oracleData/orcl/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=916623257 file name=/mnt/sdb1/oracleData/orcl/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=916623257 file name=/mnt/sdb1/oracleData/orcl/users01.dbf
Finished Duplicate Db at 08-JUL-16

Troubleshooting

http://oracleinaction.com/recover-standby-datafile-primary/