Rman duplicating an oracle 11g database in active state on the same host


I already published a post about rman> duplicate... a couple of years ago (Rman duplicating an oracle database in a new sid two host scenario), still on 10g at that time and using a backup set being transfered to a new host. With 11g, however, rman> duplicate... offers another option to not only restore from a source backup, leaving source online, but from an up and running database (with takes archivelog mode and some rman catalogued entries in the control file or catalogue nevetheless). Following below therefore, I’m going to show the do’s for an rman> duplicate ... from active database... in a same host scenario, on windows again, using orapwd and oradim as well as lsnrctl this time. The main difference, however, is employing the spfile clause of the duplicate command, such that rman will set up the destination spfile on its own. Only some file name mappings, actually like before, need to be specified. My main reference to review any new features was Duplicating a Database from the oracle 11g1 docs, other references, concerning errors that showed up underway, will be given below.

Ok, working on the same host, nothing is due to be done for software installation and stuff and we can immediately set up the new instance (note that source will be denoted tgt, for target and the destination aux for auxiliary, respectively). Firstly, we create a new password file for destination, with the same sysdba password as on source.

cd /d e:\oracle\product\11.2.0\dbhome_1\database
orapwd file=PWDAUX.ora ignorecase=y force=y

Next, we create a new pfile with only the new dbname (which may have an arbitrary value according to the docs). This was a first trap for me, since the docs are not exact on whether to create an initial spfile or pfile, the used term is just “initialization parameter file”. Having not used pfile’s for actually decades anymore, I started off with an spfile and eventually hit RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause underway the duplicate run. Creating standby database inc dg broker and 12c changes helped me out here in locating the cause of the problem. Thinking the problem through, yet another time, made clear that a spfile cannot work ever because when destination has been started up with an spfile and rman> duplicate ... spfile... tries to overwrite that spfile, it must error out by definition.

cd /d e:\oracle\product\11.2.0\dbhome_1\database
echo *.db_name='AUX' > PFILEAUX.ORA

On windows, we need a new startup/shutdown service for destination. Sometimes, the startup options of oradim may not work as expected, so you better double check the generated service and the registry entries.

cd /d e:\oracle\product\11.2.0\dbhome_1\database
oradim -new -sid aux -srvc OracleServiceAUX -srvcstart system -startmode auto

Extending the network configuration takes listener.ora and tnsnames.ora and lsnrctl as well as tnsping and sqlplus for testing. See below, highlightened lines already existed before.

# extend existing listener.ora
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME = E:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = TGT)
    )
    (SID_DESC =
      (ORACLE_HOME = E:\oracle\product\11.2.0\dbhome_1)
      (SID_NAME = AUX)
    )
  )

# reload and test status the listener, a new service for aux must be available
lsnrctl reload
lsnrctl status

# extend existing tnsnames.ora with a new name for aux
aux =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.39.82)(PORT = 1526))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = AUX)
    )
  )

# test ping the listener for this new tns name on aux successfully
tnsping aux

# test connect as sysdba to aux successfully
sqlplus "/@aux as sysdba"

# startup nomount the aux instance to be ready for rman below
startup nomount pfile = 'e:\oracle\product\11.2.0\dbhome_1\database\PFILEAUX.ORA';

Now, do execute the following select to create any paths that result from the necessary filename convert patterns, which are case-sensitive (!!) for the parameters db_file_name_convert, parameter_value_convert and log_file_name_convert used with the duplicate ... from active database command, see below. You may also do a rman> report schema; but this output will not comprise any paths needed, control files for example. However besides that, I think it is good practice to again only rely on a double check and do a create pfile = 'e:\oracle\product\11.2.0\dbhome_1\database\PFILETGT.ORA' from spfile; for source and review if there still is some path being left. Finally, and this was my second trap underway, if you hit ORA-17628: Oracle error 19505 returned by remote Oracle server, some path is still missing on destination, thank’s to Duplicate from the standby instead from the primary in 12c.

with tmp as (
  SELECT (case when (instr(dbms_utility.port_string, 'WIN_NT') > 0)
    then '\' else '/' end) as dl FROM dual )
select 'mkdir ' || decode(tmp.dl, '/', '-p ', '') || pt from tmp, (
  select pt from (
    select distinct lower(substr(fl, 1, instr(fl, tmp.dl, -1) - 1)) as pt from tmp, (
      select name as fl from v$datafile union all
      select name from v$tempfile union all
      select name from v$controlfile union all
      select member from v$logfile ))
  union all
  select lower(destination) as pt from V$ARCHIVE_DEST
    where target = 'PRIMARY' and destination is not null
  union all
  select lower(name) as pt from v$recovery_file_dest
  union all
  select lower(value) as pt from v$parameter
    where name = 'audit_file_dest'
) order by 1;

Jep, time to launch. Connect to source and destination, target and auxiliary here, reset the rman> configure... parallelism... parameter at source to gain a better throughput and run the duplicate. The several parameters to the duplicate comprise the convert patterns introduced above, a job queue switch off initially, always a good idea, and a general hint to the maximum memory available in the spfileclause. That’s it. Any other initialization parameters will be transfered from source by rman.

RMAN> connect target sys@tgt
Mit Ziel-Datenbank verbunden: TGT (DBID=126257936)

RMAN> configure device type disk parallelism 2;

RMAN> connect auxiliary sys@aux
Bei Hilfsdatenbank angemeldet: AUX (nicht mit MOUNT angeschlossen)

RMAN> duplicate target database to aux from active database
  db_file_name_convert '\tgt','\aux' , '\TGT','\AUX'
  spfile
    parameter_value_convert '\tgt','\aux' , '\TGT','\AUX' , '=TGTXDB','=AUXXDB'
    set log_file_name_convert '\tgt','\aux' , '\TGT','\AUX'
    set job_queue_processes '0'
    set memory_target '3221225472';

Now, just wait for the rman sessionn sooner or later emitting “Beendet Duplicate Db um 19.07.16“, which is finished duplicate in english. Note also, that when hitting errors underway, after fixing the problem, just shutdown, startup nomount destination and re-connect rman to destination and start over. I never had any more to do to start over, that is, no locked files and things.

What followes below is the complete but non-redundant log of the rman session, in case you might need it. Just click open the code box, it will be closed on poage load.

Have fun, Peter

RMAN> duplicate target database to aux from active database db_file_name_convert '\tgt','\aux' , '\TGT', '\AUX' spfile parameter_value_convert '\tgt','\aux' , '\TGT','\AUX' , '=TGTXDB','=AUXXDB' set log_file_name_convert '\tgt','\aux' , '\TGT','\AUX' set job_queue_processes '0' set memory_target '3221225472';

Starten Duplicate Db um 19.07.16
Zugewiesener Kanal: ORA_AUX_DISK_1
Kanal ORA_AUX_DISK_1: SID=303 Device-Typ=DISK
Zugewiesener Kanal: ORA_AUX_DISK_2
Kanal ORA_AUX_DISK_2: SID=8 Device-Typ=DISK

Inhalt von Speicher-Skript:
{ backup as copy reuse
  targetfile 'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILETGT.ORA' auxiliary format
  'E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEAUX.ORA'  ;
  sql clone "alter system set spfile= ''E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEAUX.ORA''";
}
Speicher-Skript wird ausgeführt

Starten backup um 19.07.16
Zugewiesener Kanal: ORA_DISK_1
Kanal ORA_DISK_1: SID=26 Device-Typ=DISK
Zugewiesener Kanal: ORA_DISK_2
Kanal ORA_DISK_2: SID=116 Device-Typ=DISK
Beendet backup um 19.07.16

SQL-Anweisung: alter system set spfile= ''E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEAUX.ORA''

Inhalt von Speicher-Skript:
{ sql clone "alter system set db_name = ''AUX'' comment = ''duplicate'' scope=spfile";
  sql clone "alter system set audit_file_dest = ''E:\ORACLE\ADMIN\AUX\ADUMP'' comment = '''' scope=spfile";
  sql clone "alter system set control_files = ''G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\CONTROL01.CTL'', ''H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\CONTROL02.CTL'', ''H:\HUGO_ORACLE\SAN_4\ORADATA\AUX\CONTROL03.CTL'' comment = '''' scope=spfile";
  sql clone "alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=AUXXDB)'' comment = '''' scope=spfile";
  sql clone "alter system set log_archive_dest_1 = ''LOCATION=g:\ds_oracle\san_2\oradata\aux\archive'' comment = '''' scope=spfile";
  sql clone "alter system set log_file_name_convert = ''\tgt'', ''\aux'', ''\TGT'', ''\AUX'' comment = '''' scope=spfile";
  sql clone "alter system set job_queue_processes = 0 comment = '''' scope=spfile";
  sql clone "alter system set memory_target = 3221225472 comment = '''' scope=spfile";
  shutdown clone immediate;
  startup clone nomount;
}
Speicher-Skript wird ausgeführt

SQL-Anweisung: alter system set db_name = ''AUX'' comment= ''duplicate'' scope=spfile
SQL-Anweisung: alter system set audit_file_dest = ''E:\ORACLE\ADMIN\AUX\ADUMP'' comment= '''' scope=spfile
SQL-Anweisung: alter system set control_files = ''G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\CONTROL01.CTL'',  ''H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\CONTROL02.CTL'', ''H:\HUGO_ORACLE\SAN_4\ORADATA\AUX\CONTROL03.CTL'' comment = '''' scope=spfile
SQL-Anweisung: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=AUXXDB)'' comment= '''' scope=spfile
SQL-Anweisung: alter system set log_archive_dest_1 = ''LOCATION=g:\ds_oracle\san_2\oradata\aux\archive'' comment = '''' scope=spfile
SQL-Anweisung: alter system set log_file_name_convert = ''\tgt'', ''\aux'', ''\TGT'', ''\AUX'' comment = '''' scope=spfile
SQL-Anweisung: alter system set job_queue_processes = 0 comment= '''' scope=spfile
SQL-Anweisung: alter system set memory_target = 3221225472 comment= '''' scope=spfile

Oracle-Instanz heruntergefahren
Mit Hilfsdatenbank verbunden (nicht gestartet)
Oracle-Instanz gestartet
Gesamte System Global Area  3206836224 Byte
Fixed Size           2259480 Byte
Variable Size        1744831976 Byte
Database Buffers      1442840576 Byte
Redo Buffers         16904192 Byte

Inhalt von Speicher-Skript:
{ sql clone "alter system set db_name = ''TGT'' comment = ''Modified by RMAN duplicate'' scope=spfile";
  sql clone "alter system set db_unique_name = ''AUX'' comment = ''Modified by RMAN duplicate'' scope=spfile";
  shutdown clone immediate;
  startup clone force nomount
  backup as copy current controlfile auxiliary format 'G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\CONTROL01.CTL';
  restore clone controlfile to 'H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\CONTROL02.CTL' from 'G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\CONTROL01.CTL';
  restore clone controlfile to 'H:\HUGO_ORACLE\SAN_4\ORADATA\AUX\CONTROL03.CTL' from 'G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\CONTROL01.CTL';
  alter clone database mount;
}
Speicher-Skript wird ausgeführt

SQL-Anweisung: alter system set db_name = ''TGT'' comment= ''Modified by RMAN duplicate'' scope=spfile
SQL-Anweisung: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle-Instanz heruntergefahren
Oracle-Instanz gestartet
Gesamte System Global Area  3206836224 Byte
Fixed Size           2259480 Byte
Variable Size        1744831976 Byte
Database Buffers      1442840576 Byte
Redo Buffers         16904192 Byte

Starten backup um 19.07.16
Kanal ORA_DISK_1 wird benutzt
Kanal ORA_DISK_2 wird benutzt
Kanal ORA_DISK_1: Datendatei-Kopie wird gestartet
Aktuelle Kontrolldatei wird kopiert
Ausgabedateiname=E:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SNCFTGT.ORA Tag=TAG20160719T170247 RECID=4 STAMP=917629367
Kanal ORA_DISK_1: Datendatei-Kopie abgeschlossen, abgelaufene Zeit: 00:00:01
Beendet backup um 19.07.16

Starten restore um 19.07.16
Zugewiesener Kanal: ORA_AUX_DISK_1
Kanal ORA_AUX_DISK_1: SID=6 Device-Typ=DISK
Zugewiesener Kanal: ORA_AUX_DISK_2
Kanal ORA_AUX_DISK_2: SID=173 Device-Typ=DISK

Kanal ORA_AUX_DISK_2: übersprungen, AUTOBACKUP schon gefunden
Kanal ORA_AUX_DISK_1: Kontrolldateikopie wurde kopiert
Beendet restore um 19.07.16

Starten restore um 19.07.16
Kanal ORA_AUX_DISK_1 wird benutzt
Kanal ORA_AUX_DISK_2 wird benutzt

Kanal ORA_AUX_DISK_2: übersprungen, AUTOBACKUP schon gefunden
Kanal ORA_AUX_DISK_1: Kontrolldateikopie wurde kopiert
Beendet restore um 19.07.16

Datenbank angeschlossen

Inhalt von Speicher-Skript:
{ set newname for datafile 1 to "H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF";
  ...
  backup as copy reuse
  datafile 1 auxiliary format "H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF"
  ...
  sql 'alter system archive log current';
}
Speicher-Skript wird ausgeführt

Befehl wird ausgeführt: SET NEWNAME
...

Starten backup um 19.07.16
Kanal ORA_DISK_1 wird benutzt
Kanal ORA_DISK_2 wird benutzt
...
Dateinummer der Eingabedatendatei=00001 Name=H:\HUGO_ORACLE\SAN_3\ORADATA\TGT\SYSTEM01.DBF
Ausgabedateiname=H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSAUX01.DBF Tag=TAG20160719T170317
Kanal ORA_DISK_1: Datendatei-Kopie abgeschlossen, abgelaufene Zeit: 00:00:37
...
Beendet backup um 19.07.16

SQL-Anweisung: alter system archive log current

Inhalt von Speicher-Skript:
{ backup as copy reuse
  archivelog like "G:\HUGO_ORACLE\SAN_2\ORADATA\TGT\ARCHIVE\ARC0000027920_0731252880.0001" auxiliary format "G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\ARCHIVE\ARC0000027920_0731252880.0001"  ;
  catalog clone archivelog "G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\ARCHIVE\ARC0000027920_0731252880.0001";
  switch clone datafile all;
}
Speicher-Skript wird ausgeführt

Starten backup um 19.07.16
Kanal ORA_DISK_1 wird benutzt
Kanal ORA_DISK_2 wird benutzt
Kanal ORA_DISK_1: Kopie für Archive Log wird begonnen
Eingabe-Archive-Log-Thread=1 Sequence=27920 RECID=27891 STAMP=917630432
Ausgabedateiname=G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\ARCHIVE\ARC0000027920_0731252880.0001 RECID=0 STAMP=0
Kanal ORA_DISK_1: Archive Log-Kopie abgeschlossen, abgelaufene Zeit: 00:00:03
Beendet backup um 19.07.16

Archive Log katalogisiert
Archive Log-Dateiname=G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\ARCHIVE\ARC0000027920_0731252880.0001 RECID=27891 STAMP=917630444

Datendatei 1 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=4 STAMP=917630444 Dateiname=H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF
...

Inhalt von Speicher-Skript:
{ set until scn 8784584429999;
  recover
  clone database delete archivelog;
}
Speicher-Skript wird ausgeführt

Befehl wird ausgeführt: SET until clause

Starten recover um 19.07.16
Kanal ORA_AUX_DISK_1 wird benutzt
Kanal ORA_AUX_DISK_2 wird benutzt

Media Recovery starten

Archive Log für Thread 1 mit Sequence 27920 bereits auf Datenträger als Datei G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\ARCHIVE\ARC0000027920_0731252880.0001 vorhanden
Archive Log-Dateiname=G:\HUGO_ORACLE\SAN_2\ORADATA\AUX\ARCHIVE\ARC0000027920_0731252880.0001 Thread=1 Sequence=27920

Media Recovery abgeschlossen, abgelaufene Zeit: 00:00:07
Beendet recover um 19.07.16

Oracle-Instanz gestartet
Gesamte System Global Area  3206836224 Byte
Fixed Size           2259480 Byte
Variable Size        1744831976 Byte
Database Buffers      1442840576 Byte
Redo Buffers         16904192 Byte

Inhalt von Speicher-Skript:
{ sql clone "alter system set db_name = ''AUX'' comment = ''Reset to original value by RMAN'' scope=spfile";
  sql clone "alter system reset db_unique_name scope=spfile";
  shutdown clone immediate;
  startup clone nomount;
}
Speicher-Skript wird ausgeführt

SQL-Anweisung: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile
SQL-Anweisung: alter system reset db_unique_name scope=spfile

Oracle-Instanz heruntergefahren
Mit Hilfsdatenbank verbunden (nicht gestartet)
Oracle-Instanz gestartet
Gesamte System Global Area  3206836224 Byte
Fixed Size           2259480 Byte
Variable Size        1744831976 Byte
Database Buffers      1442840576 Byte
Redo Buffers         16904192 Byte

SQL-Anweisung: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 2336
  LOGFILE
  GROUP  1 ( 'F:\HUGO_ORACLE\SAN_3\ORADATA\AUX\REDO\REDO01A.ORA', 'F:\HUGO_ORACLE\SAN_4\ORADATA\AUX\REDO\REDO01B.ORA' ) SIZE 256 M REUSE,
  ...
  datafile  'H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF'
  CHARACTER SET WE8MSWIN1252

Inhalt von Speicher-Skript:
{
  set newname for tempfile 1 to "F:\HUGO_ORACLE\SAN_1\ORADATA\AUX\TEMP01.DBF";
  set newname for tempfile 2 to "F:\HUGO_ORACLE\SAN_1\ORADATA\AUX\TEMP02.DBF";
  switch clone tempfile all;
  catalog clone datafilecopy "H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF", ...;
  switch clone datafile all;
}
Speicher-Skript wird ausgeführt

Befehl wird ausgeführt: SET NEWNAME
...

Temporäre Datei 1 in Kontrolldatei in F:\HUGO_ORACLE\SAN_1\ORADATA\AUX\TEMP01.DBF umbenannt
...

Datendateikopie katalogisiert
Dateiname der Datendateikopie=H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF RECID=1 STAMP=917630517
...

Datendatei 2 gegen Datendateikopie ausgetauscht
Eingabe-Datendateikopie RECID=1 STAMP=917630517 Dateiname=H:\HUGO_ORACLE\SAN_3\ORADATA\AUX\SYSTEM01.DBF
...

Inhalt von Speicher-Skript:
{ Alter clone database open resetlogs;
}
Speicher-Skript wird ausgeführt

Datenbank geöffnet
Beendet Duplicate Db um 19.07.16
Advertisements

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s