oracle 10g physical standby database creation

前言：
    主庫os version:centos 3.9
    備庫os version:rhel5
    oracle version:oracle10.2.0.1
構建物理備庫步驟如下:
1.在主庫，開啟強制日誌功能
  alter database force logging;--可從v$database檢視
2,主備庫皆需有對應的密碼檔案（對於dg必須配置喲）
  orapwd file=./orapwcentos password=system entries=10 force=y  --orapw+&ORACLE_SID
  orapwd file=./orapwrhel password=system entries=10 force=y
3,主庫spfile配置如下
[oracle@localhost dbs]$ more spfilecentos.ora

centos.__db_cache_size=192937984
centos.__java_pool_size=4194304
centos.__large_pool_size=4194304
centos.__shared_pool_size=79691776
centos.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/centos/adump'
*.background_dump_dest='/oracle/admin/centos/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/oracle/oradata/centos/control01.ctl','/oracle/oradata/centos/control02.ctl','/oracle/oradata/centos/control03.ctl'
*.core_dump_dest='/oracle/admin/centos/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='centos'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=centosXDB)'
*.job_queue_processes=10
*.log_archive_config='dg_config=(centos,rhel)' ---centos與rhel為db_unique_name(for primary and standby db)
*.log_archive_dest_1='location=/oracle/centosarch' ---同下
*.log_archive_dest_2='SERVICE=rhel lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=rhel'  ---此引數可透過vi寫入或者alter system動態修改皆可
*.log_archive_format='%t_%s_%r.dbf'
*.log_archive_max_processes=30 --歸檔程式數量
*.open_cursors=300
*.pga_aggregate_target=94371840
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=283115520
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/centos/udump'

---注：下為用於主庫切換為備庫的相關引數配置喲
FAL_SERVER=boston  --net service name
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'  --用於路徑轉換喲，如主備庫路徑相同，不用此引數
LOG_FILE_NAME_CONVERT=
 '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'   --同上
STANDBY_FILE_MANAGEMENT=AUTO  --動態根據主庫的動作在備庫進行相關的增刪操作

 '/arch1/boston/','/arch1/chicago/','/arch2/boston/','/arch2/chicago/'
STANDBY_FILE_MANAGEMENT=AUTO

4,如主庫未開啟歸檔，配置歸檔模式
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;

5,對主庫的datafile,logfile,controlfile,tempfile作個全備(備時，限制使用者訪問資料庫）
sqlplus sys/system as sysdba
select * from v$datafile;
select * from v$tempfile;
select * from v$log;
select * from v$controlfile;

利用tar，進行對以上內容進行壓縮

tar -cvf x.tar 以上相關檔案列表內容

6，根據主庫構建備庫所用控制檔案
在主庫上，
startup mount
alter database create standby controlfile as '/tmp/rhel.ora';
alter database open;

7,為備庫構建如下內容(依主庫spfile為藍本）
create pfile='/tmp/initrhel.ora' from pfile;--主庫上
如下：備庫pfile內容
-bash-3.1$ more spfilerhel.ora

centos.__db_cache_size=188743680
rhel.__db_cache_size=180355072
centos.__java_pool_size=4194304
rhel.__java_pool_size=4194304
centos.__large_pool_size=4194304
rhel.__large_pool_size=4194304
centos.__shared_pool_size=83886080
rhel.__shared_pool_size=92274688
centos.__streams_pool_size=0
rhel.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/centos/adump'  ---相關dump目錄，我是主備庫配置一致
*.background_dump_dest='/oracle/admin/centos/bdump'
*.compatible='10.2.0.1.0'
DB_NAME='centos'  --主備庫的db_name引數值相同
DB_UNIQUE_NAME='rhel' --備庫的db_unique_name不同於db_name（主庫上可以不配置這個引數喲）<>
LOG_ARCHIVE_CONFIG='DG_CONFIG=(centos,rhel)'
CONTROL_FILES='/oracle/oradata/centos/control01.ctl','/oracle/oradata/centos/control02.ctl','/oracle/oradata/centos/control03.ctl' --這個備庫控制檔案，源於create pfile='/tmp/initrhel.ora' from pfile，在備庫來個多元映象就ok
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_1=
 'LOCATION=/oracle/rhelarch
  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
  DB_UNIQUE_NAME=rhel'
LOG_ARCHIVE_DEST_2=  ---此引數是用於把備庫切換為主庫時所用的
 'SERVICE=centos LGWR ASYNC  --log_archive_dest_2中的service值，請注意是netca配置的net service name(一般就是global database name)
  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
  DB_UNIQUE_NAME=centos'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=centos  ---net service name(產生歸檔的庫）
FAL_CLIENT=rhel  --同上（接受歸檔的庫）
 

8,如果是windows系統，在備庫上配置一個oracle service

oradim -NEW -SID rhel -INTPWD system -STARTMODE manual

9,為主備庫建立監聽器和tnsnames.ora

主庫
[oracle@localhost dbs]$ cd /oracle/product/10.2.0/db_1/network/admin/
[oracle@localhost admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RHEL =  --備
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =centos) --service_name是global database name
    )
  )

CENTOS = --主
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = centos)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

[oracle@localhost admin]$

[oracle@localhost admin]$ more listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =   --這個小節很重要，我是採用netmgr手工新增
      (GLOBAL_DBNAME = centos)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME = centos)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

--備庫
-bash-3.1$ cd /oracle/product/10.2.0/db_1/network/admin/
-bash-3.1$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

RHEL =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.3)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =centos)
    )
  )

CENTOS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.125.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = centos)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

-bash-3.1$ more listener.ora
# listener.ora Network Configuration File: /oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
  (SID_DESC =
      (GLOBAL_DBNAME=centos)
      (ORACLE_HOME = /oracle/product/10.2.0/db_1)
      (SID_NAME=rhel)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

-bash-3.1$

以上配置完，記得用tnsping net service name測試，及sqlplus service name as sysdba測試(在主備庫）

10,在備庫上,以mount方式載入備庫
export ORACLE_SID=rhel --if being rhel or unix os
startup mount
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;--備庫日誌恢復應用

11,檢測備庫日誌應用
Step 1   Identify the existing archived redo log files.

On the standby database, query the V$ARCHIVED_LOG view to identify existing files in the archived redo log. For example:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME
---------- ------------------ ------------------
         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03

3 rows selected.

Step 2   Force a log switch to archive the current online redo log file.

On the primary database, issue the ALTER SYSTEM SWITCH LOGFILE statement to force a log switch and archive the current online redo log file group:

SQL> ALTER SYSTEM SWITCH LOGFILE;

Step 3   Verify the new redo data was archived on the standby database.

On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database:

SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME
  2>  FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

 SEQUENCE# FIRST_TIME         NEXT_TIME
---------- ------------------ ------------------
         8 11-JUL-02 17:50:45 11-JUL-02 17:50:53
         9 11-JUL-02 17:50:53 11-JUL-02 17:50:58
        10 11-JUL-02 17:50:58 11-JUL-02 17:51:03
        11 11-JUL-02 17:51:03 11-JUL-02 18:34:11
4 rows selected.

The archived redo log files are now available to be applied to the physical standby database.

Step 4   Verify new archived redo log files were applied.

On the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied.

SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
  2  ORDER BY SEQUENCE#;

SEQUENCE# APP
--------- ---
        8 YES
        9 YES
       10 YES
       11 YES

4 rows selected.

 

12,如果要對物理備庫進行查詢，須先取消備庫日誌應用恢復，然後以只讀方式開啟備庫

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE cancel immediate;
alter database open read only;
13,當然，可以從第12步還原回備庫日誌應用模式

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;--備庫日誌恢復應用