Contents

前提条件
1. 主服务器信息
2. 备服务器信息
主服务器配制
备数据库服务器操作

前提条件

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

主服务器信息

RHEL 6.8
Server name: cwdb
IP: 10.18.252.143
Oracle 11.2.0.4 software with oracle instance
Oracle SID/Global_name: cwdb / cwdb
Oracle db_unique_name: cwdb

备服务器信息

RHEL 6.8
Server name: cwdb-stb
IP: X.X.X.X
Oracle 11.2.0.4 software only
Oracle SID/Global_name: cwdb / cwdb
Oracle db_unique_name: cwdbstb

主服务器配制

启用/禁用FRA

SQL> show parameter recovery_file_dest
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = '/PATH' SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G SCOPE=SPFILE;
SQL> show parameter recovery_file_dest

# OR disable DB_RECOVERY
SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=SPFILE;

启用归档

SQL> alter system set log_archive_dest_1='LOCATION=/PATH' scope = SPFILE;
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;

Database altered.

SQL> SELECT FORCE_LOGGING  FROM v$database;

FOR
---
YES

检查初始化参数

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

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

配制LOG_ARCHIVE_CONFIG参数

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(cwdb,A-ZJFL-EASDB2)';

确认LOG_ARCHIVE_CONFIG配制
SQL> show parameter LOG_ARCHIVE_CONFIG

设置对端LOG ARCHIVE

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=A-ZJFL-EASDB2 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=A-ZJFL-EASDB2';

确认以上配制
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=A-ZJFL-EASDB2;
alter system set fal_client='A-ZJFL-EASDB1';
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='A-ZJFL-EASDB2','A-ZJFL-EASDB1' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='A-ZJFL-EASDB2','A-ZJFL-EASDB1'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

show parameter fal_server;
show parameter standby_file_management;

配制listener.ora & tnsnames.ora

tnsnames.ora

CWDBSTB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = A-ZJFL-EASDB2)(PORT = 1521))
    )
    (CONNECT_DATA =
       (SERVICE_NAME = cwdb)
    (UR=A)
  )
)

CWDB =
  (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = A-ZJFL-EASDB1)(PORT = 1521))
    )
     (CONNECT_DATA =
         (SERVICE_NAME = cwdb)
     (UR=A)
  )
)

listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = CWDB)
  (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
  (SID_NAME = CWDB)
) )
  LISTENER =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = A-ZJFL-EASDB1)(PORT = 1521))
   )
ADR_BASE_LISTENER = /u02/app/oracle

确认主备都能ping通

tnsping CWDB
tnsping CWDBSTB

备份主数据库

$ 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/A-ZJFL-EASDB2.ctl';
CREATE PFILE='/tmp/initA-ZJFL-EASDB2.ora' FROM SPFILE;

修改PFILE /tmp/initA-ZJFL-EASDB2.ora

*.db_unique_name='A-ZJFL-EASDB2'
*.fal_server='cwdb'
*.log_archive_dest_2='SERVICE=cwdb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=cwdb'
# DB_FILE_NAME_CONVERT='cwdb','A-ZJFL-EASDB2'
# *.log_file_name_convert='cwdb','A-ZJFL-EASDB2'
# *.db_create_online_log_dest_1='+DATA'
# *.db_create_online_log_dest_2='+FRA'

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

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

复制两个文件至备数据库

scp /tmp/initA-ZJFL-EASDB2.ora oracle@A-ZJFL-EASDB2:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/
scp /tmp/A-ZJFL-EASDB2.ctl oracle@A-ZJFL-EASDB2:/mnt/sdb1/oracleData/cwdb/control01.ctl
scp /tmp/A-ZJFL-EASDB2.ctl oracle@A-ZJFL-EASDB2:/mnt/sdb1/oracleData/rman/control02.ctl

复制password file和备份文件

cd /u02/app/oracle/product/11.2.0/db_1/dbs/
scp orapwcwdb oracle@A-ZJFL-EASDB2:/oracle/app/oracle/product/11.2.0/dbhome_1/dbs/

cd /mnt/sdb1/rman/
scp -r cwdb oracle@A-ZJFL-EASDB2:/mnt/sdb1/rman/

备数据库服务器操作

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

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

确认listener启动正常

lsnrctl status

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

恢复控制文件

export ORACLE_SID=cwdb
sqlplus / as sysdba

SQL> CREATE SPFILE FROM PFILE='/tmp/initA-ZJFL-EASDB2.ora';

恢复数据库

export ORACLE_SID=cwdb
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/cwdb/online_redo01.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/cwdb/online_redo02.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/cwdb/online_redo03.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/cwdb/online_redo04.log') SIZE 4G;
ALTER DATABASE ADD LOGFILE ('/mnt/sdb1/oracleData/cwdb/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/cwdb/standby_redo01.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/cwdb/standby_redo02.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/cwdb/standby_redo03.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/cwdb/standby_redo04.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/cwdb/standby_redo05.log') SIZE 4G;
ALTER DATABASE ADD STANDBY LOGFILE ('/mnt/sdb1/oracleData/cwdb/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 = CWDB.WORLD)
      (ORACLE_HOME = /oracle/app/oracle/product/11.2.0/dbhome_1)
      (SID_NAME = CWDB)
    )
  )

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

ADR_BASE_LISTENER = /oracle/app

~$ sqlplus / as sysdba
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initA-ZJFL-EASDB2.ora';

[oracle@A-ZJFL-EASDB2 ~]$ rman target sys@cwdb auxiliary sys@A-ZJFL-EASDB2
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';

测试主备服务器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#;

主备数据库切换操作

手动从主cwdb切换至备A-ZJFL-EASDB2

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

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

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

备数据库(A-ZJFL-EASDB2)执行接管操作

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

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

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

在备A-ZJFL-EASDB2数据库执行

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;

désert/Oracle/DataGuard_test