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;
|