Oracle
RMAN 複製 - Oracle Data Guard - 語法錯誤 RMAN-01009 / RMAN-01008 / RMAN-01007
數據庫新手。對 Oracle Data Guard 感興趣。新使用 RMAN。
所有這些僅用於測試，不涉及任何類型的生產環境。
使用 ORACLE 文件：
- 使用 RMAN DUPLICATE…FROM ACTIVE DATABASE 創建物理備用的分步指南（文件 ID 1075908.1）
. .==========================================================================. .
主數據庫：
[oracle@ol12c pridb]$ env | grep ORA ORACLE_UNQNAME=cdb1 ORACLE_SID=pridb ORACLE_BASE=/u01/app/oracle ORACLE_HOSTNAME=ol12c.localdomain ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 SQL> startup pfile=/u01/app/oracle/product/12.1.0.2/db_1/dbs/initpridb.ora; SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area db_recovery_file_dest_size big integer 2G SQL> archive log list Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 12 Next log sequence to archive 13 Current log sequence 13. .==========================================================================. .
2. 待機：
[oracle@ol12c stbdb]$ env | grep ORA ORACLE_UNQNAME=cdb1 ORACLE_SID=stbdb ORACLE_BASE=/u01/app/oracle ORACLE_HOSTNAME=ol12c.localdomain ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 SQL> startup nomount pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initstbdb.ora' SQL> show parameter db_recovery_file_dest NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_recovery_file_dest string /u01/app/oracle/fast_recovery_ area/stbdb db_recovery_file_dest_size big integer 2G. .==========================================================================. .
3. RMAN（在主數據庫上）：
[oracle@ol12c pridb]$ rman RMAN> connect target sys/oracle@pridb connected to target database: PRIDB (DBID=1212094364) RMAN> connect auxiliary sys/oracle@stbdb connected to auxiliary database: PRIDB (not mounted). .==========================================================================. .
4.錯誤：
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "db_unique_name": expecting one of: "archivelog, autobackup, auxiliary, auxname, backup, catalog, command, compression, controlfile, database, dbid, decryption, default, echo, encryption, high, incarnation, incremental, long, maxcorrupt, maxseq, newname, nocfau, numwidth, restore, snapshot, to restore point, until restore point, until, " RMAN-01007: at line 1 column 1 file: standard input和
RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-00558: error encountered while parsing input commands RMAN-01009: syntax error: found "identifier": expecting one of: "archivelog, autobackup, auxiliary, auxname, backup, catalog, command, compression, controlfile, database, dbid, decryption, default, echo, encryption, high, incarnation, incremental, long, maxcorrupt, maxseq, newname, nocfau, numwidth, restore, snapshot, to restore point, until restore point, until, " RMAN-01008: the bad identifier was: log_archive_dest_2 RMAN-01007: at line 1 column 7 file: standard input對於從 log_file_name_convert 開始的命令中指定的所有參數，我收到RMAN-01008錯誤。
. .==========================================================================. .
5. 我的意見：
run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate auxiliary channel aux type disk; duplicate target database for standby from active database parameter_value_convert'pridb','stbdb' set db_unique_name='stbdb' set db_file_name_convert='pridb','stbdb' set log_file_name_convert='pridb','stbdb' set control_files='/u01/app/oracle/oradata/stbdb/stbdb_control1.ctl' set standby_file_management='AUTO' set log_archive_max_processes='10' set fal_client='stbdb' set fal_server='pridb' set standby_file_management='AUTO' set log_archive_config='dg_config=(pridb,stbdb)' set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stbdb' set log_archive_dest_2='service=testdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=stbdb' ; }首先我得到：
RMAN-05557：目標實例未使用伺服器參數文件啟動
因為我在嘗試：
duplicate target database for standby from active database spfile而且我不是從 spfile 開始，而是直接從 pfile 開始。根據我的研究，我應該能夠不指定 SPfile：
- https://www.oraexcel.com/oracle-12cR1-RMAN-05557
- http://oracledba-sharinganexperience.blogspot.com/2012/12/duplicate-database-fails-with-rman-05537.html
因此，我從我檢查的連結中
duplicate target database for standby from active database spfile提到duplicate target database for standby from active database的應該工作的地方改變了。–> 但是我現在要更新哪些參數？在我看來，這似乎不合邏輯。 .
上面的程式碼在分配通道時成功執行了一次，但隨後停止並開始拋出錯誤。
目前我正在執行腳本
rman checksyntax只是為了確保我不會破壞任何東西.. :). .==========================================================================. .
你能幫我嗎：
- 我試圖更好地理解 RMAN。
- 找出我在上述情況下做錯了什麼。（+ 為什麼！）
我對了解正在發生的事情等比快速完成這項工作更感興趣。
（即使它工作起來會很棒！:)此時的目的是學習）
預先感謝您的幫助！
. .==========================================================================. .
解決方案
需要一個 SPFILE
在主數據庫上：
SQL> create spfile from pfile='/u01/app/oracle/product/12.1.0.2/db_1/dbs/initpridb.ora'; File created. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 671088744 bytes Database Buffers 394264576 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.然後啟動 RMAN 並執行腳本，正如我們所說的那樣執行！:) -耶！
以下錯誤：
RMAN-05537：當使用 spfile 啟動輔助實例時，沒有 TARGET 連接的 DUPLICATE 不能使用 SPFILE 子句
通過更新腳本值和重新連接數據庫來解決。
使用的最終腳本：
RMAN> run{ allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate auxiliary channel aux type disk; duplicate target database for standby from active database spfile parameter_value_convert'pridb','stbdb' set db_unique_name='stbdb' set db_file_name_convert='/u01/app/oracle/oradata/pridb/PRIDB/datafile/','/u01/app/oracle/oradata/stbdb/STBDB/datafile/' set log_file_name_convert='/u01/app/oracle/oradata/pridb/PRIDB/onlinelog/','/u01/app/oracle/oradata/stbdb/STBDB/onlinelog/' set control_files='/u01/app/oracle/oradata/stbdb/stbdb_control1.ctl' set standby_file_management='AUTO' set log_archive_max_processes='10' set fal_client='stbdb' set fal_server='pridb' set standby_file_management='AUTO' set log_archive_config='dg_config=(pridb,stbdb)' set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stbdb' set log_archive_dest_2='service=testdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=stbdb' ; }
parameter_value_convert沒有就不能使用spfile。重複：
dupOptionList::=
主數據庫使用 spfile 啟動：
[oracle@o71 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 7 11:41:46 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show parameter spfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/12.1.0 /dbhome_1/dbs/spfilepridb.ora SQL>待機是用 pfile 啟動的：
[oracle@o72 ~]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 7 11:39:23 2019 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/tmp/pf.ora'; ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 402653288 bytes Database Buffers 662700032 bytes Redo Buffers 5455872 bytes SQL> Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options [oracle@o72 ~]$複製：
[oracle@o71 ~]$ rman target sys/Oracle123@\'o71:1521/pridb\' auxiliary sys/Oracle123@\'o72:1521/stbdb\' Recovery Manager: Release 12.1.0.2.0 - Production on Thu Mar 7 11:39:45 2019 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: PRIDB (DBID=1212244976) connected to auxiliary database: PRIDB (not mounted) RMAN> run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate auxiliary channel aux type disk; duplicate target database for standby from active database spfile parameter_value_convert'pridb','stbdb' set db_unique_name='ic_stb' set db_file_name_convert='pridb','stbdb' set log_file_name_convert='pridb','stbdb' set control_files='/oradata/stbdb/control01.ctl', '/oradata/stbdb/control02.ctl' set standby_file_management='AUTO' set log_archive_max_processes='10' set fal_client='stbdb' set fal_server='pridb' set standby_file_management='AUTO' set log_archive_config='dg_config=(pridb,stbdb)' set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stbdb' set log_archive_dest_2='service=testdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=stbdb' ; 23> } using target database control file instead of recovery catalog allocated channel: c1 channel c1: SID=247 device type=DISK allocated channel: c2 channel c2: SID=401 device type=DISK allocated channel: c3 channel c3: SID=5 device type=DISK allocated channel: c4 channel c4: SID=88 device type=DISK allocated channel: aux channel aux: SID=83 device type=DISK Starting Duplicate Db at 07-MAR-19 contents of Memory Script: { backup as copy reuse targetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwpridb' auxiliary format '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/orapwstbdb' targetfile '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilepridb.ora' auxiliary format '/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilestbdb.ora' ; sql clone "alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilestbdb.ora''"; } executing Memory Script Starting backup at 07-MAR-19 Finished backup at 07-MAR-19 sql statement: alter system set spfile= ''/u01/app/oracle/product/12.1.0/dbhome_1/dbs/spfilestbdb.ora'' contents of Memory Script: { sql clone "alter system set audit_file_dest = ''/u01/app/oracle/admin/stbdb/adump'' comment= '''' scope=spfile"; sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stbdbXDB)'' comment= '''' scope=spfile"; sql clone "alter system set db_unique_name = ''ic_stb'' comment= '''' scope=spfile"; sql clone "alter system set db_file_name_convert = ''pridb'', ''stbdb'' comment= '''' scope=spfile"; sql clone "alter system set log_file_name_convert = ''pridb'', ''stbdb'' comment= '''' scope=spfile"; sql clone "alter system set control_files = ''/oradata/stbdb/control01.ctl'', ''/oradata/stbdb/control02.ctl'' comment= '''' scope=spfile"; sql clone "alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_max_processes = 10 comment= '''' scope=spfile"; sql clone "alter system set fal_client = ''stbdb'' comment= '''' scope=spfile"; sql clone "alter system set fal_server = ''pridb'' comment= '''' scope=spfile"; sql clone "alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_config = ''dg_config=(pridb,stbdb)'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_1 = ''location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stbdb'' comment= '''' scope=spfile"; sql clone "alter system set log_archive_dest_2 = ''service=testdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=stbdb'' comment= '''' scope=spfile"; shutdown clone immediate; startup clone nomount; } executing Memory Script sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/stbdb/adump'' comment= '''' scope=spfile sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=stbdbXDB)'' comment= '''' scope=spfile sql statement: alter system set db_unique_name = ''ic_stb'' comment= '''' scope=spfile sql statement: alter system set db_file_name_convert = ''pridb'', ''stbdb'' comment= '''' scope=spfile sql statement: alter system set log_file_name_convert = ''pridb'', ''stbdb'' comment= '''' scope=spfile sql statement: alter system set control_files = ''/oradata/stbdb/control01.ctl'', ''/oradata/stbdb/control02.ctl'' comment= '''' scope=spfile sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile sql statement: alter system set log_archive_max_processes = 10 comment= '''' scope=spfile sql statement: alter system set fal_client = ''stbdb'' comment= '''' scope=spfile sql statement: alter system set fal_server = ''pridb'' comment= '''' scope=spfile sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile sql statement: alter system set log_archive_config = ''dg_config=(pridb,stbdb)'' comment= '''' scope=spfile sql statement: alter system set log_archive_dest_1 = ''location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=stbdb'' comment= '''' scope=spfile sql statement: alter system set log_archive_dest_2 = ''service=testdb ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=stbdb'' comment= '''' scope=spfile Oracle instance shut down connected to auxiliary database (not started) Oracle instance started Total System Global Area 1073741824 bytes Fixed Size 2932632 bytes Variable Size 402653288 bytes Database Buffers 662700032 bytes Redo Buffers 5455872 bytes allocated channel: aux channel aux: SID=163 device type=DISK contents of Memory Script: { backup as copy current controlfile for standby auxiliary format '/oradata/stbdb/control01.ctl'; restore clone primary controlfile to '/oradata/stbdb/control02.ctl' from '/oradata/stbdb/control01.ctl'; } executing Memory Script Starting backup at 07-MAR-19 channel c1: starting datafile copy copying standby control file output file name=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/snapcf_pridb.f tag=TAG20190307T114117 channel c1: datafile copy complete, elapsed time: 00:00:01 Finished backup at 07-MAR-19 Starting restore at 07-MAR-19 channel aux: copied control file copy Finished restore at 07-MAR-19 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 "/oradata/stbdb/temp01.dbf"; switch clone tempfile all; set newname for datafile 1 to "/oradata/stbdb/system01.dbf"; set newname for datafile 3 to "/oradata/stbdb/sysaux01.dbf"; set newname for datafile 4 to "/oradata/stbdb/undotbs01.dbf"; set newname for datafile 6 to "/oradata/stbdb/users01.dbf"; backup as copy reuse datafile 1 auxiliary format "/oradata/stbdb/system01.dbf" datafile 3 auxiliary format "/oradata/stbdb/sysaux01.dbf" datafile 4 auxiliary format "/oradata/stbdb/undotbs01.dbf" datafile 6 auxiliary format "/oradata/stbdb/users01.dbf" ; sql 'alter system archive log current'; } executing Memory Script executing command: SET NEWNAME renamed tempfile 1 to /oradata/stbdb/temp01.dbf in control file executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME executing command: SET NEWNAME Starting backup at 07-MAR-19 channel c1: starting datafile copy input datafile file number=00001 name=/oradata/pridb/system01.dbf channel c2: starting datafile copy input datafile file number=00003 name=/oradata/pridb/sysaux01.dbf channel c3: starting datafile copy input datafile file number=00004 name=/oradata/pridb/undotbs01.dbf channel c4: starting datafile copy input datafile file number=00006 name=/oradata/pridb/users01.dbf output file name=/oradata/stbdb/system01.dbf tag=TAG20190307T114124 channel c1: datafile copy complete, elapsed time: 00:00:01 output file name=/oradata/stbdb/sysaux01.dbf tag=TAG20190307T114124 channel c2: datafile copy complete, elapsed time: 00:00:01 output file name=/oradata/stbdb/undotbs01.dbf tag=TAG20190307T114124 channel c3: datafile copy complete, elapsed time: 00:00:01 output file name=/oradata/stbdb/users01.dbf tag=TAG20190307T114124 channel c4: datafile copy complete, elapsed time: 00:00:01 Finished backup at 07-MAR-19 sql statement: alter system archive log current contents of Memory Script: { switch clone datafile all; } executing Memory Script datafile 1 switched to datafile copy input datafile copy RECID=1 STAMP=1002282086 file name=/oradata/stbdb/system01.dbf datafile 3 switched to datafile copy input datafile copy RECID=2 STAMP=1002282086 file name=/oradata/stbdb/sysaux01.dbf datafile 4 switched to datafile copy input datafile copy RECID=3 STAMP=1002282086 file name=/oradata/stbdb/undotbs01.dbf datafile 6 switched to datafile copy input datafile copy RECID=4 STAMP=1002282086 file name=/oradata/stbdb/users01.dbf Finished Duplicate Db at 07-MAR-19 released channel: c1 released channel: c2 released channel: c3 released channel: c4 released channel: aux RMAN>您的主要應使用 spfile。
