oracle10g 物理standby dataguard 建立過程
實驗環境說明
1：primary server：
hostname：rac1
IP：192.168.55.119
ORACLE_SID=pdg
db_unique_name=pdg
已經安裝完資料庫軟體，並用dbca建立了資料庫例項
2、standby server：
hostname：rac2
IP：192.168.55.120
ORACLE_SID=sdg
db_unique_name=sdg
只安裝了資料庫軟體，沒有用dbca建立例項
以下是具體操作步驟
1：在primary庫上執行
[root@rac1 ~]# more /etc/sysconfig/network
NETWORKING=yes
HOSTNAME=rac1
GATEWAY=192.168.123.254
[root@rac1 ~]#
[root@rac1 ~]# more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 rac1 localhost.localdomain localhost
192.168.55.119 rac1
192.168.55.120 rac2
[root@rac1 ~]#
[oracle@rac1 ~]$ more .bash_profile
在檔案最後新增如下行：
# Set env for oracle10g
export ORACLE_BASE=/home/oracle
export ORACLE_HOME=$ORACLE_BASE/10.2.0
export ORACLE_SID=pdg
export PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin
然後安裝資料庫軟體，建立資料庫
[oracle@rac1 pdg]$ mkdir -p /home/oracle/archivelog //建立歸檔日誌路徑
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> alter database force logging; //(不啟用的命令是 alter database no force logging;）
Database altered.
SQL> alter system set db_unique_name=pdg scope=spfile;
System altered.
SQL> alter system set log_archive_config='dg_config=(pdg,sdg)' scope=both;
System altered.
SQL> alter system set log_archive_dest_1='location=/home/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdg';
System altered.
SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;
System altered.
SQL> alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE scope=spfile;
System altered.
SQL> alter system set LOG_ARCHIVE_FORMAT='log%t_%s_%r.arc' scope=spfile;
System altered.
SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount;
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 67110868 bytes
Database Buffers 146800640 bytes
Redo Buffers 2973696 bytes
Database mounted.
SQL>
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /home/oracle/archivelog
Oldest online log sequence 1
Next log sequence to archive 2
Current log sequence 2
SQL>
SQL> create pfile from spfile;
File created.
建立遠端登陸密碼檔案
[oracle@rac1 dbs]$ orapwd file=orapwpdg password=test entries=10
[root@rac1 ~]# su - oracle
[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs
[oracle@rac1 dbs]$
[oracle@rac1 dbs]$ ls
hc_pdg.dat init.ora initdw.ora initpdg.ora lkPDG orapwpdg spfilepdg.ora
修改 initpdg.or 引數，在最下面加入如下行
++++++++++++++++++++++++++++++++++++++++
#--------配置standby角色的引數用於角色轉換
FAL_SERVER='pdg'
FAL_CLIENT='sdg'
DB_FILE_NAME_CONVERT='/home/oracle/oradata/sdg','/home/oracle/oradata/pdg'
LOG_FILE_NAME_CONVERT='/home/oracle/oradata/sdg','/home/oracle/oradata/pdg'
alter system set STANDBY_FILE_MANAGEMENT='AUTO'
然後檢視最後修改後的initpdg.ora檔案
[oracle@rac1 dbs]$ more initpdg.ora
pdg.__db_cache_size=146800640
pdg.__java_pool_size=4194304
pdg.__large_pool_size=4194304
pdg.__shared_pool_size=58720256
pdg.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/pdg/adump'
*.background_dump_dest='/home/oracle/admin/pdg/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/oradata/pdg/control01.ctl','/home/oracle/oradata/pdg/control02.ctl','/home/oracle/oradata/pdg/control03.ctl'
*.core_dump_dest='/home/oracle/admin/pdg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='pdg'
*.db_unique_name='PDG'
*.job_queue_processes=10
*.log_archive_config='dg_config=(pdg,sdg)'
*.log_archive_dest_1='location=/home/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=pdg'
*.log_archive_dest_2='SERVICE=sdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sdg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='log%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=216006656
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/pdg/udump'
#-------- 配置standby角色的引數用於角色轉換
FAL_SERVER='sdg'
FAL_CLIENT='pdg'
DB_FILE_NAME_CONVERT='/home/oracle/oradata/sdg','/home/oracle/oradata/pdg'
LOG_FILE_NAME_CONVERT='/home/oracle/oradata/sdg','/home/oracle/oradata/pdg'
STANDBY_FILE_MANAGEMENT='AUTO'
然後重建spfile檔案
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> create spfile from pfile;
File created.
可以用 show parameter xxx 來驗證是否生效
重新建立監聽檔案，並保證可以用tnsping通對方
[oracle@rac1 pdg]$ cd $ORACLE_HOME/network/admin
[oracle@rac1 admin]$ ls
listener.ora samples shrept.lst tnsnames.ora
[oracle@rac1 admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener
.ora
# Generated by Oracle configuration tools.
PDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.119)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_PDG =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)
[oracle@rac1 admin]$
[oracle@rac1 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames
.ora
# Generated by Oracle configuration tools.
PDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdg)
)
)
SDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.120)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdg)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
[oracle@rac1 admin]$
建立standby資料庫控制檔案
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL>
SQL> alter database create standby controlfile as '/home/oracle/sdg01.ctl'; //
Database altered.
SQL>
然後在primary庫上做rman備份
[oracle@rac1 ~]$ mkdir -p /home/oracle/rmanbackup
[oracle@rac1 ~]$
[oracle@rac1 ~]$ rman target /
rman: can't open target
[oracle@rac1 ~]$ export PATH=$ORACLE_HOME/bin:$PATH
[oracle@rac1 ~]$ rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Mar 5 21:46:46 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PDG (DBID=3413940343)
RMAN> configure controlfile autobackup on;
using target database control file instead of recovery catalog
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN> backup database format='/home/oracle/rmanbackup/%d_%s.bak' plus archivelog;
Starting backup at 05-MAR-14
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=142 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=2 recid=1 stamp=841441754
channel ORA_DISK_1: starting piece 1 at 05-MAR-14
channel ORA_DISK_1: finished piece 1 at 05-MAR-14
piece handle=/home/oracle/10.2.0/dbs/01p2eoet_1_1 tag=TAG20140305T214916 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08
Finished backup at 05-MAR-14
Starting backup at 05-MAR-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/home/oracle/oradata/pdg/system01.dbf
input datafile fno=00003 name=/home/oracle/oradata/pdg/sysaux01.dbf
input datafile fno=00005 name=/home/oracle/oradata/pdg/example01.dbf
input datafile fno=00002 name=/home/oracle/oradata/pdg/undotbs01.dbf
input datafile fno=00004 name=/home/oracle/oradata/pdg/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-MAR-14
channel ORA_DISK_1: finished piece 1 at 05-MAR-14
piece handle=/home/oracle/rmanbackup/PDG_2.bak tag=TAG20140305T214926 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:03:16
Finished backup at 05-MAR-14
Starting backup at 05-MAR-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=3 recid=2 stamp=841441963
channel ORA_DISK_1: starting piece 1 at 05-MAR-14
channel ORA_DISK_1: finished piece 1 at 05-MAR-14
piece handle=/home/oracle/10.2.0/dbs/03p2eolc_1_1 tag=TAG20140305T215244 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 05-MAR-14
Starting Control File and SPFILE Autobackup at 05-MAR-14
piece handle=/home/oracle/10.2.0/dbs/c-3413940343-20140305-00 comment=NONE
Finished Control File and SPFILE Autobackup at 05-MAR-14
RMAN>
然後將rman備份的資料檔案、引數檔案、備份的控制檔案、監聽的配置檔案都傳到 standby 庫上
[oracle@rac1 dbs]$ scp initpdg.ora oracle@192.168.50.120:/home/oracle/10.2.0/dbs
[oracle@rac1 dbs]$ scp 01p2eoet_1_1 03p2eolc_1_1 c-3413940343-20140305-00 oracle@192.168.50.120:/home/oracle/10.2.0/dbs
[oracle@rac1 admin]$ scp listener.ora oracle@192.168.50.120:/home/oracle/10.2.0/network/admin
[oracle@rac1 admin]$ scp tnsnames.ora oracle@192.168.50.120:/home/oracle/10.2.0/network/admin
[oracle@rac1 admin]$ scp /home/oracle/rmanbackup/PDG_2.bak oracle@192.168.50.120:/home/oracle/rmanbackup
[oracle@rac1 ~]$ scp /home/oracle/sdg01.ctl oracle@192.168.50.120:/home/oracle/oradata/sdg
2：在standby庫上執行
[oracle@rac2 ~]$ mkdir -p /home/oracle/admin/sdg/adump
[oracle@rac2 ~]$
[oracle@rac2 ~]$ mkdir -p /home/oracle/admin/sdg/bdump
[oracle@rac2 ~]$
[oracle@rac2 ~]$ mkdir -p /home/oracle/admin/sdg/cdump
[oracle@rac2 ~]$
[oracle@rac2 ~]$ mkdir -p /home/oracle/admin/sdg/udump
[oracle@rac2 ~]$
[oracle@rac2 ~]$ mkdir -p /home/oracle/oradata/sdg/
[oracle@rac2 ~]$
[oracle@rac2 ~]$ mkdir -p /home/oracle/archivelog
[oracle@rac2 ~]$
[oracle@rac2 dbs]$ mv initpdg.ora initsdg.ora[oracle@rac2 sdg]$ pwd
/home/oracle/oradata/sdg
[oracle@rac2 sdg]$ ls -l
total 6908
-rw-r----- 1 oracle dba 7061504 Mar 5 22:27 sdg01.ctl
[oracle@rac2 sdg]$ mv sdg01.ctl control01.ctl
[oracle@rac2 sdg]$ cp control01.ctl control02.ctl
[oracle@rac2 sdg]$ cp control01.ctl control03.ctl
建立遠端登陸密碼檔案
[oracle@rac2 dbs]$ orapwd file=orapwsdg password=test entries=10
檢視standby上的監聽檔案
[root@rac2 ~]# su - oracle
[oracle@rac2 ~]$
[oracle@rac2 ~]$ cd $ORACLE_HOME/network/admin
[oracle@rac2 admin]$ ls
listener.ora samples shrept.lst sqlnet.log sqlnet.ora tnsnames.ora
[oracle@rac2 admin]$ more listener.ora
# listener.ora Network Configuration File: /home/oracle/10.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_SDG =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /home/oracle/10.2.0)
(PROGRAM = extproc)
)
)
SDG =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.120)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
[oracle@rac2 admin]$
[oracle@rac2 admin]$
[oracle@rac2 admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /home/oracle/10.2.0/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.119)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = pdg)
)
)
SDG =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.50.120)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = sdg)
)
)
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)
然後修改 standby 庫的 initsdg.ora 檔案
[oracle@rac2 dbs]$ more initsdg.ora
pdg.__db_cache_size=146800640
pdg.__java_pool_size=4194304
pdg.__large_pool_size=4194304
pdg.__shared_pool_size=58720256
pdg.__streams_pool_size=0
*.audit_file_dest='/home/oracle/admin/sdg/adump'
*.background_dump_dest='/home/oracle/admin/sdg/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/home/oracle/oradata/sdg/control01.ctl','/home/oracle/oradata/sdg/control02.ctl','/home/oracle/oradata/sdg/control03.ctl'
*.core_dump_dest='/home/oracle/admin/sdg/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='pdg'
*.db_unique_name='SDG'
*.job_queue_processes=10
*.log_archive_config='dg_config=(pdg,sdg)'
*.log_archive_dest_1='location=/home/oracle/archivelog VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sdg'
*.log_archive_dest_2='SERVICE=pdg LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=pdg'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='log%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=216006656
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/home/oracle/admin/sdg/udump'
#-------- 配置standby角色的引數用於角色轉換
FAL_SERVER='pdg'
FAL_CLIENT='sdg'
DB_FILE_NAME_CONVERT='/home/oracle/oradata/pdg','/home/oracle/oradata/sdg'
LOG_FILE_NAME_CONVERT='/home/oracle/oradata/pdg','/home/oracle/oradata/sdg'
STANDBY_FILE_MANAGEMENT='AUTO'
此處一定要注意：
即使在 primary 庫上用rman做了控制檔案的備份，在standby庫上也不能用rman中恢復出來的控制檔案，
否則的話，會報錯如下錯誤
SQL> alter database mount standby database;
alter database mount standby database
*
ERROR at line 1:
ORA-01665: control file is not a standby control file
因此一定要用之前在primary庫上用 alter database create standby controlfile as '/home/oracle/sdg01.ctl'建立的控制檔案
[oracle@rac2 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Mar 5 22:58:51 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/home/oracle/10.2.0/dbs/initsdg.ora';
ORACLE instance started.
Total System Global Area 218103808 bytes
Fixed Size 1218604 bytes
Variable Size 67110868 bytes
Database Buffers 146800640 bytes
Redo Buffers 2973696 bytes
SQL>
SQL> alter database mount standby database; //緊接著上一步執行
Database altered.
RMAN> catalog backuppiece '/home/oracle/rmanbackup/PDG_2.bak'; // 在rman中註冊備份片
cataloged backuppiece
backup piece handle=/home/oracle/rmanbackup/PDG_2.bak recid=1 stamp=841485100
RMAN>
RMAN> restore database;
Starting restore at 06-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=152 devtype=DISK
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /home/oracle/oradata/sdg/system01.dbf
restoring datafile 00002 to /home/oracle/oradata/sdg/undotbs01.dbf
restoring datafile 00003 to /home/oracle/oradata/sdg/sysaux01.dbf
restoring datafile 00004 to /home/oracle/oradata/sdg/users01.dbf
restoring datafile 00005 to /home/oracle/oradata/sdg/example01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/rmanbackup/PDG_2.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/rmanbackup/PDG_2.bak tag=TAG20140305T214926
channel ORA_DISK_1: restore complete, elapsed time: 00:02:07
Finished restore at 06-MAR-14
RMAN>
然後驗證 standby庫已經啟動到了 mount狀態
[oracle@rac2 sdg]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Mar 6 10:01:29 2014
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28373936/viewspace-1722275/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle10g 建立物理DataGuard（一）Oracle
- Oracle10g 建立物理DataGuard（二）Oracle
- Oracle10g 建立物理DataGuard（三）Oracle
- DataGuard搭建物理StandBy
- Oracle DataGuard環境failover後通過舊備份建立物理StandbyOracleAI
- 【DATAGUARD 學習】使用duplicate 建立物理standby 資料庫資料庫
- Dataguard物理Standby Switchover 角色轉換
- 一步一步學DataGuard(5)物理standby之建立示例
- dataguard之物理standby 日誌切換
- ORACLE10G 物理standby轉為邏輯standbyOracle
- oracle10g data guard建立物理standby資料庫的例子Oracle資料庫
- 通過RMAN Duplicate建立Oracle物理standby備庫Oracle
- Oracle11g的Dataguard測試，建立物理備庫(Physical Standby Database)OracleDatabase
- ORACLE10g DataGuard 配置Physical Standby DatabaseOracleDatabase
- dataguard之物理standby庫failover 切換AI
- standby 資料庫的建立過程資料庫
- 使用rman建立standby database的過程Database
- 配置Oracle11g的Dataguard測試，建立物理備庫(Physical Standby Database)OracleDatabase
- DataGuard---->物理StandBy的角色切換之switchover
- 建立一個standby database的全過程Database
- Standby資料庫簡單建立過程資料庫
- 建立測試物理Standby日誌
- RMAN DUPLICATE建立DataGuard物理備庫
- Oracle10g logical standby 建立Oracle
- ORACLE DUPLICATE建立物理standby資料庫Oracle資料庫
- 使用RMAN建立物理Standby資料庫資料庫
- dataguard-建立物理備庫全程解析
- [轉帖]Oracle9i Standby (Dataguard) 建立Oracle
- 關於建立DataGuard Physical Standby資料庫資料庫
- 【DataGuard】使用GC建立的物理DataGuard主備庫pfile比較GC
- Oracle11g R2之Dataguard搭建物理standbyOracle
- 附錄A Oracle Dataguard 物理Standby跨平臺組合支援列表Oracle
- oracle10g之standby tempfile自動建立Oracle
- Oracle 11g RAC Data Guard 物理standby 建立Oracle
- Data Guard學習之物理standby建立步驟
- 【DATAGUARD】 將11g物理備庫轉換為Snapshot Standby
- 配置 Oracle 10g 單例項物理dataguard和邏輯standbyOracle 10g單例
- 【DataGuard】10g物理standby主備switchover方式切換詳述