Clone an online database - applying standby feature

In a previous post, I described how to clone an online database by transferring data files through network. Since the cloned database should not be opened immediately after the copy, instead, it needed to be opened one week later. This was to minimize the maintenance window needed to configure the cloned database. In this scenario, we need to ship the archived logs from source to target, and also apply them in the target during the whole week in order to keep the source and target database in sync. For that purpose, I wrote a shell script and put it in the cron to automatically transfer the archived logs and then manually applying the logs.

Inspired by a fellow DBA recently, I have found that we can take advantage of Oracle standby technology to achieve the log transportation and apply purpose automatically. I thus did a test on a testing 10g database to demonstrate the concept, which consisted of the following steps:

1. Copied selected tablespaces from source to target, using the same method as in the previous post
    Tablespaces: SYSTEM, SYSAUX, USERS and UNDOTBS1

2. Created standby control file in source and copied them to the target.e.g
     a. At source, alter database create standby controlfile as '/tmp/stby.ctl';
     b. scp from source to target server
     c. At target, issue:
        cp /tmp/stby.ctl /db1/u01/oradata/orasid1/control01.ctl
        cp /tmp/stby.ctl /db1/u02/oradata/orasid1/control02.ctl
        cp /tmp/stby.ctl /db1/u03/oradata/orasid1/control03.ctl

3. Added in the init.ora a parameter: standby_file_management = manual
    Note: this is to take into account that data file directory may be different between source and targer server.

4. Mount the target database with the standby controlfile.
     I then did Offline drop all those data files that are not copied
   e.g.  alter database datafile 'filename' offline drop;

5. In mount mode, issue:
    SQL> RECOVER STANDBY DATABASE UNTIL CANCEL;
    Note: this is to apply all available archived logs upto the current time manually.

6. Started the managed recovery of the standby database at target server
    SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

7. In the source db issue:
    SQL> ALTER SYSTEM SET log_archive_dest_2='SERVICE=ORASID1_STBY LGWR ASYNC';

8. Verify archived log are shiped and applied automatically.
    for examples:
     SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

9. Opened the standby database as a regular operational database
    a. At source issue: ALTER SYSTEM SET log_archive_dest_2=''

    b. At target, cancel Redo Apply:
        ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
    c. At target issue: alter database activate physical standby database
    d. ALTER DATABASE OPEN;
    e. Drop those tablespace not copied and needed in the target server.

Archived redo log destination filling up

What can you do while the archived redo log destination gets filling up? One of the clients I worked before has set up a cron job to monitor the space and automatically move the old archived redo logs to the second location if certain threshold reaches. The script also does the cleaup for those acrhived logs older than three days ago. The client adopted NetApp snapshot for backup solution.

The current client I am working for uses different strategy. They also monitor the space, however instead of moving the arhived log, they change the archived redo log destination dynamically when the threshold reaches. In addtion, there is a rman job runing every 10-15 min to backup archived redo logs to tape and cleanup the logs.

When I try to test the syntax of changing archived redo log destination dynamically, I get confused by Oracle online documentation. After searching internet and test a little while, I have learned that the 'location' keyword is necessary and no space inside the quotation mark is allowed. While Oracle documention shows the space there. Examples are shown as follows:


- missing 'location' keyword

sys@DB10G> alter system set log_archive_dest_1='C:\temp';
alter system set log_archive_dest_1='C:\temp'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


- correct syntax

sys@DB10G> alter system set log_archive_dest_1='location=C:\temp';

System altered.

- space can not appear inside the quotation mark

sys@DB10G> alter system set log_archive_dest_1='location = C:\temp';
alter system set log_archive_dest_1='location = C:\temp'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


sys@DB10G> alter system set log_archive_dest_1='location = C:\';
alter system set log_archive_dest_1='location = C:\'
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-16179: incremental changes to "log_archive_dest_1" not allowed with SPFILE


- This is correct

sys@DB10G> alter system set log_archive_dest_1='location=C:\';

System altered.