Contents

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

前提条件

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

主服务器信息

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

备服务器信息

OEL/RHEL 6.6
Server name: hdwms-stb
IP: 192.168.81.2
Oracle 11.2.0.4 software only
Oracle SID/Global_name: hdwms / ec
Oracle db_unique_name: hdwmsstb

主服务器配制

启用归档

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

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

启用logging

SQL> ALTER DATABASE FORCE LOGGING;

检查初始化参数

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      hdwms

SQL> show parameter db_unique_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_unique_name                       string      hdwms

配制LOG_ARCHIVE_CONFIG参数

SQL> ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(hdwms,hdwmsstb)';

确认LOG_ARCHIVE_CONFIG配制
SQL> show parameter LOG_ARCHIVE_CONFIG

设置对端LOG ARCHIVE

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

确认以上配制
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=hdwmsstb;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='hdwmsstb','hdwms' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='hdwmsstb','hdwms'  SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

show parameter fal_server;
show parameter standby_file_management;

配制listener.ora & tnsnames.ora

tnsnames.ora

HDWMS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HDWMS)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = HDWMS.WORLD)
    )
  )

HDWMSSTB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = HDWMSSTB)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = HDWMS.WORLD)
    )
  )

listener.ora

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
    (GLOBAL_DBNAME = HDWMS)
  (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
  (SID_NAME = HDWMS)
) )
  LISTENER =
   (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = HDWMS)(PORT = 1521))
   )
ADR_BASE_LISTENER = /u01/app/oracle

确认主备都能ping通

tnsping HDWMS
tnsping HDWMSSTB

备份主数据库

$ rman target /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

# 将备份到Flash Recovery Area.

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

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

修改PFILE /tmp/inithdwmsstb.ora

*.db_unique_name='hdwmsstb'
*.fal_server='hdwms'
*.log_archive_dest_2='SERVICE=hdwms ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=hdwms'

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

mkdir -p /u01/app/oracle/oradata/hdwms
mkdir -p /u01/app/oracle/fast_recovery_area/hdwms
mkdir -p /u01/app/oracle/admin/hdwms/adump

复制两个文件至备数据库

scp /tmp/inithdwmsstb.ora oracle@192.168.81.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp /tmp/hdwmsstb.ctl oracle@192.168.81.2:/u01/app/oracle/oradata/hdwms/control01.ctl
scp /tmp/hdwmsstb.ctl oracle@192.168.81.2:/u01/app/oracle/flash_recovery_area/hdwms/control02.ctl

复制password file和备份文件

cd /u01/app/oracle/product/11.2.0/dbhome_1/dbs/
scp orapwhdwms oracle@192.168.81.2:/u01/app/oracle/product/11.2.0/dbhome_1/dbs/

cd /u01/app/oracle/flash_recovery_area/
scp -r hdwms oracle@192.168.81.2:/u01/app/oracle/flash_recovery_area/

备数据库服务器操作

首先更改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=hdwms
sqlplus / as sysdba

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

恢复数据库

export ORACLE_SID=hdwms
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 ('/u01/app/oracle/oradata/hdwms/online_redo01.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo02.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo03.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo04.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE ('/u01/app/oracle/oradata/hdwms/online_redo05.log') SIZE 200M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

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

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL'
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo01.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo02.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo03.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo04.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo05.log') SIZE 200M;
ALTER DATABASE ADD STANDBY LOGFILE ('/u01/app/oracle/oradata/hdwms/standby_redo06.log') SIZE 200M;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO'

应用传输进程

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#;

主备数据库切换操作

手动从主hdwms切换至备hdwmsstb

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

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

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

切换回去

备数据库(hdwmsstb)服务器切换至主服务器。

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

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

在备hdwmsstb数据库执行

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