Skip to content

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

  • by
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.
  2. 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.
  3. Copy data files from raw devices by "dd".
  4. 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
    ...

  5. Copy data files by RMAN
  6. 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.
  7. Copy data files from ASM
  8. 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

  9. Backup by RMAN
  10. 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.
  11. Online duplicate by 11g RMAN
  12. 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

Leave a Reply

Your email address will not be published.