10GR2下建立物理standby STEP BY STEP
這篇文章主要介紹如何在10g下建立物理standby。
伺服器資訊：
主庫：
IP:10.2.98.10
SID:Primary
備庫：
IP:10.2.98.11
SID:Primary
OS平臺都是基於linux，資料庫版本是10.2.0.3
具體步驟如下：
1、在主資料庫執行force logging
SQL> alter database force logging;
Database altered.
2、在主庫建立密碼檔案（如果已經存在則略過此步驟）
3、在主庫建立standby redo log
在主庫上建立standby log，大小與主庫聯機日誌大小一樣，組數至少大1（這個是可選，是為了角色切換方便）
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/primary/standbyredo04.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/primary/standbyredo5.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/primary/standbyredo6.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/primary/standbyredo7.log' size 50m;
Database altered.
4、設定主庫初始化引數
SQL> create pfile from spfile;
File created.
然後編輯生成的pfile，主要修改的地方如下：
DB_UNIQUE_NAME=primary
LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/ 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
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=4
COMPATIBLE = 10.2.0.3
#以下引數是為了角色切換設定
FAL_CLIENT = primary
FAL_SERVER = standby
STANDBY_FILE_MANAGEMENT =AUTO
log_file_name_convert='/u01/oracle/oradata/primary/','/u01/oracle/oradata/primary/'
5、設定歸檔模式
SQL> startup mount pfile=?/dbs/initprimary.ora
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 125829992 bytes
Database Buffers 37748736 bytes
Redo Buffers 2932736 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> create spfile from pfile;
File created.
6、在主庫用RMAN做一個全備
[oracle@primary ~]$ rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 17 02:50:40 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1462491904)
RMAN> backup database format='/u01/backup/%U_%s.bak';
Starting backup at 17-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=118 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/oracle/oradata/primary/system01.dbf
input datafile fno=00002 name=/u01/oracle/oradata/primary/undotbs01.dbf
input datafile fno=00003 name=/u01/oracle/oradata/primary/sysaux01.dbf
input datafile fno=00004 name=/u01/oracle/oradata/primary/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-07
channel ORA_DISK_1: finished piece 1 at 17-JUN-07
piece handle=/u01/backup/01ikfgkh_1_1_1.bak tag=TAG20070617T025056 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-JUN-07
channel ORA_DISK_1: finished piece 1 at 17-JUN-07
piece handle=/u01/backup/02ikfglk_1_1_2.bak tag=TAG20070617T025056 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 17-JUN-07
RMAN> sql "Alter System Archive Log Current";
sql statement: Alter System Archive Log Current
RMAN> Backup filesperset 10 ArchiveLog all format='/u01/backup/%U_%s.bak';
Starting backup at 17-JUN-07
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=22 recid=1 stamp=625459450
input archive log thread=1 sequence=23 recid=2 stamp=625459916
input archive log thread=1 sequence=24 recid=3 stamp=625459929
channel ORA_DISK_1: starting piece 1 at 17-JUN-07
channel ORA_DISK_1: finished piece 1 at 17-JUN-07
piece handle=/u01/backup/03ikfgmr_1_1_3.bak tag=TAG20070617T025210 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-JUN-07
把備份檔案傳到備庫中
[oracle@primary backup]$ pwd
/u01/backup
[oracle@primary backup]$ ls
01ikfgkh_1_1_1.bak 02ikfglk_1_1_2.bak 03ikfgmr_1_1_3.bak
[oracle@primary backup]$ pwd
/u01/backup
[oracle@primary backup]$ scp * 10.2.98.11:`pwd`
oracle@10.2.98.11's password:
01ikfgkh_1_1_1.bak 100% 298MB 3.0MB/s 01:39
02ikfglk_1_1_2.bak 100% 6976KB 2.3MB/s 00:03
03ikfgmr_1_1_3.bak 100% 178KB 178.0KB/s 00:00
也可以用熱備的方法備份，把資料檔案和歸檔傳到備庫中
7、在主庫建立備用伺服器控制檔案
SQL> alter database create standby controlfile as '/u01/backup/standby.ctl';
Database altered.
複製到備庫，並複製多份
--主庫
[oracle@primary backup]$ scp standby.ctl 10.2.98.11:/u01/oracle/oradata/primary
oracle@10.2.98.11's password:
standby.ctl 100% 6896KB 3.4MB/s 00:02
--備庫
[oracle@standby backup]$ mkdir -p /u01/oracle/oradata/primary
[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control01.ctl
[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control02.ctl
[oracle@standby backup]$ cp standby.ctl /u01/oracle/oradata/primary/control03.ctl
8、設定備庫引數檔案
從主庫傳送pfile到備庫中。
[oracle@primary dbs]$ scp initprimary.ora 10.2.98.11:`pwd`
oracle@10.2.98.11's password:
initprimary.ora 100% 1528 1.5KB/s 00:00
修改如下引數：
*.DB_UNIQUE_NAME='standby'
*.FAL_CLIENT='standby'
*.FAL_SERVER='primary'
*.LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary,standby)'
*.LOG_ARCHIVE_DEST_1='LOCATION=/u01/archivelog/ 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'
*.STANDBY_FILE_MANAGEMENT =AUTO
*.control_files='/u01/oracle/oradata/primary/control01.ctl','/u01/oracle/oradata/primary/control02.ctl','/u01/oracle/oradata/primary/control03.ctl'
*.COMPATIBLE = 10.2.0.3
*.log_file_name_convert='/u01/oracle/oradata/primary/','/u01/oracle/oradata/primary/'
9、在備庫建立密碼檔案
[oracle@standby dbs]$ orapwd file=orapwprimary password=suk entries=10
在備庫上建立目錄：
[oracle@standby dbs]$ mkdir -p /u01/oracle/admin/primary/{adump,bdump,cdump,udump}
10、在備庫端還原資料庫
[oracle@standby dbs]$ sqlplus "/as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sun Jun 24 00:19:36 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount pfile=?/dbs/initprimary.ora
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260696 bytes
Variable Size 75498344 bytes
Database Buffers 88080384 bytes
Redo Buffers 2932736 bytes
SQL> alter database mount standby database;
Database altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning and Data Mining options
[oracle@standby dbs]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.3.0 - Production on Sun Jun 24 00:20:10 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: PRIMARY (DBID=1463363807, not open)
RMAN> restore database;
Starting restore at 24-JUN-07
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 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 /u01/oracle/oradata/primary/system01.dbf
restoring datafile 00002 to /u01/oracle/oradata/primary/undotbs01.dbf
restoring datafile 00003 to /u01/oracle/oradata/primary/sysaux01.dbf
restoring datafile 00004 to /u01/oracle/oradata/primary/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/backup/01il1ila_1_1_1.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/01il1ila_1_1_1.bak tag=TAG20070623T231553
channel ORA_DISK_1: restore complete, elapsed time: 00:00:36
Finished restore at 24-JUN-07
RMAN> restore archivelog all;
Starting restore at 24-JUN-07
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=10
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=12
channel ORA_DISK_1: reading from backup piece /u01/backup/03il1inl_1_1_3.bak
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/backup/03il1inl_1_1_3.bak tag=TAG20070623T231708
channel ORA_DISK_1: restore complete, elapsed time: 00:00:06
Finished restore at 24-JUN-07
11、分別在主庫和備庫配置監聽並啟動
在主庫，listener.ora配置如下：
[oracle@primary admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
(SID_NAME = primary)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
)
在備庫，listener.ora配置如下：
[oracle@primary admin]$ more listener.ora
# listener.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = primary)
(ORACLE_HOME = /u01/oracle/product/10.2.0/db_1)
(SID_NAME = primary)
)
)
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
)
12、在主庫和備庫分別配置tnsnames
在主庫和備庫的tnsnames.ora都做以下配置：
[oracle@primary admin]$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
PRIMARY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.98.10)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)
STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.2.98.11)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = primary)
)
)
13、備庫端建立spfile
在備庫執行：
SQL> create spfile from pfile;
File created.
13、啟動備庫
startup mount
14、在備庫建立standby redo log和online redo log
在備庫上建立standby log，大小與主庫聯機日誌大小一樣，組數至少大1；(如果是最大效能保護模式，可以不新增standby log，但是建議新增上，避免損失更多資料)
在備庫中不需要建立聯機日誌，它會自動建立與主庫一樣的日誌的。
首先在主庫切換一次日誌，使備庫中記錄的原主庫的standby redo log資訊被清除：
主庫執行：
SQL> alter system switch logfile;
稍等一會，等在主庫中查詢v$standby_log沒有原來主庫standby log的資訊返回時，再在備庫中新增standby redo log。
如果備庫正在處於恢復狀態，先停止恢復，否則新增standby log會報錯：
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
--取消恢復狀態
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> alter database add standby logfile group 4 '/u01/oracle/oradata/primary/standbyredo4.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 5 '/u01/oracle/oradata/primary/standbyredo5.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 6 '/u01/oracle/oradata/primary/standbyredo6.log' size 50m;
Database altered.
SQL> alter database add standby logfile group 7 '/u01/oracle/oradata/primary/standbyredo7.log' size 50m;
Database altered.
15、啟動redo apply
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
Database altered.
16、檢查物理standby情況
1）在備庫檢查當前的archivelog
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------- -------------------
22 2007-06-17 02:42:12 2007-06-17 02:44:05
23 2007-06-17 02:44:05 2007-06-17 02:51:56
24 2007-06-17 02:51:56 2007-06-17 02:52:09
2）在主庫新建一個表，插入資料，然後切換日誌
SQL> create table test(id int);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
3）再次檢查備庫的archivelog
SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;
SEQUENCE# FIRST_TIME NEXT_TIME
---------- ------------------- -------------------
22 2007-06-17 02:42:12 2007-06-17 02:44:05
23 2007-06-17 02:44:05 2007-06-17 02:51:56
24 2007-06-17 02:51:56 2007-06-17 02:52:09
25 2007-06-17 02:52:09 2007-06-17 04:16:23
26 2007-06-17 04:16:23 2007-06-17 04:19:16
27 2007-06-17 04:19:16 2007-06-17 04:21:59
此時檢查alret檔案，可以看到類似於下面的資訊：
Sun Jun 24 16:36:32 2007
Primary database is in MAXIMUM PERFORMANCE mode
RFS[2]: Successfully opened standby log 4: '/u01/oracle/oradata/primary/standbyredo04.log'
Sun Jun 24 16:36:33 2007
Media Recovery Log /u01/archivelog/1_15_626106231.dbf
這表示應用歸檔成功。
4）在備庫查詢資料
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
Database altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> select * from test;
ID
----------
1
可以，資料已經正常同步。
至此，最大效能保護模式下的DG配置完成。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-63824/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- React Step by StepReact
- 2.4.8 Step 7: 建立spfile
- Command 模式 Step by Step模式
- Shell Step by Step (4) —— Cron & Echo
- Promise的實現(step by step)Promise
- 2.4.6 Step 5: (Windows)建立例項Windows
- Step by Step Data Replication Using Oracle GoldenGateOracleGo
- TIDB DM資料同步step by stepTiDB
- Step by Step 安裝 BizTalk Server 2009Server
- 單步除錯 step into/step out/step over 區別詳解除錯
- ABP應用開發（Step by Step）-下篇
- ABP應用開發（Step by Step）-上篇
- 實時 Linux 抖動分析 Step by stepLinux
- 2.4.10 Step 9:手工建立資料庫資料庫
- Linkerd 2.10(Step by Step)—多叢集通訊
- Sql Server Linux(Redhat) Distributed Availability Group Setup — step by stepSQLServerLinuxRedhatAI
- Step-by-step，打造屬於自己的vue ssrVue
- Adaboost Algorithm StepGo
- 深度學習之step by step搭建神經網路深度學習神經網路
- Linkerd 2.10(Step by Step)—使用 Kustomize 自定義 Linkerd 的配置
- Linkerd 2.10(Step by Step)—設定服務配置檔案
- 2.4.11 Step 10: 建立額外的表空間
- Linkerd 2.10(Step by Step)—4. 如何配置外部 Prometheus 例項Prometheus
- 使用Eclipse 安裝 構建Maven專案 (step-by-step)EclipseMaven
- HTML step 屬性HTML
- 2.4.5 Step 4: 建立初始化引數檔案
- 【Step-By-Step】高頻面試題深入解析 / 週刊06面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊07面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊04面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊05面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊03面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊01面試題
- 【Step-By-Step】高頻面試題深入解析 / 週刊02面試題
- 阿里雲數倉Dataworks資料匯出到檔案step by step阿里
- Linkerd 2.10(Step by Step)—將 GitOps 與 Linkerd 和 Argo CD 結合使用GitGo
- Linkerd 2.10(Step by Step)—1. 將您的服務新增到 Linkerd
- step1 補充
- [完結] Learn Vue 2: Step By Step [Laracasts 免費視訊中文語音]VueAST