Building a Physical Standby Database (07/14) - Prepare data files on primary database

Building a Physical Standby Database (06/14) - Prepare standby controlfile on primary database

There are various ways to prepare the data files, but you would be better to offline the data files or enter begin backup mode before copy the data files to maintain the consistency of data except RMAN, which can keep the same SCN across all files with a online database.

1. Copy data files from a file system.
Since the media type of  carriers could be tapes, you might consider to zip those files from the file system and transport the tapes to the disaster recovery site.

2. Copy data files from raw devices by "dd".
Before using Unix command "dd" to output your files, you should know the db_block_size and every data file's blocks.
SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------------------
db_block_size                        integer     8192
Then, check the blocks of every data file.
SQL> column name format a50;
SQL> column blocks format 999,999,999;
SQL> select name, blocks from v$datafile;

NAME                                                     BLOCKS
-------------------------------------------------- ------------
/u01/app/oracle/oradata/primdb/system01.dbf              61,440
/u01/app/oracle/oradata/primdb/undotbs01.dbf              3,200
/u01/app/oracle/oradata/primdb/sysaux01.dbf              30,720
/u01/app/oracle/oradata/primdb/users01.dbf                  640
/u01/app/oracle/oradata/primdb/example01.dbf             12,800
Last, you must add 1 block of header to the blocks of every data file.
# dd if=/dev/rdisk04 of=/tmp/datafiles/system01.dbf bs=8k count=61441
# dd if=/dev/rdisk05 of=/tmp/datafiles/undotbs01.dbf bs=8k count=3201
...

3. Copy data files by RMAN
You can use RMAN to output the data files no matter where the data files are:
RMAN> run {
2> allocate channel c1 device type disk format '/tmp/datafiles/%U';
3> backup as copy database;
4> }

using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=103 instance=primdb1 device type=DISK

Starting backup at 08-NOV-12
channel c1: starting datafile copy
input datafile file number=00001 name=+DATA/primdb/datafile/system.897.797943475
output file name=/tmp/datafiles/data_D-COMPDB_I-841830157_TS-SYSTEM_FNO-1_0cnpq84s tag=TAG20121108T201732 RECID=18 STAMP=798826720
channel c1: datafile copy complete, elapsed time: 00:01:15
channel c1: starting datafile copy
input datafile file number=00002 name=+DATA/primdb/datafile/sysaux.898.797943569
output file name=/tmp/datafiles/data_D-COMPDB_I-841830157_TS-SYSAUX_FNO-2_0dnpq877 tag=TAG20121108T201732 RECID=19 STAMP=798826804
channel c1: datafile copy complete, elapsed time: 00:01:23
channel c1: starting datafile copy
input datafile file number=00003 name=+DATA/primdb/datafile/undotbs1.899.797943687
output file name=/tmp/datafiles/data_D-COMPDB_I-841830157_TS-UNDOTBS1_FNO-3_0enpq89q tag=TAG20121108T201732 RECID=20 STAMP=798826821
channel c1: datafile copy complete, elapsed time: 00:00:17
channel c1: starting datafile copy
input datafile file number=00005 name=+DATA/primdb/datafile/example.900.797943711
output file name=/tmp/datafiles/data_D-COMPDB_I-841830157_TS-EXAMPLE_FNO-5_0fnpq8ab tag=TAG20121108T201732 RECID=21 STAMP=798826837
channel c1: datafile copy complete, elapsed time: 00:00:17
channel c1: starting datafile copy
input datafile file number=00006 name=+DATA/primdb/datafile/undotbs2.901.797943737
output file name=/tmp/datafiles/data_D-COMPDB_I-841830157_TS-UNDOTBS2_FNO-6_0gnpq8as tag=TAG20121108T201732 RECID=22 STAMP=798826849
channel c1: datafile copy complete, elapsed time: 00:00:09
channel c1: starting datafile copy
copying current control file
output file name=/tmp/datafiles/cf_D-COMPDB_id-841830157_0hnpq8b5 tag=TAG20121108T201732 RECID=23 STAMP=798826856
channel c1: datafile copy complete, elapsed time: 00:00:07
channel c1: starting datafile copy
input datafile file number=00004 name=+DATA/primdb/datafile/users.902.797943753
output file name=/tmp/datafiles/data_D-COMPDB_I-841830157_TS-USERS_FNO-4_0inpq8bc tag=TAG20121108T201732 RECID=24 STAMP=798826861
channel c1: datafile copy complete, elapsed time: 00:00:01
channel c1: starting full datafile backup set
channel c1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel c1: starting piece 1 at 08-NOV-12
channel c1: finished piece 1 at 08-NOV-12
piece handle=/tmp/datafiles/0jnpq8bd_1_1 tag=TAG20121108T201732 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 08-NOV-12
You nay need to rename these data files to original after copying. Because the names of data files were all renamed by RMAN.
Data files in raw devices would be faster to output all data files by RMAN.

4. Copy data files from ASM
You can get the data files from 11g ASM via ASMCMD command, this is a new feature to let you copy the files out of ASM
ASMCMD> cp +data/compdb/datafile/EXAMPLE.264.761506243 /tmp/example01.dbf
copying +data/compdb/datafile/EXAMPLE.264.761506243 -> /tmp/example01.dbf

5. Backup by RMAN
Backup the whole database by RMAN into backup sets is a fast way to prepare data files.
RMAN> BACKUP DATABASE FORMAT '/tmp/database_backups/%U.bak' TAG for_creating_standby KEEP UNTIL TIME 'SYSDATE + 3';

Starting backup at 08-NOV-12
current log archived

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=94 instance=primdb1 device type=DISK
backup will be obsolete on date 11-NOV-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=+DATA/primdb/datafile/system.897.797943475
input datafile file number=00002 name=+DATA/primdb/datafile/sysaux.898.797943569
input datafile file number=00003 name=+DATA/primdb/datafile/undotbs1.899.797943687
input datafile file number=00005 name=+DATA/primdb/datafile/example.900.797943711
input datafile file number=00006 name=+DATA/primdb/datafile/undotbs2.901.797943737
input datafile file number=00004 name=+DATA/primdb/datafile/users.902.797943753
channel ORA_DISK_1: starting piece 1 at 08-NOV-12
channel ORA_DISK_1: finished piece 1 at 08-NOV-12
piece handle=/tmp/database_backups/08npq6cs_1_1.bak tag=FOR_CREATING_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:07

using channel ORA_DISK_1
backup will be obsolete on date 11-NOV-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 08-NOV-12
channel ORA_DISK_1: finished piece 1 at 08-NOV-12
piece handle=/tmp/database_backups/09npq6gu_1_1.bak tag=FOR_CREATING_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

current log archived
using channel ORA_DISK_1
backup will be obsolete on date 11-NOV-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=2 sequence=59 RECID=408 STAMP=798824852
input archived log thread=1 sequence=61 RECID=409 STAMP=798824993
input archived log thread=2 sequence=60 RECID=410 STAMP=798824994
channel ORA_DISK_1: starting piece 1 at 08-NOV-12
channel ORA_DISK_1: finished piece 1 at 08-NOV-12
piece handle=/tmp/database_backups/0anpq6h8_1_1.bak tag=FOR_CREATING_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

using channel ORA_DISK_1
backup will be obsolete on date 11-NOV-12
archived logs required to recover from this backup will be backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 08-NOV-12
channel ORA_DISK_1: finished piece 1 at 08-NOV-12
piece handle=/tmp/database_backups/0bnpq6h9_1_1.bak tag=FOR_CREATING_STANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 08-NOV-12

RMAN> list backup tag=for_creating_standby;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.17G      DISK        00:02:07     08-NOV-12
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: FOR_CREATING_STANDBY
        Piece Name: /tmp/database_backups/08npq6cs_1_1.bak
        Keep: BACKUP_LOGS        Until: 11-NOV-12
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 3471112    08-NOV-12 +DATA/primdb/datafile/system.897.797943475
  2       Full 3471112    08-NOV-12 +DATA/primdb/datafile/sysaux.898.797943569
  3       Full 3471112    08-NOV-12 +DATA/primdb/datafile/undotbs1.899.797943687
  4       Full 3471112    08-NOV-12 +DATA/primdb/datafile/users.902.797943753
  5       Full 3471112    08-NOV-12 +DATA/primdb/datafile/example.900.797943711
  6       Full 3471112    08-NOV-12 +DATA/primdb/datafile/undotbs2.901.797943737

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    80.00K     DISK        00:00:00     08-NOV-12
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: FOR_CREATING_STANDBY
        Piece Name: /tmp/database_backups/09npq6gu_1_1.bak
        Keep: BACKUP_LOGS        Until: 11-NOV-12
  SPFILE Included: Modification time: 08-NOV-12
  SPFILE db_unique_name: PRIMDB

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3       184.50K    DISK        00:00:00     08-NOV-12
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: FOR_CREATING_STANDBY
        Piece Name: /tmp/database_backups/0anpq6h8_1_1.bak
        Keep: BACKUP_LOGS        Until: 11-NOV-12

  List of Archived Logs in backup set 3
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    61      3471091    08-NOV-12 3471406    08-NOV-12
  2    59      3470837    08-NOV-12 3471096    08-NOV-12
  2    60      3471096    08-NOV-12 3471411    08-NOV-12

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    18.61M     DISK        00:00:09     08-NOV-12
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: FOR_CREATING_STANDBY
        Piece Name: /tmp/database_backups/0bnpq6h9_1_1.bak
        Keep: BACKUP_LOGS        Until: 11-NOV-12
  Control File Included: Ckp SCN: 3471460      Ckp time: 08-NOV-12
You can see there are four pieces in the backup set.

6. Online duplicate by 11g RMAN
Since 11g RMAN can duplicate all related files in one execution, if you are planning to use it, you can skip the step.

Building a Physical Standby Database (08/14) - Copy data files into standby database