#acl merlyn:read,write All:read
<<TableOfContents()>>

= 前提条件 =
 1. db_unique_name 在主备服务器不能相同。
 2. 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;
}}}