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