Cloning a database (ASM to ASM)

Cloning production ASM (RAC)  to ASM (non RAC)

In this document I will be cloning database SOURCEDB (11.1.0.6 RAC) to TARGETDB (11.1.0.6 single instance). There will be no downtime on the source database and minimal impact.

Source server source_server  150Gb data ASM storage used

Target server target_server

Pre-requirements -ensure that you have backed up the source database and are comfortable that you are covered in case anything goes wrong

200Gb of disk space

NFS mount between the 2 servers. In this case I created a directory /app/oradata/rman_backup_dir and arranged for a NFS mount to the same filesystem on target_server

source_server:/app/oradata/rman_backup_dir

403701760 42657229 338479316   11% /app/oradata/rman_backup_dir

The steps we will follow will be :-

• Prepare database on target server
• Backup source database
• Run duplicate database scripts

Note 1

RMAN uses a terminology of target as being the source database and auxiliary as the duplicated database. In this document I am using the terms source and target for the two databases. However the RMAN script will use target and auxiliary instead out of necessity.

Note 2

Initially I wanted to use active duplicate database however whilst it did work once it failed many more times and there is an Oracle bug Bug 6603587 – CREATION OF SECOND STANDBY USING RMAN DUPLICATE FAILS WITH RMAN-20242

I then tried using non-active database and with a database backup. This time I hit Bug.5653255: PHSB: RMAN CAN\’T RESTORE DATAFILES CORRECTLY ON ASM USING IMAGE COPIES. The workaround to this was to use a backupset..

Prepare target database

I am assuming that we have an existing database built on the target server and we are just performing a refresh from production. However for completeness this section shows how to prepare the spfile and startup nomount the target database.

If this process is a refresh rather than new build then go to here

Create a tnsnames.ora entry on the source server

Note that we are using port 1524 whereas the source database uses port 1522

TARGETDB_CLONE  =

(DESCRIPTION =

(ADDRESS_LIST=

(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))

)

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = TARGETDB)

)

)

Create a listener on the target server

Note that we are using port 1524 whereas the source database uses port 1522

LISTENERCLONE =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = target_server)(PORT = 1524))

)

)

SID_LIST_LISTENERCLONE =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = TARGETDB)

(ORACLE_HOME = /app/oracle/product/11.1.0/db_1/)

(SID_NAME = TARGETDB)

)

)

Start the listener

lsnrctl start listenerclone

On source_server

create pfile=\’/home/oracle/initTARGETDB.ora\’ from spfile;

Create pfile – target_server. Edit to remove references to RAC (threads etc). As I am moving from DATA/FRA diskgroup to DATA/FRA diskgroup I have no need to use any covert file_name parameters. Edit the spfile to replace the source SID name with the new SID

Edit /etc/oratab to add database entry

Copy the pfile from source_server

cd $ORACLE_HOME/dbs

scp oracle@source_server:/home/oracle/initTARGETDB.ora .

Create audit dest on targetserver

mkdir -p /app/oracle/admin/TARGETDB/adump

Copy passwordfile from source_server

scp oracle@source_server:/app/oracle/product/11.1.0/db_1/orapwSOURCEDB /app/oracle/product/11.1.0/db_1/orapwTARGETDB

Startup standby database from targetserver

sqlplus  / as sysdba

create spfile from pfile;

startup nomount

exit

Start the clone listener on target_server.

This is required as you cannot connect to a database that is unavailable with the asm listener

lsnrctl start listenerclone

From the primary database server source_server test the connection to the target database using the command:

sqlplus sys/password@TARGETDB_CLONE as sysdba

Prepare refreshed database

sqlplus  / as sysdba

shutdown immediate;

startup nomount;

exit

Backup source database

rm /app/oradata/rman_backup_dir/*

export NLS_DATE_FORMAT=\’YYYY-MM-DD:HH24:MI:SS\’

rman target /

run

{

allocate channel d1 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d2 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d3 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d4 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

allocate channel d5 device type disk format \’/app/oradata/rman_backup_dir/%U\’;

backup as backupset database plus archivelog;

}

This takes around 10 minutes. Make a note of the finish time (which is why the NLS_DATE_FORMAT variable was set)

Duplicate database

2 scripts have been created in /home/oracle on pebor02 which are pasted here

rman_duplicate_from_backup.sh

#

# Set the Oracle Environment

#

export ORACLE_HOME= /app/oracle/product/11.1.0/db_1/

export ORACLE_SID=SOURCEDB

export PATH=$ORACLE_HOME/bin:$PATH

export NLS_LANG=american

export NLS_DATE_FORMAT=\’YYYY-MM-DD:HH24:MI:SS\’

echo $ORACLE_HOME

echo $ORACLE_SID

#

# Run the RMAN Command

#

nohup  $ORACLE_HOME/bin/rman target sys/password nocatalog auxiliary sys/password@TARGETDB_clone @rman_duplicate_from_backup.cmd log=/home/oracle/rman_duplicate_from_backup.log  &

rman_duplicate_from_backup.cmd

run

{

set until scn 9660022443946;

allocate auxiliary channel d1 device type disk;

allocate auxiliary channel d2 device type disk;

allocate auxiliary channel d3 device type disk;

allocate auxiliary channel d4 device type disk;

allocate auxiliary channel d5 device type disk;

duplicate target database to TARGETDB;

}

Run the shell script  ./ rman_duplicate_from_backup.sh then it will fail with an invalid SCN, however  the output will provide the latest SCN that can be recovered until. Edit the rman_duplicate_from_backup.cmd file and replace the SCN and rerun.

You can get the SCN from the timestamp by this way you are letting Oracle do the work for you..

Restart the shell script and monitor the log file specified in the shell script.

Tidy up

Stop the listener_clone and start a listener for the newly refreshed database.

Remove the files from /app/oradata/rman_backup_dir

Notify the Apps team that the database has been refreshed and the time that the backup finished.

ORA-0600 1880 – archivelog compression

We had a problem this morning on a 10.2.0.3 database which runs our OMS repository. Enterprise manager access was hanging and a connect to the database took ages. The filesystem holding the bdump log files was full so I tidied that up but the database still had problems so I stopped OMS with opmnctl stopall, shutdown the database and then restarted it. The database failed to open with

SQL>startup

ORACLE instance started.

Total System Global Area 788529152 bytes

Fixed Size 2076176 bytes

Variable Size 243270128 bytes

Database Buffers 536870912 bytes

Redo Buffers 6311936 bytes

Database mounted.

ORA-00600: internal error code, arguments: [1880], [0x110462EF8],

[0x110462C90], [1], [0], [], [], []

The server was bounced and fsck performed on the filesystems but all to no avail.

Metalink only had one hit using the ORA000 lookup tool which was a reference (note 460217.1) to maxpiecesize in the RMAN configuration causing problems when set. I immediately thought to myself how could a RMAN config setting cause a database to fail to start when it has been started many times before. However we did not have that setting enabled.

We then started looking at the trace files (most of which we had deleted earlier to release disk space) and spotted the following line in the alert log (repeated many times)

Errors in file /app/oracle/admin/SID/bdump/mtsprd1a_arc1_1532034.trc:

ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=ORA; arguments: [1880] [0x1103EF620] [0x1103EF3B8] [1] [0]

Which pointed me to this in the trace files

Redo thread mounted by this instance: 1

Oracle process number: 122

Unix process pid: 786544, image: oracle@xxx (ARC1)

Detected dead process 786542; subsuming V$MANAGED_STANDBY slot

*** 2009-03-05 07:22:14.972

*** SERVICE NAME:(SYS$BACKGROUND) 2009-03-05 07:22:14.972

*** SESSION ID:(21.6335) 2009-03-05 07:22:14.972

Archivelog compression complete.

Input: 47124480 bytes Output: 45775293 bytes

Compression Performance: 94.00 percent or 7.77 bits per byte

*** 2009-03-05 07:22:14.974

ksedmp: internal or fatal error

ORA-00600: Message 600 not found; No message file for product=RDBMS, facility=ORA; arguments: [1880] [0x1103EF620] [0x1103EF3B8] [1] [0]

A Google search did not provide anything helpful but my colleague James Hardaker had seen something like this before and thought we should take off archivelog compression

startup mount;

alter database noarchivelog

alter database archivelog;

alter database open;

alter database archive log compress disable;

That did work for us and the database restarted cleanly but we need to investigate the cause of the problem. We believe it is around the area of lack of disk space when trying to compress. Hopefully the problem will be resolved as we are currently provisioning a new set of servers (HP Itanium) which will run a 2 node RAC cluster with a standby database on a third node. At the same time we will upgrade the EM database to 11.1.0.6 and OMS 10.2.0.4 which will improve supportability as well as performance.

Script to tidy archivelogs from ASM and RMAN

We have a pre-production RAC cluster that is kept in archivelog mode to allow true performance monitoring (and to be used for Streams and DataGuard testing). However we do not need the archivelogs for recovery purposes and as we perform high-volume testing the +FRA diskgroup (on solid-state disk) gets full very quickly.

I wrote a script that can be run to quickly free up space. It connects to the ASM instance and removes the logfiles.

It sets the SID and ORACLE_HOME to that of the RAC instance then runs RMAN to perform a crosscheck and delete of the archivelogs.

Not particularly complex but efficient.

export ORACLE_SID=+ASM1
export ORACLE_HOME=/u00/app/asm/product/11.1.0/db_1

asmcmd -p << EOF

ls FRA/RACCLUSTER/ARCHIVELOG/*2008*/*
rm -rf FRA/RACCLUSTER/ARCHIVELOG/*
ls FRA/RACCLUSTER/ARCHIVELOG/*
EOF

export ORACLE_SID=RACSID
export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

rman target / catalog username/password@catdb << EOF1

CHANGE ARCHIVELOG ALL VALIDATE;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
EOF1

RMAN CHECKSYNTAX

I tend to write on subjects that I have had problems with or on commands and syntax that are new to me or hard to find good working examples of.
I was trying to find out how to answer a prompt after an RMAN command within a script and I came across the CHECKSYNTAX command to RMAN. This came in with 10g
Very simple to use but also very helpful when putting an RMAN script together.

The documentation example is as good as any to try out

Create an flat file badcommand with the following contents :-

#command file with bad syntax commands
restore database
recover database

Unix_prompt> RMAN CHECKSYNTAX @badcommand

Recovery Manager: Release 11.1.0.6.0 – Production on Thu Jul 17 09:07:08 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> #command file with bad syntax commands
2> restore database
3> recover
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found “recover”: expecting one of: “archivelog, channel, check, controlfile, clone, database, datafile, device, from, force, high, (, preview, primary, ;, skip, spfile, skip readonly, standby, tablespace, to restore point, until, until restore point, validate”
RMAN-01007: at line 3 column 1 file: badcmdfile
Recovery Manager complete.

Edit the badcommand file and add semi-colons at the end of each line

Unix_prompt> RMAN CHECKSYNTAX @badcommand

Recovery Manager: Release 11.1.0.6.0 – Production on Thu Jul 17 09:07:33 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN> #command file with bad syntax commands
2> restore database;
3> recover database;
4>
The cmdfile has no syntax errors

Recovery Manager complete.

Overall nice and simple and well worth using when writing RMAN scripts

RMAN backup script – example – logging output

To answer a question about writing the output from RMAN commands to a logfile I posted a copy of a shell script I use to the Oracle-L newsgroup. It is a script I use for testing which does explain the multiple RMAN commands, much of which are commented out.

I

#!/bin/ksh

export ORACLE_HOME=/u00/app/oracle/product/11.1.0/db_2

export ORACLE_SID=TEST1

export PATH=$PATH:$ORACLE_HOME/bin

export NLS_DATE_FORMAT=’DD-MON-YY HH24:MI:SS’

export DATE=$(date +%Y-%m-%d)

rman target backup_admin/xxxxxxx catalog rman/xxxxxxx@server msglog /export/backups/rman/11g/logs/rman_full_backup_db_online_TEST1_${DATE}.log <<EOF

#backup AS COMPRESSED BACKUPSET database ;

#backup AS COMPRESSED BACKUPSET archivelog all delete input ;

backup AS COMPRESSED BACKUPSET database plus archivelog delete input;

#backup AS COMPRESSED BACKUPSET database;

#backup database;

#backup archivelog all delete input;

#BACKUP INCREMENTAL LEVEL 0 TAG = WEEKLY DATABASE;

#delete noprompt force obsolete;

#Change archivelog all validate

exit;

EOF

exit

 

From the Oracle documentation I also found the following notes

Using RMAN with Command Files
A command file is a text file which contains RMAN commands as you would enter them at the command line. You can run the a command file by specifying its name on the command line. The contents of the command file will be interpreted as if entered at the command line. If the LOG command line argument is specified, RMAN directs output to the named log file. Command files are one way to automate scheduled backups through an operating system job control facility.

In this example, a sample RMAN script is placed into a command file called commandfile.rcv. You can run this file from the operating system command line and write the output into the log file outfile.txt as follows:

% rman TARGET / CATALOG rman/cat@catdb CMDFILE commandfile.rcv LOG outfile.txt
Directing RMAN Output to a Log File
When you run RMAN in command line mode, it sends the output to the terminal. If you specify the LOG option, then RMAN writes the output to a specified log file instead.

Output for currently executing RMAN jobs is also stored in the V$RMAN_OUTPUT view, which reads only from memory (that is, the information is not stored in the control file). The V$RMAN_STATUS view contains metadata about jobs in progress as well as completed jobs. The metadata for completed jobs is stored in the control file.

ORA-27054 when using RMAN to NAS/NFS disks

Problem

When an NFS disk is mounted there are a set of requested mount attributes. It is perfectly possible for an NFS disk to mount with the correct actual attributes (rsize/wsize etc) even if these attributes are not explicitly listed in the original mount request. However, on some platforms Oracle does not check the “actual” attributes and can think, incorrectly, that the disk is not mounted properly. Some platforms include an auto-mount feature which auto mounts the NFS disks, again with correct attributes, but Oracle cannot determine the actual mount point and attributes of the auto-mounted disk/s. As the files look like NFS type then again ORA-27054 is thrown even though the disks are mounted correctly as Oracle cannot confirm the mount attributes.

Solution

Use correct mount options.

rw,bg,hard,nointr,rsize=32768,wsize=32768,tcp,vers=3,timeo=600,actimeo=0

default rsize and wsize for NFS is 4096 Blocks

Do not use auto-mount. Touching a file on the auto-mounted fs from your RMAN script does not wor.

set event=”10298 trace name context forever, level 32″ in the spfile and restart the instance. The event will disable the NFS checks in 10gR2 but it will also have some side effects:

- It will generate some trace files which you can ignore Bug:4998064 ‘Ksfq 10298 Trace Enabled For Levels Other Than Bit Level 0×2′ (unpublished)
- It can have a side effect of disabling direct IO on RAC using regular files on NFS mounts

This information comes from Metalink note 356199.1. I thought it worth commenting on as we have been testing RMAN network backups recently and I kept hitting the same problems as we have a sys admin who likes to use automount.