12c 傳統資料庫的ADG初體驗
實驗環境
OS：oracle linux 6.6
DB：oracle 12.1.0.1
主庫：chicago
備庫：boston
主庫操作
1. 主庫資料庫檔案、控制檔案、日誌檔案的規劃
SYS@chicago >
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile
union
select value from v$parameter where name='spfile';
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/chicago/control01.ctl
/home/oracle/dbfile/chicago/control02.ctl
/home/oracle/dbfile/chicago/redo01.log
/home/oracle/dbfile/chicago/redo02.log
/home/oracle/dbfile/chicago/redo03.log
/home/oracle/dbfile/chicago/sysaux01.dbf
/home/oracle/dbfile/chicago/system01.dbf
/home/oracle/dbfile/chicago/temp01.dbf
/home/oracle/dbfile/chicago/undotbs01.dbf
/home/oracle/dbfile/chicago/users01.dbf
/u01/app/oracle/product/12.1.0.1/db_1/dbs/spfilechicago.ora
11 rows selected.
2. 主庫現有3組redo，建立4組standby logfile group
[oracle@snow ~]$ sqlplus sys/oracle@chicago as sysdba
SYS@chicago > select group#,bytes/1024/1024 from v$log;
GROUP# BYTES/1024/1024
---------- ---------------
1 50
2 50
3 50
SYS@chicago >
alter database add standby logfile group 4 ('/home/oracle/dbfile/chicago/redo04.log') size 50m;
alter database add standby logfile group 5 ('/home/oracle/dbfile/chicago/redo05.log') size 50m;
alter database add standby logfile group 6 ('/home/oracle/dbfile/chicago/redo06.log') size 50m;
alter database add standby logfile group 7 ('/home/oracle/dbfile/chicago/redo07.log') size 50m;
3. 主庫設定foece logging
SYS@chicago > alter database force logging;
SYS@chicago > shutdown immediate
4. 主庫建立pfile
SYS@chicago > create pfile from spfile;
Segmentation fault (core dumped) <== 沒見過這個報錯，重登入
[oracle@snow ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 21 13:03:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@chicago > create pfile from spfile;
File created.
SYS@chicago > exit
5. 向主庫的pfile新增內容
[oracle@snow ~]$ cd $ORACLE_HOME/dbs
[oracle@snow dbs]$ vi initchicago.ora
*.audit_file_dest='/u01/app/oracle/admin/chicago/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
#DB_NAME=PROD1
#CONTROL_FILES='/arch1/PROD1/control1.ctl', '/arch2/PROD1/control2.ctl'
#LOG_ARCHIVE_MAX_PROCESSES=30
*.control_files='/home/oracle/dbfile/chicago/control01.ctl','/home/oracle/dbfile/chicago/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chicago'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)'
*.log_archive_dest_1='LOCATION=/home/oracle/arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1440m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#for ADG
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=boston
FAL_CLIENT=chicago
DB_FILE_NAME_CONVERT='boston','chicago'
LOG_FILE_NAME_CONVERT='boston','chicago'
STANDBY_FILE_MANAGEMENT=AUTO
6. 複製主庫的pfile檔案，修改為備庫的pfile
[oracle@snow dbs]$ cp initchicago.ora initboston.ora
[oracle@snow dbs]$ vi initboston.ora
*.audit_file_dest='/u01/app/oracle/admin/boston/adump'
*.audit_trail='db'
*.compatible='12.1.0.0.0'
*.control_files='/home/oracle/dbfile/boston/control01.ctl','/home/oracle/dbfile/boston/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='chicago'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)'
*.log_archive_dest_1='LOCATION=/home/oracle/arch'
*.log_archive_format='%t_%s_%r.arc'
*.memory_target=1440m
*.open_cursors=300
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
#for ADG
DB_UNIQUE_NAME=boston
LOG_ARCHIVE_CONFIG='DG_CONFIG=(boston,chicago)'
LOG_ARCHIVE_DEST_1=
'LOCATION=/home/oracle/arch
VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2=
'SERVICE=boston ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
FAL_SERVER=chicago
FAL_CLIENT=boston
DB_FILE_NAME_CONVERT='chicago','boston'
LOG_FILE_NAME_CONVERT='chicago','boston'
STANDBY_FILE_MANAGEMENT=AUTO
7. 將備庫的pfile檔案scp到備庫的/u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@snow dbs]$ scp initboston.ora boston:/u01/app/oracle/product/12.1.0/db_1/dbs
oracle@boston's password:
initboston.ora 100% 1141 1.1KB/s 00:00
8. 複製主庫的密碼檔案，修改為備庫的密碼檔案並scp到備庫的/u01/app/oracle/product/12.1.0/db_1/dbs
[oracle@snow dbs]$ cp orapwchicago orapwboston
[oracle@snow dbs]$ scp orapwboston boston:/u01/app/oracle/product/12.1.0/db_1/dbs
oracle@boston's password:
orapwboston 100% 7680 7.5KB/s 00:00
[oracle@snow dbs]$ cd $ORACLE_HOME/network/admin
[oracle@snow admin]$ vi tnsnames
9. 修改主庫的$ORACLE_HOME/network/admin/tnsnames.ora
[oracle@snow admin]$ vi tnsnames.ora
chicago=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.12)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = chicago)
)
)
boston=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.13)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = boston)
)
)
備庫操作
10. 建立備庫所需目錄
[oracle@boston ~]$ export ORACLE_SID=boston
[oracle@boston ~]$ mkdir -p $ORACLE_BASE/admin/boston/adump
[oracle@boston ~]$ mkdir -p dbfile/boston
11. 為備庫建立靜態監聽，此步驟可以使用netmgr
[oracle@boston ~]$ vi $ORACLE_HOME/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = boston)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/db_1)
(SID_NAME = boston)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = boston)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
ADR_BASE_LISTENER = /u01/app/oracle
12. 修改備庫tnsnames.ora
[oracle@boston ~]$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
BOSTON =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.228.13)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = boston)
)
)
13. 備庫reload監聽器
[oracle@boston ~]$ lsnrctl
LSNRCTL> reload
主庫操作
14. 主庫從修改後的pfile建立spfile，啟動例項。此時關於ADG的配置資訊將隨之例項的啟動生效。
[oracle@snow ~]$ sqlplus sys/oracle@chicago as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Tue Apr 21 14:25:31 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@chicago > create spfile from pfile;
File created.
SYS@chicago > startup
備庫操作
15. 備庫從修改後的pfile建立spfile，啟動例項到nomount狀態。
[oracle@boston dbs]$ sqlplus sys/oracle@boston as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 6 13:03:06 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SYS@boston > create spfile from pfile;
File created.
SYS@boston > startup nomount;
ORACLE instance started.
Total System Global Area 459304960 bytes
Fixed Size 2289544 bytes
Variable Size 381681784 bytes
Database Buffers 71303168 bytes
Redo Buffers 4030464 bytes
SYS@boston > exit
16. 使用rman duplicate來線上複製主庫。rman部分輸出比較多，命名語句使用高亮表標示，其它字型為螢幕輸出。
[oracle@boston ~]$ rman target sys/oracle@chicago auxiliary sys/oracle@boston
Recovery Manager: Release 12.1.0.1.0 - Production on Wed May 6 13:05:12 2015
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: CHICAGO (DBID=1487327917)
connected to auxiliary database: CHICAGO (not mounted)
RMAN> duplicate target database for standby from active database dorecover nofilenamecheck;
Starting Duplicate Db at 06-MAY-15
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=21 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
targetfile '/u01/app/oracle/product/12.1.0.1/db_1/dbs/orapwchicago' auxiliary format
'/u01/app/oracle/product/12.1.0/db_1/dbs/orapwboston' ;
}
executing Memory Script
Starting backup at 06-MAY-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Finished backup at 06-MAY-15
contents of Memory Script:
{
restore clone from service 'chicago' standby controlfile;
}
executing Memory Script
Starting restore at 06-MAY-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/dbfile/boston/control01.ctl
output file name=/home/oracle/dbfile/boston/control02.ctl
Finished restore at 06-MAY-15
contents of Memory Script:
{
sql clone 'alter database mount standby database';
}
executing Memory Script
sql statement: alter database mount standby database
contents of Memory Script:
{
set newname for tempfile 1 to
"/home/oracle/dbfile/boston/temp01.dbf";
switch clone tempfile all;
set newname for datafile 1 to
"/home/oracle/dbfile/boston/system01.dbf";
set newname for datafile 3 to
"/home/oracle/dbfile/boston/sysaux01.dbf";
set newname for datafile 4 to
"/home/oracle/dbfile/boston/undotbs01.dbf";
set newname for datafile 6 to
"/home/oracle/dbfile/boston/users01.dbf";
restore
from service 'chicago' clone database
;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/dbfile/boston/temp01.dbf in control file
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 06-MAY-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /home/oracle/dbfile/boston/system01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/dbfile/boston/sysaux01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00004 to /home/oracle/dbfile/boston/undotbs01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /home/oracle/dbfile/boston/users01.dbf
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-MAY-15
sql statement: alter system archive log current
current log archived
contents of Memory Script:
{
restore clone force from service 'chicago'
archivelog from scn 1864000;
switch clone datafile all;
}
executing Memory Script
Starting restore at 06-MAY-15
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=11
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=12
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: using network backup set from service chicago
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=13
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 06-MAY-15
datafile 1 switched to datafile copy
input datafile copy RECID=1 STAMP=878994450 file name=/home/oracle/dbfile/boston/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=878994450 file name=/home/oracle/dbfile/boston/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=878994450 file name=/home/oracle/dbfile/boston/undotbs01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=4 STAMP=878994450 file name=/home/oracle/dbfile/boston/users01.dbf
contents of Memory Script:
{
set until scn 1864100;
recover
standby
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 06-MAY-15
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 11 is already on disk as file /home/oracle/arch/1_11_877609198.arc
archived log for thread 1 with sequence 12 is already on disk as file /home/oracle/arch/1_12_877609198.arc
archived log for thread 1 with sequence 13 is already on disk as file /home/oracle/arch/1_13_877609198.arc
archived log file name=/home/oracle/arch/1_11_877609198.arc thread=1 sequence=11
archived log file name=/home/oracle/arch/1_12_877609198.arc thread=1 sequence=12
archived log file name=/home/oracle/arch/1_13_877609198.arc thread=1 sequence=13
media recovery complete, elapsed time: 00:00:00
Finished recover at 06-MAY-15
Finished Duplicate Db at 06-MAY-15
17. duplicate結束後資料被啟動到mount狀態
[oracle@boston ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Wed May 6 13:08:43 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SYS@boston > select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
MOUNTED PHYSICAL STANDBY
18. 將資料庫啟動到read only
SYS@boston > ALTER DATABASE OPEN;
Database altered.
SYS@boston > select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY PHYSICAL STANDBY
19. 開啟redo apply，達到ADG的狀態
SYS@boston > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;
Database altered.
SYS@boston > select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY
20. 檢視備庫複製後的檔案規劃
SYS@boston >
select name from v$datafile
union
select name from v$controlfile
union
select member from v$logfile
union
select name from v$tempfile
union
select value from v$parameter where name='spfile';
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/boston/control01.ctl
/home/oracle/dbfile/boston/control02.ctl
/home/oracle/dbfile/boston/redo01.log
/home/oracle/dbfile/boston/redo02.log
/home/oracle/dbfile/boston/redo03.log
/home/oracle/dbfile/boston/redo04.log
/home/oracle/dbfile/boston/redo05.log
/home/oracle/dbfile/boston/redo06.log
/home/oracle/dbfile/boston/redo07.log
/home/oracle/dbfile/boston/sysaux01.dbf
/home/oracle/dbfile/boston/system01.dbf
NAME
--------------------------------------------------------------------------------
/home/oracle/dbfile/boston/temp01.dbf
/home/oracle/dbfile/boston/undotbs01.dbf
/home/oracle/dbfile/boston/users01.dbf
/u01/app/oracle/product/12.1.0/db_1/dbs/spfileboston.ora
15 rows selected.
全文完
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31520497/viewspace-2156784/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 虛谷資料庫初體驗資料庫
- MySQL資料庫初體驗_gwMySql資料庫
- 【12C】Oracle 12c 可插拔資料庫之資料泵功能體驗Oracle資料庫
- 【12C】Oracle 12C 新特性“可插拔資料庫”功能體驗Oracle資料庫
- 分散式NoSQL資料庫MongoDB初體驗-v5.0.5分散式SQL資料庫MongoDB
- GBase 8s資料庫初體驗-01安裝資料庫
- 達夢資料庫初體驗-單機環境部署記錄資料庫
- Oracle 12C 跨網路傳輸資料庫Oracle資料庫
- 【Oracle 12c資料庫支援閃回庫功能】實驗Oracle資料庫
- 建立Oracle ADG standby資料庫若干方法Oracle資料庫
- 移植了ortp庫之後的初體驗
- 拓端：資料下的隱形眼鏡初體驗
- 資料倉儲和傳統資料庫的區別資料庫
- CRM系統資料庫是如何影響客戶體驗的？資料庫
- 初識Oracle資料庫體系結構Oracle資料庫
- oracle 12c 搭建adg注意地方Oracle
- 圖資料庫HugeGraph：HugeGraph-Hubble基於Web的視覺化圖管理初體驗資料庫Web視覺化
- Oracle 19c adg全庫遷移資料Oracle
- 資料庫介紹--初識資料庫資料庫
- 談談資料從sql server資料庫匯入mysql資料庫的體驗(轉)Server資料庫MySql
- oracle exadata資料庫一體機初識Oracle資料庫
- influxdb與傳統資料庫的比較UX資料庫
- 傳統OLTP資料庫存什麼？資料庫
- Python資料分析初體驗，需要解答的三大問題！Python
- 小程式的初體驗
- 翻譯的初體驗
- 12c 應答方式安裝 （一）資料庫軟體資料庫
- 區塊鏈代表的資料庫和傳統資料庫有何區別區塊鏈資料庫
- MySQL資料庫初識——初窺MySQLMySql資料庫
- Angular 初體驗Angular
- http初體驗HTTP
- AQS初體驗AQS
- golang 初體驗Golang
- OpenCV 初體驗OpenCV
- Nuxt 初體驗UX
- indexedDB 初體驗Index
- laravel初體驗Laravel
- Flutter初體驗Flutter