Debian下配置Oracle DataGuard

分別安裝主庫和備庫的oracle軟體，主庫建立資料庫庫，備庫只安裝資料庫軟體。

一. 配置primary database

--設定primary庫為force Logging模式(為了便於切換，建議standby庫也設定為force logging),這樣所有的改變都會放入重做日誌中，確保了可靠的恢復。

SQL> ALTER DATABASE FORCE LOGGING;
SQL> select force_logging from v$database;

--設定主庫初始化引數並修改
SQL> create pfile from spfile;

複製兩份：一份修改為primary,一份修改為standby

--設定歸檔模式。
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;

--Create a Backup Copy of the Primary Database Datafiles

rman target /
RMAN> run{
 allocate channel c1 type disk;
 backup format '/oracle/rman/%u_%s.bak' database;
 sql 'alter system archive log current';
 backup filesperset 10 archivelog all format '/oracle/rman/%u_%s.bak';
 backup format '/oracle/rman/controlbak.bak' current controlfile for standby;
 release channel c1;
 }

也可以這樣建立控制檔案或在RMAN備份裡執行，二者複製到備庫後複製多份,複製改名為(control01.ctl,control02.ctl,control03.ctl)  
--Create a Control File for the Standby Database
SQL> alter database create standby controlfile as '/oracle/rman/controlbak.bak';
或
RMAN> copy current controlfile for standby to '/oracle/rman/controlbak.bak';

--修改主庫引數檔案：
*.instance_name='orcl'
*.service_names='primary','orcl'
*.db_unique_name=primary 
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.log_archive_dest_1='location=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=3
*.FAL_SERVER=standby
*.FAL_CLIENT=primary
*.STANDBY_FILE_MANAGEMENT=AUTO

##下面這段可以省略

--在主庫建立standby redo log大小與主庫聯機日誌檔案大小一樣，組數至少比primary中的log file數量大1（可以省略）

SQL> alter database add standby logfile group 5 '/oracle/oradata/orcl/standbyredo05.log' size 100M;
SQL> alter database add standby logfile group 6 '/oracle/oradata/orcl/standbyredo06.log' size 100M;
SQL> alter database add standby logfile group 7 '/oracle/oradata/orcl/standbyredo07.log' size 100M;
SQL> alter database add standby logfile group 8 '/oracle/oradata/orcl/standbyredo08.log' size 100M;
SQL> alter database add standby logfile group 9 '/oracle/oradata/orcl/standbyredo09.log' size 100M;

--查詢
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

SQL>shutdown immediate

--刪除主庫的spfileorcl.ora引數檔案

--用pfile啟動，再重新建立spfile.
SQL> create spfile from pfile='/oracle/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
SQL> startup

--分別在主庫和備庫配置監聽並啟動

二. 配置standby database

--複製Backup datafiles 、Standby control file 、pfile、password file等到相應的目錄
  (將standby.ctl複製到備庫後複製多份,複製改名為control01.ctl,control02.ctl,control03.ctl)

:~> scp *.bak 10.1.11.32:/oracle/rman

--Prepare an Initialization Parameter File for the Standby Database
*.instance_name='orcl'
*.service_names='standby','orcl'
*.db_unique_name=standby
*.log_archive_config='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/oracle/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'
*.LOG_ARCHIVE_DEST_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=primary'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=3
*.FAL_SERVER=primary
*.FAL_CLIENT=standby
*.STANDBY_FILE_MANAGEMENT=AUTO

--在備庫端還原資料庫

依照主庫的資料檔案位置，在備庫上建立相應的目錄結構（最好與主庫一致）；
mkdir -p /oracle/app/oracle/admin/orcl/adump
mkdir -p /oracle/app/oracle/admin/orcl/udump
mkdir -p /oracle/app/oracle/admin/orcl/bdump
mkdir -p /oracle/app/oracle/admin/orcl/cdump
mkdir -p /oracle/app/oracle/admin/orcl/pfile
mkdir -p /oracle/app/oracle/admin/orcl/dpdump

sqlplus /nolog
SQL> conn / as sysdba
SQL> create spfile from pfile='/oracle/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
SQL> startup nomount;
rman target /
RMAN> restore controlfile from '/oracle/rman/controlbak.bak';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;

SQL> alter database add standby logfile group 5 '/oracle/oradata/orcl/standbyredo05.log' size 100M;
SQL> alter database add standby logfile group 6 '/oracle/oradata/orcl/standbyredo06.log' size 100M;
SQL> alter database add standby logfile group 7 '/oracle/oradata/orcl/standbyredo07.log' size 100M;
SQL> alter database add standby logfile group 8 '/oracle/oradata/orcl/standbyredo08.log' size 100M;
SQL> alter database add standby logfile group 9 '/oracle/oradata/orcl/standbyredo09.log' size 100M;

--查詢
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

--啟動redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

***取消redo應用***
SQL> alter database recover managed standby database cancel;
SQL> shutdown immediate;

--測試是否成功

--確認現有備庫歸檔重做日誌檔案
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

--在主資料庫上執行, 測試歸檔操作到物理備資料庫
SQL> ALTER SYSTEM SWITCH LOGFILE;

--在備庫上檢查是否歸檔和應用
SQL> SELECT SEQUENCE#, FIRST_TIME,NEXT_TIME,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

--檢視資料庫角色
SQL> select database_role,protection_mode,protection_level,switchover_status from v$database;