0

New to DB. Interested in Oracle Data Guard. New working with RMAN.

All of this is for testing only and does not involve any kind of production env.

Making use of ORACLE documentation:

  • Step by Step Guide on Creating Physical Standby Using RMAN DUPLICATE...FROM ACTIVE DATABASE (Doc ID 1075908.1)

. .==========================================================================. .

The Primary Database:

[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. The Standby:

[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 (On the primary db):

[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. Error:

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

AND

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

I receive the RMAN-01008 error for all the parameters specified in the command starting from log_file_name_convert.

. .==========================================================================. .

5. My input:

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

First I got:

RMAN-05557: Target instance not started with server parameter file

Because I was trying: duplicate target database for standby from active database spfile and I am not starting from an spfile but directly from the pfile.

As per my research, I should be able to not specify the SPfile:

Thus I changed from duplicate target database for standby from active database spfile to duplicate target database for standby from active database which should work as mentioned in the links I checked.

--> But which parameters am I updating now? To me, this doesn't seem logical. .

The above code ran once with success on allocating the channels but then stopped and started throwing errors.

Currently I am running the script in rman checksyntax just to be sure I don't break anything.. :)

. .==========================================================================. .

Can you please help me with:

  • Me trying to understand RMAN better.

  • Figuring out what I am doing wrong in the above situation. (+ Why!)

I have more interest in understanding what is going on etc than making this work quickly.

(Even though it working would be great ofcouse! :) The purpose at this point is learning)

Thank you in advance for your help!

. .==========================================================================. .

SOLUTION

A SPFILE IS REQUIRED

On the primary database:

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.

Then started RMAN and ran the script is running as we speak! :) - YEY!

Following error:

RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause

Solved by updating the script values & re-connecting the dbs.

Final script used:

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'
;
}
0

parameter_value_convert can not be used without spfile.

DUPLICATE:

dupOptionList::=

enter image description here

Primary database was started with 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>

Standby is started with 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 ~]$

Duplicate:

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

Your primary should use an spfile.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.