DBA survival BLOG

Some DBA stuff and lots of Oracle Data Guard

Script that duplicates a database using a physical standby RAC as source

Posted on March 18, 2012 by Ludovico

It’s possibile to duplicate a database for testing purposes (it’s an example) using a standby database as source. This allows you to off-load the production environment.

This is a simple script that makes use of ASM and classic duplicate, although I guess it’s possible to use the standby DB for a duplicate from active database.
You can launch it everyday to align your test env at a point in time.

#!/bin/bash
if [ $USER != 'oracle' ] ; then
        echo "need to be oracle"
        exit 0
fi

. $HOME/set11
export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM  from your old test dbfiles:
## it's as simple as running with the CORRECT ENV:
## asmcmd rm -rf \
##   +DATA/TEST/ONLINELOG
##   +DATA/TEST/DATAFILE
##   +DATA/TEST/CONTROLFILE
##   +DATA/TEST/TEMPFILE
##   +FRA/TEST/ONLINELOG
##   +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF
        set echo on
        startup nomount
        alter system set cluster_database=false scope=spfile;
        shutdown immediate
        startup nomount
EOF

rman <<EOF
connect target sys/mystrongpassword@stdby
connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF


sqlplus / as sysdba <<EOF
        set echo on
        alter system set cluster_database=true scope=spfile;
        shutdown immediate
        startup mount
        alter database noarchivelog;
        shutdown immediate
EOF


srvctl start database -d test

#!/bin/bash

if [ $USER != 'oracle' ] ; then

echo "need to be oracle"

exit 0

. $HOME/set11

export ORACLE_SID=test1

srvctl stop database -d test -o immediate

## this is supposed to be a script that erase your ASM from your old test dbfiles:

## it's as simple as running with the CORRECT ENV:

## asmcmd rm -rf \

## +DATA/TEST/ONLINELOG

## +DATA/TEST/DATAFILE

## +DATA/TEST/CONTROLFILE

## +DATA/TEST/TEMPFILE

## +FRA/TEST/ONLINELOG

## +FRA/TEST/CONTROLFILE

ssh grid@testsrv /shared/refresh_test/remove_test_files.sh

sqlplus / as sysdba < <EOF

set echo on

startup nomount

alter system set cluster_database=false scope=spfile;

shutdown immediate

startup nomount

EOF

rman <<EOF

connect target sys/mystrongpassword@stdby

connect auxiliary /

duplicate database to 'test' until time "trunc(sysdate)+6/24";

EOF

sqlplus / as sysdba <<EOF

set echo on

alter system set cluster_database=true scope=spfile;

shutdown immediate

startup mount

alter database noarchivelog;

shutdown immediate

EOF

srvctl start database -d test

Bio
Latest Posts

Ludovico

Principal Product Manager at Oracle

Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

Latest posts by Ludovico (see all)

New views in Oracle Data Guard 23c - January 3, 2024
New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023

12 thoughts on “Script that duplicates a database using a physical standby RAC as source”

Ludovico on February 21, 2020 at 3:32 pm said:

Subbu, have you tried the steps of my blog post? what is the outcome?

Reply ↓
Subbarao Kommalapati on November 15, 2019 at 4:10 am said:

Hi Ludovico,

Test Database subjected to Clone from Standby standalone ( as a Source environment is not working on 12.1.0.2 .. Can you please add up all the Notes , Sufficient information and Requisites required

Thanks
Subbu

Reply ↓
- Subbu on February 16, 2020 at 8:37 am said:
  
  Standby as Source won’t work and I’ve checked with Oracle as well .
  
  Can you please throw some light ?
  
  Reply ↓
  - Ludovico on February 17, 2020 at 12:38 pm said:
    
    Well… it used to work with 10g. In 11g it was not working anymore. I haven’t tried from 12c onwards.
    Which version are you using?
    
    Reply ↓
  - Subbu on February 18, 2020 at 6:48 pm said:
    
    I am trying to use it 12c version environment ..Can you assist me here
    
    Standby as Source is with Mounted by status and don’t have any active data guard configured
    
    Reply ↓
Slim on January 23, 2018 at 4:11 pm said:

Hi Ludovico,

i try to duplicate from standby database to a test one , on different server , using backup on ZDLRA, but it fail with the error below :

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 01/23/2018 15:04:06
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-00600: internal error, arguments [6007] [] [] [] []

could you help please ?

Best regards,
Slim

Reply ↓
Hector Barcelata Carvajal on August 30, 2017 at 7:55 pm said:

Hi Ludovico,
I’m trying to do a duplicate form a standby to a test database on the same server, the version are 11.2.0.3 and the Rman comnad is failing at the end when is trying to open with resetlog the cloned DB, and if I try to do a recover I have an ORA-00600 interla error, do youhave any Idea?

contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script

released channel: c1
released channel: c2
released channel: c3
released channel: c4
released channel: cr1
released channel: cr2
released channel: cr3
released channel: cr4
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 08/29/2017 18:04:27
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01152: file 2 was not restored from a sufficiently old backup
ORA-01110: data file 2: ‘/app/testmaster1/oradata/datafiles/undotbs01.dbf’

SQL> recover database using backup controlfile until cancel;
ORA-00283: recovery session canceled due to errors
ORA-00600: internal error code, arguments: [kcvhvdf_1], [], [], [], [], [], [],
[], [], [], [], []

Reply ↓
- Ludovico on September 6, 2017 at 1:30 pm said:
  
  Hi Hector, did you specify correctly the new control file path? and the parameters to convert the path correctly?
  
  Reply ↓
  - Subbarao Kommalapati on August 7, 2019 at 5:47 pm said:
    
    Hi Ludovico ,
    
    Thanks for sharing us DB articles which are very helpful .
    
    Can you please let me know that Standby Database (Standalone ) works for refreshing test database on 12cR1 .. please provide your Comments
    
    Reply ↓
    - Ludovico on August 23, 2019 at 2:49 pm said:
      
      Thanks!
      
      Reply ↓
Rudy on September 13, 2013 at 12:26 pm said:

Hi Ludovico,
yes, you can “duplicate from active” a standby database. Tested on 11gR2 in the last hour.

I remember I saw something in the Oracle manuals that went like “you cannot duplicate a standby”, so I never attempted. I can’t find the quote now though.
But now I saw your blog post, gave it a try and succeeded.

The main obstacle preventing us to use duplication is that backups are not available at test locations (too big), but standbys are.
However, duplicating a standby is still cumbersome because only a subset of the data is actually needed on test databases, and duplicating a subset of the tablespaces won’t help.
Nevertheless, this technique turns out to be really useful for small databases.

With 12c and storage-snapshot-aware RMAN, things are likely to get easier.

Thanks

PS: Nice blog, I just added it to my feed reader.

Rodolfo
ITOUG, dbaitalia.org (owner)

Reply ↓
- Ludovico on September 13, 2013 at 3:37 pm said:
  
  Hi Rodolfo,
  
  thank you for your positive feedback.
  I’ve not tested yet the new 12c storage-aware snapshot copy feature, but I’ve a customer who owns a ZFS appliance, I hope I’ll convince them to test the 12c soon (and blog about the feature you mention, consequently). 🙂
  
  Hope to meet you soon, why not, as soon as the ITOUG starts boost its activities. 😉
  
  Best regards
  Ludovico
  
  Reply ↓

Ludovico

Latest posts by Ludovico (see all)

12 thoughts on “Script that duplicates a database using a physical standby RAC as source”

Leave a Reply Cancel reply