f RMAN Duplicate from active database 建置 RAC 12.2 Active DataGuard ~ 迪貝之家

Pages

RMAN Duplicate from active database 建置 RAC 12.2 Active DataGuard

參考資料來自於Oracle的技術文件 1617946.1
Creating a Physical Standby using RMAN Duplicate (RAC or Non-RAC)
不過你要依照文件,沒有進行任何修改
能把ADG建置出來的話~~
哪你就真是神人了
照著文件做..我是做不出來啦!!
依照我之前建置single instance DR 的經驗
再加上這次的測試
反正ADG的建置就是分為兩個部分
1. Clone 資料庫
2. DG同步的設定
對...就是這麼簡單
建議還是對照原文件 1617946.1
1) Create a service to run RMAN on allocated instances in the cluster:
su - oracle
srvctl add service -db chicago -service srv_rman -preferred chicago1,chicago2
srvctl start service -db chicago -service srv_rman
[oracle@exa503 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.2.0.1.0 - Production on 18-FEB-2020 13:20:27
Copyright (c) 1991, 2016, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.2.0.1.0 - Production
Start Date 15-FEB-2020 17:47:49
Uptime 2 days 19 hr. 32 min. 37 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/12.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/exa503/listener/alert/log.x ml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.6.63)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=172.22.6.67)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "+ASM_DATA" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "9e97918792b863ace053400616acd3bf" has 1 instance(s).
Instance "chicago1", status READY, has 1 handler(s) for this service...
Service "chicago" has 1 instance(s).
Instance "chicago1", status READY, has 1 handler(s) for this service...
Service "chicagoXDB" has 1 instance(s).
Instance "chicago1", status READY, has 1 handler(s) for this service...
Service "pdb1" has 1 instance(s).
Instance "chicago1", status READY, has 1 handler(s) for this service...
Service "srv_rman" has 1 instance(s).
Instance "chicago1", status READY, has 1 handler(s) for this service...
2) When running RMAN, use the service name in the connect string for the “target” parameter.
rman target sys/xxxx@prim-scan.dbaid.tw/srv_rman
上述步驟建出服務,是為了能讓你用下列的步驟來進行rman clone source 資料庫
1. Create standby redo logs on the primary database that are the same size of the online redo logs.
Steps to Duplicate the Primary Database
SQL> alter database add standby logfile thread 1 group 7 size 200M, group 8 size 200M, group 9 size 200M;
Database altered.
SQL> alter database add standby logfile thread 2 group 10 size 200M, group 11 size 200M, group 12 size 200M;
Database altered.
SQL> alter system set log_archive_dest_1='location=+DATA' scope=spfile;
System altered.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
懂Oracle的應該不用我說了吧~~
這是要啟動archive log mode
[oracle@exa503 ~]$ srvctl stop database -d chicago
[oracle@exa503 ~]$ srvctl start instance -d chicago -i chicago1 -o mount
[oracle@exa503 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 19 16:21:48 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter database archivelog ;
Database altered.
[oracle@exa503 ~]$ srvctl stop instance -d chicago -i chicago1
[oracle@exa503 ~]$ srvctl start database -d chicago
[oracle@exa503 ~]$ srvctl status database -d chicago
Instance chicago1 is running on node exa503
Instance chicago2 is running on node exa504
[oracle@exa503 ~]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.2.0.1.0 Production on Wed Feb 19 16:24:47 2020
Copyright (c) 1982, 2016, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3
2.接下來是要把source 資料庫password檔從asm拷貝出來
有metalink文件說在rman duplicate的過程中
就會主動幫你把它一併clone到target資料庫的asm
我測試的時候,是看不到這個動作
所以我先手動處理掉
[oracle@exa503 ~]$ asmcmd -p
ASMCMD [+] > cd data
ASMCMD [+data] > ls
ASM/
_mgmtdb/
chicago/
orapwasm
orapwasm_backup
prim/
ASMCMD [+data] > cd chicago
ASMCMD [+data/chicago] > ls
4700A987085B3DFAE05387E5E50A8C7B/
9EEA10F359D16AFAE0533F0616AC5334/
9EEA34AF67A97F5FE0533F0616ACB77C/
ARCHIVELOG/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
PASSWORD/
TEMPFILE/
ASMCMD [+data/chicago] > cd PASSWORD
ASMCMD [+data/chicago/PASSWORD] > LS
ASMCMD [+data/chicago/PASSWORD] > ls
pwdchicago.378.1032796145
ASMCMD [+data/chicago/PASSWORD] > pwcopy '+data/chicago/PASSWORD/pwdchicago.378.1032796145' '/tmp/pwdboston'
copying +data/chicago/PASSWORD/pwdchicago.378.1032796145 -> /tmp/pwdboston
把pwdboston傳檔到你的target 主機
3.For the RMAN duplication process it is necessary to setup a temporary listener which will later be removed.
LISTENER_duplicate = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = exa505) (PORT = 1525)(IP = FIRST))))
SID_LIST_LISTENER_duplicate = (SID_LIST = (SID_DESC = (SID_NAME = boston1) (ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)))
4. In the database home on the primary node, create an Oracle Net alias to connect to the listener created in the above step. 

dup = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP) (HOST = exa505) (PORT = 1525)) (CONNECT_DATA = (SERVER = DEDICATED) (SID = boston1)))
5.建出這個密碼檔案是為了rman duplicate時,能夠連進target 主機上的nomount db instance
orapwd file=orapwboston1 password=test
6. target 主機dbinstance 的parameter file
我會把archive log destination參數先加進去
因為duplicate進行完後,要open時,會發生system tablespace修復的不夠
文件只有前3行
db_name=chicago
db_unique_name=boston
sga_target=5g
LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'
LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
LOG_ARCHIVE_DEST_STATE_1=ENABLE'
FAL_SERVER=chicago
FAL_CLIENT=boston
7. On all standby hosts create the audit directory for the boston database:
mkdir -p /u01/app/oracle/admin/boston/adump
9. On the standby host set the SID to the standby SID (boston1) and startup nomount the standby/auxiliary instance.
$ export ORACLE_SID=boston1
SQL> startup nomount pfile='initboston1.ora'

11. On the primary host run an RMAN script that duplicates the primary database using the “duplicate target database for
standby from active database” command
文件的rman script 有底下這一行
parameter_value_convert 'chicago','boston'
拿掉,不然會出現0113的錯誤
這是我改到最後,我可以使用的RMAN DUP SCRIPT
rman <<EOF
connect target sys/xxxx@prim-scan.dbaid.tw/srv_rman;
connect auxiliary sys/xxxx@dup;
run {
duplicate target database for standby from active database
spfile
set db_unique_name='boston'
SET CLUSTER_DATABASE='FALSE'
set control_files='+DATA/boston/standby.ctl'
set local_listener='boston_local_listener'
set remote_listener='stby-scan:1521'
set audit_file_dest='/u01/app/oracle/admin/boston/adump'
set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston'
set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago'
set LOG_ARCHIVE_DEST_STATE_1='ENABLE'
set FAL_SERVER='chicago'
set FAL_CLIENT='boston'
section size=200M; }
EOF
man <<EOF
connect target sys/xxxx@prim-scan.dbaid.tw/srv_rman;
connect auxiliary sys/xxxx@dup;
run {
duplicate target database for standby from active database
spfile
set db_unique_name='boston'
SET CLUSTER_DATABASE='FALSE'
set control_files='+DATA/boston/standby.ctl'
set local_listener='boston_local_listener'
set remote_listener='stby-scan:1521'
set audit_file_dest='/u01/app/oracle/admin/boston/adump'
set LOG_ARCHIVE_DEST_1='LOCATION=+DATA'
section size=200M; }
EOF
12.再來就是要把已經clone好的資料庫的參數檔轉出文字檔進行修改
因為原本的參數是從source拷貝過來的
要把source instance name chicago改成boston
這也許就是為什麼之前的rman script設了相應的置換
boston1.__data_transfer_cache_size=0
boston1.__db_cache_size=4060086272
boston1.__inmemory_ext_roarea=0
boston1.__inmemory_ext_rwarea=0
boston1.__java_pool_size=16777216
boston1.__large_pool_size=33554432
boston1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
boston1.__pga_aggregate_target=1677721600
boston1.__sga_target=4999610368
boston1.__shared_io_pool_size=0
boston1.__shared_pool_size=872415232
boston1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/boston/adump'
*.audit_trail='db'
*.cluster_database=FALSE
*.compatible='12.2.0'
*.control_files='+DATA/boston/standby.ctl'#Restore Controlfile
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_name='chicago'
*.db_unique_name='boston'
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=chicagoXDB)'
*.enable_pluggable_database=true
family:dw_helper.instance_mode='read-only'
chicago1.instance_number=1
chicago2.instance_number=2
*.local_listener='boston_local_listener'
*.log_archive_dest_1='LOCATION=+DATA'
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.open_cursors=300
*.pga_aggregate_target=1589m
*.processes=300
*.remote_listener='stby-scan:1521'
*.remote_login_passwordfile='exclusive'
*.sga_target=4764m
chicago2.thread=2
chicago1.thread=1
chicago2.undo_tablespace='UNDOTBS2'
chicago1.undo_tablespace='UNDOTBS1'
+data/boston/parameterfile/spfile.329.1032799981
13.再來就是一樣用asm的指令pwcopy,把password file拷貝進ASM
+DATA/BOSTON/PASSWORD/pwboston
14.把ORACLE_HOME及password以及spfile的位置寫進OCR裡頭
srvctl add database -db boston -oraclehome /u01/app/oracle/product/12.2.0/dbhome_1
srvctl add instance -db boston -instance boston1 -node exa505
srvctl add instance -db boston -instance boston2 -node exa506
srvctl modify database -db boston -role physical_standby -spfile '+DATA/boston/parameterfile/spfile.329.1032799981' -pwfile '+DATA/BOSTON/PASSWORD/pwdboston'
15.啟動TARGET RAC 為mount
srvctl start database -d boston -o mount
16.在SOURCE RAC 端設上這些參數
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' scope=both;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=chicago' scope=both;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=boston' scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=both;
alter system set FAL_SERVER=boston scope=both;
alter system set FAL_CLIENT=chicago scope=both;
17.在TARGET RAC 補上參數
alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)' scope=spfile;
alter system set LOG_ARCHIVE_DEST_1='LOCATION=+DATA VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=boston' scope=spfile;
alter system set LOG_ARCHIVE_DEST_2='SERVICE=boston LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=chicago' scope=spfile;
alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE scope=spfile;
alter system set FAL_SERVER='chicago' scope=spfile;
alter system set FAL_CLIENT='boston' scope=spfile;
18. 啟動同步
alter database recover managed standby database disconnect from session;

設置broker的步驟
完全照著文件1617946.1做就可以了
我想做的是是透過broker的命令傳達一個非常重要的概念
dg broker
不管你的DR RAC有幾個NODE
APPY DATA永遠只有一個