Thinking Out Loud

October 8, 2011

Create Standby Database from Active Database

Filed under: 11g,Dataguard,RMAN — mdinh @ 2:45 pm

This is just another post on how to create standby database from active database. By the way, I do love Oracle’s technology most of the time.

This option was primarily chosen because the database size was ~400GB and there is no storage available to create backup for standby database.

The process took almost 6 hours and was running while I was sleeping🙂

Recovery Manager: Release 11.2.0.2.0 - Production on Fri Oct 7 21:49:57 2011
Finished Duplicate Db at 08-OCT-2011 03:31:22

Configuration:

PRIMARY pfile
> more initdb08.ora
ifile=/home/oracle/pfile/initprodoltp_common.ora
_log_deletion_policy=ALL
event="10298 trace name context forever, level 32"
*.audit_file_dest="/u01/app/oracle/admin/db08/adump"
*.control_files=/oracle/oradata/db08/control01.ctl,/oracle/oradata/db08/control02.ctl
*.db_16k_cache_size=2G
*.db_32k_cache_size=2G
*.db_cache_size=2G
*.db_name="db08"
*.db_unique_name="lax_db08"
*.fal_client=lax_db08
*.fal_server=phx_db08
*.log_archive_config="DG_CONFIG=(lax_db08,phx_db08)"
*.log_archive_dest_1="LOCATION=/oracle/oradata/db08/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lax_db08"
*.log_archive_dest_2='SERVICE=phx_db08 LGWR ASYNC COMPRESSION=ENABLE REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=phx_db08'
*.shared_pool_reserved_size=512M
*.shared_pool_size=3G

STANDBY pfile
> more initdb08.ora
ifile=/home/oracle/pfile/initdr_common.ora
_log_deletion_policy=ALL
event="10298 trace name context forever, level 32"
*.audit_file_dest='/u01/app/oracle/admin/db08/adump'
*.control_files='/oracle/oradata/db08/control01.ctl','/oracle/oradata/db08/control02.ctl'#Restore Controlfile
*.db_16k_cache_size=512M
*.db_32k_cache_size=512M
*.db_cache_size=512M
*.db_name='db08'
*.db_unique_name='phx_db08'
*.fal_client='phx_db08'
*.fal_server='lax_db08'
*.log_archive_config='DG_CONFIG=(lax_db08,phx_db08)'
*.log_archive_dest_1='LOCATION=/oracle/oradata/db08/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=phx_db08'
*.log_archive_dest_2='SERVICE=lax_db08 LGWR ASYNC REOPEN=15 NET_TIMEOUT=30 MAX_FAILURE=10 VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=lax_db08'
*.shared_pool_size=256M

RMAN rcv 
> cat db08.crstby.rcv
connect target;
connect auxiliary sys/password@phx_db08;
run {
DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;}
exit

I manually created the parameter and password file at the STANDBY, opted not to use spfile.

From STANDBY, start listener and database (nomount)

From PRIMARY, execute RMAN using RCV

> rman log=’db08.crstby.log’ cmdfile=’db08.crstby.rcv’

Snippets from the log file:

RMAN> connect target;
2> connect auxiliary *
3> run {
4> DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE DORECOVER NOFILENAMECHECK;}
5> exit
connected to target database: DB08 (DBID=1578229963)
connected to auxiliary database: DB08 (not mounted)

Starting Duplicate Db at 07-OCT-2011 21:50:02
using target database control file instead of recovery catalog
contents of Memory Script:
{
   backup as copy reuse
   targetfile  '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/orapwdb08' auxiliary format
 '/u01/app/oracle/product/11.2.0.2/dbhome_1/dbs/orapwdb08'   ;
}
executing Memory Script
contents of Memory Script:
{
   backup as copy current controlfile for standby auxiliary format  '/oracle/oradata/db08/control01.ctl';
   restore clone controlfile to  '/oracle/oradata/db08/control02.ctl' from
 '/oracle/oradata/db08/control01.ctl';
}
executing Memory Script
contents of Memory Script:
{
   sql clone 'alter database mount standby database';
}
executing Memory Script
contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oracle/oradata/db08/temp01.dbf";
   set newname for tempfile  2 to
 "/oracle/oradata/db08/temp02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oracle/oradata/db08/system01.dbf";
   set newname for datafile  2 to
 "/oracle/oradata/db08/undotbs01.dbf";
   set newname for datafile  3 to
 "/oracle/oradata/db08/sysaux01.dbf";
   set newname for datafile  4 to
 "/oracle/oradata/db08/undotbs02.dbf";
   set newname for datafile  5 to
 "/oracle/oradata/db08/ctxsys01.dbf";
~~~~
   backup as copy reuse
   datafile  1 auxiliary format
 "/oracle/oradata/db08/system01.dbf"   datafile
 2 auxiliary format
 "/oracle/oradata/db08/undotbs01.dbf"   datafile
 3 auxiliary format
 "/oracle/oradata/db08/sysaux01.dbf"   datafile
 4 auxiliary format
 "/oracle/oradata/db08/undotbs02.dbf"   datafile
 5 auxiliary format
 "/oracle/oradata/db08/ctxsys01.dbf"   datafile
   sql 'alter system archive log current';
}
executing Memory Script
Starting backup at 07-OCT-2011 21:50:45
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
Finished backup at 08-OCT-2011 03:24:14

sql statement: alter system archive log current

contents of Memory Script:
{
   backup as copy reuse
   archivelog like  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf" auxiliary format
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf"   archivelog like
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf" auxiliary format
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf"   archivelog like
 "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75242.dbf" auxiliary format
~~~
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75242.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75243.dbf";
   catalog clone archivelog  "/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75244.dbf";
   switch clone datafile all;
}
executing Memory Script
Starting backup at 08-OCT-2011 03:24:21
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=75240 RECID=135728 STAMP=763941140
channel ORA_DISK_2: starting archived log copy
input archived log thread=1 sequence=75241 RECID=135729 STAMP=763941998
channel ORA_DISK_3: starting archived log copy
input archived log thread=1 sequence=75242 RECID=135730 STAMP=763942998
channel ORA_DISK_4: starting archived log copy
input archived log thread=1 sequence=75243 RECID=135731 STAMP=763944071
Finished backup at 08-OCT-2011 03:26:56

cataloged archived log
archived log file name=/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75240.dbf RECID=1 STAMP=763961217

cataloged archived log
archived log file name=/oracle/oradata/db08/arch/arc_5e11e0cb_1_598985931_75241.dbf RECID=2 STAMP=763961218
contents of Memory Script:
{
   set until scn  10533738778008;
   recover
   standby
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 08-OCT-2011 03:27:19
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

starting media recovery
media recovery complete, elapsed time: 00:03:08
Finished recover at 08-OCT-2011 03:30:49
Finished Duplicate Db at 08-OCT-2011 03:31:22

Recovery Manager complete.

1 Comment »

  1. Hi, I used this method many times and it works fine, but now I have small issue – rman creates local spfile and not deletes it after clone proces is completed
    contents of Memory Script:
    {
    backup as copy current controlfile ….;
    restore clone controlfile to …;
    sql clone “create spfile from memory”;
    shutdown clone immediate;
    startup clone nomount;
    ….
    At the end standby runs wit RMAN’s spfile – I don’t know why…, I prefer pfile at this stage

    Comment by Leszek — May 10, 2013 @ 2:35 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

Gravatar
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 )

Cancel

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: