There are several ways to create a standby database (besides having someone else do it for you), and one that isn’t often used is to copy the datafiles from one standby database to create another which uses a manual log transfer/registration mechanism. RMAN offers this capability (the file copy, not the manual log transfer/registration) and can be put to good use in this endeavor. Let’s see how this can be done.
RMAN can copy datafiles from filesystem to filesystem, from filesystem to ASM, from ASM to filesystem and, finally, from ASM to ASM. The example provided here will use two ASM diskgroups. MYBIGDG and MYEMPTYDG. We’re running a standby database using MYBIGDG so we know it’s already mounted. We need to mount MYEMPTYDG so we can use it:
ASM> alter diskgroup myemptydg mount;
Diskgroup altered.
ASM>
[The prompt is set to display ASM to avoid confusion between the ASM instance and any regular database.] We have the empty destination diskgroup available, and it is very important that this diskgroup IS completely empty if you’re using Oracle Managed Files and, if it is not, it needs to be dropped and recreated to ensure that the group does not run out of space. [Oracle Managed File names are generated at the time the file is created, and will not be the same as existing files in the diskgroup for the same database, hence the requirement the diskgroup be completely empty.] Now let’s take a peek at the RMAN script to copy the datafiles:
connect target /;
connect rcvcat 'RMAN/###########@rcovcat';
run{
allocate channel backup_ch1 DEVICE TYPE DISK;
allocate channel backup_ch2 DEVICE TYPE DISK;
allocate channel backup_ch3 DEVICE TYPE DISK;
allocate channel backup_ch4 DEVICE TYPE DISK;
allocate channel backup_ch5 DEVICE TYPE DISK;
allocate channel backup_ch6 DEVICE TYPE DISK;
allocate channel backup_ch7 DEVICE TYPE DISK;
allocate channel backup_ch8 DEVICE TYPE DISK;
copy
datafile 1 to '+MYEMPTYDG',
datafile 2 to '+MYEMPTYDG',
datafile 3 to '+MYEMPTYDG',
datafile 4 to '+MYEMPTYDG',
datafile 5 to '+MYEMPTYDG',
datafile 6 to '+MYEMPTYDG',
datafile 7 to '+MYEMPTYDG',
datafile 8 to '+MYEMPTYDG',
datafile 9 to '+MYEMPTYDG',
datafile 10 to '+MYEMPTYDG',
datafile 11 to '+MYEMPTYDG',
datafile 12 to '+MYEMPTYDG',
datafile 13 to '+MYEMPTYDG',
datafile 14 to '+MYEMPTYDG',
datafile 15 to '+MYEMPTYDG',
datafile 16 to '+MYEMPTYDG',
datafile 17 to '+MYEMPTYDG',
datafile 18 to '+MYEMPTYDG',
datafile 19 to '+MYEMPTYDG',
datafile 20 to '+MYEMPTYDG',
datafile 21 to '+MYEMPTYDG',
datafile 22 to '+MYEMPTYDG',
datafile 23 to '+MYEMPTYDG',
datafile 24 to '+MYEMPTYDG',
datafile 25 to '+MYEMPTYDG',
datafile 26 to '+MYEMPTYDG',
datafile 27 to '+MYEMPTYDG',
datafile 28 to '+MYEMPTYDG',
datafile 29 to '+MYEMPTYDG',
datafile 30 to '+MYEMPTYDG';
)
exit;
We’re making new copies of all of the datafiles in the source standby database; this doesn’t create or copy the controlfile and the names generated if using Oracle Managed Files will differ from the source names, but we won’t worry about that now. Set the environment for the existing standby database then run the script using rman command-line syntax:
nohup timex rman cmdfile='copy_standby_files.rcv' log='copy_standby_files.log' &
Using nohup keeps the process going unattended and is a nice addition if connection problems plague you; timex (a UNIX utility) reports on the time required to complete the command. I use it to gauge how long a task will take the next time I run it. The & puts the command in the background so you can do other tasks while this is running, like fry fish, make raisin bread or fix that nagging temp space issue one of the developers reported a while back. Depending on the size of the files this could take a while to complete so have patience.
Time passes, seasons change, your kids graduate from college and the copy process is complete. [Okay, okay, it won’t take THAT long but it may seem like it.] It’s now time to get this new standby started. We’ll need a standby controlfile and that can be generated from the primary database:
SQL> alter database create standby controlfile as '/sneem/flurbor/my_standby.ctl';
Database altered.
SQL>
Of course if this new standby database is on another server some additional tasks will be necessary, such as dismounting the MYEMPTYDG diskgroup and having the Unix team dismount the storage from the standby server and mount it on the server for the new standby database. Copy the standby controlfile that was just created to the server where the new standby database will call home. Edit the init.ora file for the new standby database so it will use this controlfile.
Since this configuration is using manual log transfer the time has come to enable that process on the existing standby server; scp can be configured to connect to the new standby server without requiring a password (a nice feature in secure data centers) and thus can be used to transfer the log file via a cron job. Talk with your friendly neighborhood UNIX administrator on how best to script this transfer.
Registering the logs in the new standby is a fairly simple task to script:
#!/bin/ksh
. $HOME/.profile
cd /home/oracle/arch_log_xfer
cat /dev/null > register_arch_logs.sql
cd /u10/newstdby/arch
set - `ls -1tr *.arc`
cd /home/oracle/arch_log_xfer
while [ $# -gt 1 ]
do
echo "alter database register logfile '//arch/"$1"';" >> register_sc_arch_logs.sql
shift
done
sqlplus /nolog <<EOF
connect / as sysdba
@register_arch_logs
EOF
Notice the last log in the ls -tr listing is not processed; this prevents the script from registering a logfile still locked for writing. That log will be picked up when the next log is being transferred, so the process is one log behind. This is not a problem as it keeps Oracle from terminating the managed recovery because the file that’s still being written is smaller than the header reports when Oracle tries to apply it. After the logs are applied you’ll need to delete them and that’s another easy script to write:
#!/bin/ksh
. $HOME/.profile
cd /home/oracle/arch_log_xfer
sqlplus /nolog <<EOF
connect / as sysdba
@rm_applied_logs.sql
EOF
The SQL script looks like this:
set pagesize 0 linesize 200 trimspool on feedback ofF
select 'if [ -f '||name||' ] ; then rm '||name||' ; fi'
from v$archived_log
where applied = 'YES'
spool del_applied_logs.sh
/
spool off
!chmod 755 del_applied_logs.sh
!./del_applied_logs.sh
Start these cron jobs after the log transfer has started.
When everything is in place it’s time to startup and mount the new standby database:
SQL> connect / as sysdba
Connected.
SQL> startup mount pfile="/u01/app/oracle/product/10.2.0/dbs/initNEWSTDBY.ora"
Oracle instance starting.
[The usual messages/memory areas/sizes displayed here]
Database mounted.
SQL>
It’s time to use RMAN to ‘fix’ those datafile names. This is done in two steps after connecting RMAN to the target WITHOUT using the catalog:
$ rman target / nocatalog
[...]
RMAN> catalog start with '+MYEMPTYDG/newstdby/datafiles';
[A list of datafiles in the diskgroup are listed and you are prompted to continue, requiring a YES or NO response. Type YES to catalog these names in the controlfile.]
RMAN>
Once that task is complete the second step is to get RMAN to rename the files in the controlfile for you:
RMAN> switch database to copy;
[RMAN will display the progress of the switch and should, eventually, report successful completion.]
RMAN>
Presuming all went according to plan (you’ll know if it didn’t) it should now be possible to start managed recovery on the new standby database and verify the logs are being applied:
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select process, status, sequence#
2 from v$managed_standby
3 /
PROCESS STATUS SEQUENCE#
--------- ------------ ----------
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
ARCH CONNECTED 0
MRP0 APPLYING_LOG 76338
11 rows selected.
SQL>
You now have a secondary standby database, getting its logs from the original standby database ‘manually’. And you didn’t need to perform an RMAN duplicate and disrupt the production database. You can’t ask for more than that.
Well, okay, you CAN, but odds are you won’t get it.
Oracle Tips and Tricks -- David Fitzjarrell 