How to Duplicate a Standalone Database: ASM to ASM
Posted at Thursday, September 18, 2008
This example covers how to duplicate a 10.2.0.4 database using ASM from one host to another, which is also using ASM. This example uses RMAN without a catalog to disk, but could be quite easily retrofit to support tape channels or the use of a recovery catalog. The RMAN backup is written to a file system available to both hosts.
In my example, I am duplicating my Grid Control Database from one host to another (EMREP, duplicating to EMTST).
The term 'TARGET' is used to refer to the Primary Host/Database (The Database you are copying). The term 'AUXILIARY' is used to refer to the Duplicate Host/Database (The New Cloned Database you are creating).
Backup the TARGET Database (EMREP)
backup_emrep.rman
run_backup.bsh
Run the Backup
The output should look something like this:
The filesystem should now house a series of files, similar to:
Prepare the Auxiliary Host for the Duplicate Database (EMTST)
Update /etc/oratab
Create a password file for the Database (EMTST)
Create any Required Directories for the Database (EMTST)
Update the Oracle Networking Files as appropriate (EMTST)
Test connectivity to all services involved in the DUPLICATE operation, for example:
Create/Update the Listener with a Static Entry (EMTST)
If creating a new LISTENER, start it:
Create Necessary ASM Directories on the Host of the New Database (EMTST)
Copy the Parameter File from the TARGET to the AUXILIARY(EMREP to EMTST)
Edit the Parameter File, Changing Necessary Values on Auxiliary Host (EMTST)
In this example, the Diskgroup is switched from DATA to DATA2. I will create the online redo logs as part of the DUPLICATE clause.
Create an SPFILE and startup nomount the Auxiliary Instance
Create a Connection File that Obfuscates Passwords for RMAN
connect.rman
Change the file's ownership:
Perform the DUPLICATE operation on the Auxiliary Host
duplicate_emrep_to_emtst.rman
Run the aforementioned script
A successful DUPLICATE should yield output similar to this:
Logon to the the Auxiliary Database (EMTST) and Verify
Here is how to adjust the aforementioned example to use an RMAN Recovery Catalog
Create the RMAN Recovery Catalog
Create the Recovery Catalog and Register the TARGET Database (EMREP)
Create a Connection File on the TARGET to obfuscate the RMAN password
rman.connect
Modify the file's permissions
Add the following line to the previously created backup script, backup_emrep.rman
Create a New Backup of the TARGET (EMREP)
Update the Connect File on the AUXILIARY (EMTST)
rman.connect
Add the following line:
Drop the Previously Created Database (EMTST)
Duplicate the Database using the Recovery Catalog
You may have noticed the following error earlier on in this example:
"WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only"
Using OMF (Oracle Managed Files) and ASM with this parameter is a little tricky. I prefer to discontinue the use of OMF by relocating the files and renaming them; for example:
Once all the files have been explicitly named, file name conversion becomes much more straightforward.
If you are using explicitly named files on ASM, then you can alter db_file_name_convert; for example:
Provided that the necessary directories are there, this should work. You will, however, see the aforementioned error message. The intended mapping should - in essence - have the intended effects.
In my example, I am duplicating my Grid Control Database from one host to another (EMREP, duplicating to EMTST).
The term 'TARGET' is used to refer to the Primary Host/Database (The Database you are copying). The term 'AUXILIARY' is used to refer to the Duplicate Host/Database (The New Cloned Database you are creating).
Backup the TARGET Database (EMREP)
backup_emrep.rman
run {
configure device type disk parallelism 4 backup type to compressed backupset;
configure default device type to disk;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '/u02/oracle/ora
1/emrep/%F';
configure maxsetsize to 5G;
configure snapshot controlfile name to '/u02/oracle/ora1/emrep/snapf_emrep.ora';
backup full database format '/u02/oracle/ora1/emrep/%d_%U.rman' tag rman_nocat_e
mrep plus archivelog format '/u02/oracle/ora1/emrep/%d_%U.rman' delete input tag
rman_nocat_emrep ;
}run_backup.bsh
#!/bin/bash
export $ORACLE_SID=emrep
. oraenv
rman target=/ @backup_emrep.rman
Run the Backup
$ ./run_backup.bsh > rman_emrep.log
The output should look something like this:
$ more rman_emrep.log
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 18 12:13:21 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: EMREP (DBID=3888623110)
RMAN> run {
2> configure device type disk parallelism 4 backup type to compressed backupset;
3> configure default device type to disk;
4> configure controlfile autobackup on;
5> configure controlfile autobackup format for device type disk to '/u02/oracle/ora1/emrep/%F';
6> configure maxsetsize to 5G;
7> configure snapshot controlfile name to '/u02/oracle/ora1/emrep/snapf_emrep.ora';
8> backup full database format '/u02/oracle/ora1/emrep/%d_%U.rman' tag rman_nocat_emrep plus archivelog format '/u02/orac
le/ora1/emrep/%d_%U.rman' delete input tag rman_nocat_emrep ;
9> }
10>
using target database control file instead of recovery catalog
old RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters:
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oracle/ora1/emrep/%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u02/oracle/ora1/emrep/%F';
new RMAN configuration parameters are successfully stored
old RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 5 G;
new RMAN configuration parameters:
CONFIGURE MAXSETSIZE TO 5 G;
new RMAN configuration parameters are successfully stored
snapshot control file name set to: /u02/oracle/ora1/emrep/snapf_emrep.ora
new RMAN configuration parameters are successfully stored
Starting backup at 18-SEP-08
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=135 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=144 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=125 devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=68 recid=53 stamp=665756004
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_24jqt8b5_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_68.263.665756005 recid=53 stamp=665756004
Finished backup at 18-SEP-08
Starting backup at 18-SEP-08
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00005 name=+DATA2/emrep/datafile/mgmt_tablespace.256.665690321
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_2: starting compressed full datafile backupset
channel ORA_DISK_2: specifying datafile(s) in backupset
input datafile fno=00001 name=+DATA2/emrep/datafile/system.257.665690973
input datafile fno=00006 name=+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169
channel ORA_DISK_2: starting piece 1 at 18-SEP-08
channel ORA_DISK_3: starting compressed full datafile backupset
channel ORA_DISK_3: specifying datafile(s) in backupset
input datafile fno=00002 name=+DATA2/emrep/datafile/undotbs1.258.665691079
input datafile fno=00003 name=+DATA2/emrep/datafile/sysaux.259.665691133
channel ORA_DISK_3: starting piece 1 at 18-SEP-08
channel ORA_DISK_4: starting compressed full datafile backupset
channel ORA_DISK_4: specifying datafile(s) in backupset
input datafile fno=00004 name=+DATA2/emrep/datafile/users.261.665691205
channel ORA_DISK_4: starting piece 1 at 18-SEP-08
channel ORA_DISK_4: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_4: backup set complete, elapsed time: 00:00:18
channel ORA_DISK_3: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_3: backup set complete, elapsed time: 00:01:23
channel ORA_DISK_2: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:31
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:56
Finished backup at 18-SEP-08
Starting backup at 18-SEP-08
current log archived
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=69 recid=54 stamp=665756192
channel ORA_DISK_1: starting piece 1 at 18-SEP-08
channel ORA_DISK_1: finished piece 1 at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman tag=RMAN_NOCAT_EMREP comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_69.263.665756189 recid=54 stamp=665756192
Finished backup at 18-SEP-08
Starting Control File and SPFILE Autobackup at 18-SEP-08
piece handle=/u02/oracle/ora1/emrep/c-3888623110-20080918-01 comment=NONE
Finished Control File and SPFILE Autobackup at 18-SEP-08
Recovery Manager complete.
The filesystem should now house a series of files, similar to:
$ ls -lart
total 160138
drwxr-xr-x 9 oracle dba 4096 Sep 18 11:36 ../
-rw-r----- 1 oracle dba 376320 Sep 18 12:13 EMREP_24jqt8b5_1_1.rman
-rw-r----- 1 oracle dba 98304 Sep 18 12:13 EMREP_28jqt8b9_1_1.rman
-rw-r----- 1 oracle dba 76972032 Sep 18 12:14 EMREP_27jqt8b9_1_1.rman
-rw-r----- 1 oracle dba 54304768 Sep 18 12:14 EMREP_26jqt8b8_1_1.rman
-rw-r----- 1 oracle dba 22044672 Sep 18 12:16 EMREP_25jqt8b8_1_1.rman
-rw-r----- 1 oracle dba 100352 Sep 18 12:16 EMREP_29jqt8h1_1_1.rman
drwxr-xr-x 2 oracle dba 4096 Sep 18 12:16 ./
-rw-r----- 1 oracle dba 5079040 Sep 18 12:16 c-3888623110-20080918-01
-rw-r----- 1 oracle dba 4997120 Sep 18 12:16 snapf_emrep.ora
Prepare the Auxiliary Host for the Duplicate Database (EMTST)
Update /etc/oratab
echo "emtst:/u01/app/oracle/product/10.2:N" >> /etc/oratab
Create a password file for the Database (EMTST)
$ cd $ORACLE_HOME/dbs
$ orapwd file=orapwemtst password=shine123
Create any Required Directories for the Database (EMTST)
$ mkdir -p /u01/app/oracle/admin/emtst/bdump
$ mkdir -p /u01/app/oracle/admin/emtst/udump
$ mkdir -p /u01/app/oracle/admin/emtst/cdump
$ mkdir -p /u01/app/oracle/admin/emtst/adump
Update the Oracle Networking Files as appropriate (EMTST)
$ cd $TNS_ADMIN
$ vi tnsnames.ora
EMREP =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emrep.colestock.test)
(INSTANCE_NAME = emrep)
)
)
EMTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = emtst.colestock.test)
(INSTANCE_NAME = emtst)
)
)
Test connectivity to all services involved in the DUPLICATE operation, for example:
$ tnsping emrep
TNS Ping Utility for Linux: Version 10.2.0.4.0 - Production on 18-SEP-2008 13:27:46
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.colestock.test)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = emrep.colestock.test) (INSTANCE_NAME = emrep)))
OK (0 msec)
Create/Update the Listener with a Static Entry (EMTST)
$ vi listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/product/10.2)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = emtst.colestock.test)
(ORACLE_HOME = /u01/app/oracle/product/10.2)
(SID_NAME = emtst)
)
)
If creating a new LISTENER, start it:
$ lsnrctl start LISTENER
LSNRCTL for Linux: Version 11.1.0.6.0 - Production on 18-SEP-2008 13:26:13
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting /u01/app/oracle/product/11.1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.1.0.6.0 - Production
System parameter file is /u01/app/oracle/product/11.1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.colestock.test)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.1.0.6.0 - Production
Start Date 18-SEP-2008 13:26:15
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rac2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac2.colestock.test)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "emtst.colestock.test" has 1 instance(s).
Instance "emtst", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
Create Necessary ASM Directories on the Host of the New Database (EMTST)
$ export ORACLE_SID=+ASM2
$ . oraenv
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.1 is /u01/app/oracle
$ asmcmd
ASMCMD> cd DATA
ASMCMD> mkdir EMTST
ASMCMD> cd EMTST
ASMCMD> mkdir CONTROLFILE
ASMCMD> mkdir ONLINELOG
Copy the Parameter File from the TARGET to the AUXILIARY(EMREP to EMTST)
$ export ORACLE_SID=emrep
$ . oraenv
$ sqlplus "/ as sysdba"
SQL> create pfile='/tmp/initemtst.ora' from spfile;
scp /tmp/initemtst.ora oracle@rac2:/u01/app/oracle/product/10.2/dbs/.
oracle@rac2's password:
initemtst.ora 100% 1409 1.4KB/s 00:00
cd $ORACLE_HOME/dbs
Edit the Parameter File, Changing Necessary Values on Auxiliary Host (EMTST)
In this example, the Diskgroup is switched from DATA to DATA2. I will create the online redo logs as part of the DUPLICATE clause.
vi initemtst.ora
*.background_dump_dest='/u01/app/oracle/admin/emtst/bdump'
*.core_dump_dest='/u01/app/oracle/admin/emtst/cdump'
*.db_file_name_convert=('+DATA2','+DATA')
*.control_files='+DATA/EMTST/CONTROLFILE/control01.ctl','+DATA/EMTST/CONTROLFILE/control02.ctl','+DATA/EMTST/CONTROLFILE/control03.ctl'
*.db_name='emtst'
*.log_archive_dest_1='LOCATION=+DATA'
*.log_archive_format='emtst_%t_%s_%r.arch'
*.user_dump_dest='/u01/app/oracle/admin/emtst/udump'
Create an SPFILE and startup nomount the Auxiliary Instance
SQL> create spfile from pfile;
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 536870912 bytes
Fixed Size 1268484 bytes
Variable Size 503317756 bytes
Database Buffers 25165824 bytes
Redo Buffers 7118848 bytes
Create a Connection File that Obfuscates Passwords for RMAN
connect.rman
connect auxiliary / ;
connect target sys/password@emrep ;
Change the file's ownership:
chmod 711 connect.rman
Perform the DUPLICATE operation on the Auxiliary Host
duplicate_emrep_to_emtst.rman
@connect.rman
run {
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
allocate auxiliary channel aux3 device type disk;
duplicate target database to emtst logfile
group 1 ('+DATA/EMTST/ONLINELOG/redo_0101.log','+DATA/EMTST/ONLINELOG/redo_0102.log') SIZE 100M REUSE,
group 2 ('+DATA/EMTST/ONLINELOG/redo_0201.log','+DATA/EMTST/ONLINELOG/redo_0202.log') SIZE 100M REUSE,
group 3 ('+DATA/EMTST/ONLINELOG/redo_0301.log','+DATA/EMTST/ONLINELOG/redo_0302.log') SIZE 100M REUSE,
group 4 ('+DATA/EMTST/ONLINELOG/redo_0401.log','+DATA/EMTST/ONLINELOG/redo_0402.log') SIZE 100M REUSE;
}
Run the aforementioned script
rman @duplicate_emrep_to_emtst.rman > duplicate_emrep_to_emtst.log &
A successful DUPLICATE should yield output similar to this:
$ more duplicate_emrep_to_emtst.log
Recovery Manager: Release 10.2.0.4.0 - Production on Thu Sep 18 16:18:02 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN> @connect.rman
2> connect auxiliary *;
3> connect target *;
4> **end-of-file**
5> run {
6> allocate auxiliary channel aux1 device type disk;
7> allocate auxiliary channel aux2 device type disk;
8> allocate auxiliary channel aux3 device type disk;
9> duplicate target database to emtst logfile
10> group 1 ('+DATA/EMTST/ONLINELOG/redo_0101.log','+DATA/EMTST/ONLINELOG/redo_0102.log') SIZE 100M REUSE,
11> group 2 ('+DATA/EMTST/ONLINELOG/redo_0201.log','+DATA/EMTST/ONLINELOG/redo_0202.log') SIZE 100M REUSE,
12> group 3 ('+DATA/EMTST/ONLINELOG/redo_0301.log','+DATA/EMTST/ONLINELOG/redo_0302.log') SIZE 100M REUSE,
13> group 4 ('+DATA/EMTST/ONLINELOG/redo_0401.log','+DATA/EMTST/ONLINELOG/redo_0402.log') SIZE 100M REUSE;
14> }
15>
connected to auxiliary database: EMTST (not mounted)
connected to target database: EMREP (DBID=3888623110)
using target database control file instead of recovery catalog
allocated channel: aux1
channel aux1: sid=156 devtype=DISK
allocated channel: aux2
channel aux2: sid=155 devtype=DISK
allocated channel: aux3
channel aux3: sid=154 devtype=DISK
Starting Duplicate Db at 18-SEP-08
WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only.
contents of Memory Script:
{
set until scn 5512981;
set newname for datafile 1 to
"+data";
set newname for datafile 2 to
"+data";
set newname for datafile 3 to
"+data";
set newname for datafile 4 to
"+data";
set newname for datafile 5 to
"+data";
set newname for datafile 6 to
"+data";
restore
check readonly
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 18-SEP-08
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +DATA
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman
channel aux2: starting datafile backupset restore
channel aux2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +DATA
restoring datafile 00003 to +DATA
channel aux2: reading from backup piece /u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman
channel aux3: starting datafile backupset restore
channel aux3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +DATA
restoring datafile 00006 to +DATA
channel aux3: reading from backup piece /u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_28jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:00:15
channel aux1: starting datafile backupset restore
channel aux1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +DATA
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman
channel aux2: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_27jqt8b9_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux2: restore complete, elapsed time: 00:01:20
channel aux3: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_26jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux3: restore complete, elapsed time: 00:01:20
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_25jqt8b8_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:02:30
Finished restore at 18-SEP-08
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "EMTST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( '+DATA/EMTST/ONLINELOG/redo_0101.log', '+DATA/EMTST/ONLINELOG/redo_0102.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DATA/EMTST/ONLINELOG/redo_0201.log', '+DATA/EMTST/ONLINELOG/redo_0202.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DATA/EMTST/ONLINELOG/redo_0301.log', '+DATA/EMTST/ONLINELOG/redo_0302.log' ) SIZE 100 M REUSE,
GROUP 4 ( '+DATA/EMTST/ONLINELOG/redo_0401.log', '+DATA/EMTST/ONLINELOG/redo_0402.log' ) SIZE 100 M REUSE
DATAFILE
'+DATA/emtst/datafile/system.282.665770693'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=665770855 filename=+DATA/emtst/datafile/undotbs1.283.665770693
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=665770855 filename=+DATA/emtst/datafile/sysaux.292.665770695
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=665770855 filename=+DATA/emtst/datafile/users.284.665770693
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=665770855 filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=665770855 filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695
contents of Memory Script:
{
set until scn 5512981;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 18-SEP-08
starting media recovery
archive log thread 1 sequence 70 is already on disk as file +DATA2/emrep/archivelog/2008_09_18/thread_1_seq_70.
280.665766251
channel aux1: starting archive log restore to default destination
channel aux1: restoring archive log
archive log thread=1 sequence=69
channel aux1: reading from backup piece /u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman
channel aux1: restored backup piece 1
piece handle=/u02/oracle/ora1/emrep/EMREP_29jqt8h1_1_1.rman tag=RMAN_NOCAT_EMREP
channel aux1: restore complete, elapsed time: 00:00:02
archive log filename=+DATA/emtst/archivelog/2008_09_18/thread_1_seq_69.287.665770857 thread=1 sequence=69
channel clone_default: deleting archive log(s)
archive log filename=+DATA/emtst/archivelog/2008_09_18/thread_1_seq_69.287.665770857 recid=1 stamp=665770856
archive log filename=+DATA2/emrep/archivelog/2008_09_18/thread_1_seq_70.280.665766251 thread=1 sequence=70
media recovery complete, elapsed time: 00:00:32
Finished recover at 18-SEP-08
contents of Memory Script:
{
shutdown clone;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 536870912 bytes
Fixed Size 1268484 bytes
Variable Size 503317756 bytes
Database Buffers 25165824 bytes
Redo Buffers 7118848 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "EMTST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 454
LOGFILE
GROUP 1 ( '+DATA/EMTST/ONLINELOG/redo_0101.log', '+DATA/EMTST/ONLINELOG/redo_0102.log' ) SIZE 100 M REUSE,
GROUP 2 ( '+DATA/EMTST/ONLINELOG/redo_0201.log', '+DATA/EMTST/ONLINELOG/redo_0202.log' ) SIZE 100 M REUSE,
GROUP 3 ( '+DATA/EMTST/ONLINELOG/redo_0301.log', '+DATA/EMTST/ONLINELOG/redo_0302.log' ) SIZE 100 M REUSE,
GROUP 4 ( '+DATA/EMTST/ONLINELOG/redo_0401.log', '+DATA/EMTST/ONLINELOG/redo_0402.log' ) SIZE 100 M REUSE
DATAFILE
'+DATA/emtst/datafile/system.282.665770693'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 2 to
"+data";
switch clone tempfile all;
catalog clone datafilecopy "+DATA/emtst/datafile/undotbs1.283.665770693";
catalog clone datafilecopy "+DATA/emtst/datafile/sysaux.292.665770695";
catalog clone datafilecopy "+DATA/emtst/datafile/users.284.665770693";
catalog clone datafilecopy "+DATA/emtst/datafile/mgmt_tablespace.291.665770703";
catalog clone datafilecopy "+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed temporary file 2 to +data in control file
cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/undotbs1.283.665770693 recid=1 stamp=665770907
cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/sysaux.292.665770695 recid=2 stamp=665770907
cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/users.284.665770693 recid=3 stamp=665770907
cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703 recid=4 stamp=665770907
cataloged datafile copy
datafile copy filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695 recid=5 stamp=665770907
datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=665770907 filename=+DATA/emtst/datafile/undotbs1.283.665770693
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=665770907 filename=+DATA/emtst/datafile/sysaux.292.665770695
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=665770907 filename=+DATA/emtst/datafile/users.284.665770693
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=665770907 filename=+DATA/emtst/datafile/mgmt_tablespace.291.665770703
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=665770907 filename=+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 18-SEP-08
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-12005: error during channel cleanup
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Recovery Manager complete.
Logon to the the Auxiliary Database (EMTST) and Verify
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
+DATA/emtst/datafile/system.282.665770693
+DATA/emtst/datafile/undotbs1.283.665770693
+DATA/emtst/datafile/sysaux.292.665770695
+DATA/emtst/datafile/users.284.665770693
+DATA/emtst/datafile/mgmt_tablespace.291.665770703
+DATA/emtst/datafile/mgmt_ecm_depot_ts.293.665770695
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
+DATA/emtst/controlfile/control01.ctl
+DATA/emtst/controlfile/control02.ctl
+DATA/emtst/controlfile/control03.ctl
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
+DATA/emtst/onlinelog/redo_0401.log
+DATA/emtst/onlinelog/redo_0402.log
+DATA/emtst/onlinelog/redo_0301.log
+DATA/emtst/onlinelog/redo_0302.log
+DATA/emtst/onlinelog/redo_0201.log
+DATA/emtst/onlinelog/redo_0202.log
+DATA/emtst/onlinelog/redo_0101.log
+DATA/emtst/onlinelog/redo_0102.log
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
+DATA/emtst/tempfile/temp.295.665770955
Here is how to adjust the aforementioned example to use an RMAN Recovery Catalog
Create the RMAN Recovery Catalog
$ export ORACLE_SID=jlc2
$ . oraenv
$ sqlplus "/ as sysdba"
SQL> create user rman identified by rman
2 temporary tablespace temp
3 default tablespace users quota unlimited on users;
User created.
SQL> grant recovery_catalog_owner to rman;
Grant succeeded.
Create the Recovery Catalog and Register the TARGET Database (EMREP)
$ export ORACLE_SID=emrep
$ . oraenv
$ rman catalog=rman/rman@jlcc target=/
RMAN> create catalog;
recovery catalog created
RMAN> register database;
Create a Connection File on the TARGET to obfuscate the RMAN password
rman.connect
connect catalog rman/rman@jlc;
Modify the file's permissions
$ chmod 711 rman.connect
Add the following line to the previously created backup script, backup_emrep.rman
@rman.connect
Create a New Backup of the TARGET (EMREP)
./run_backup.bsh > rman_emrep.log
Update the Connect File on the AUXILIARY (EMTST)
rman.connect
Add the following line:
connect catalog rman/rman@jlc;
Drop the Previously Created Database (EMTST)
$ export ORACLE_SID=emtst
$ . oraenv
SQL> shutdown immediate;
SQL> startup restrict mount;
SQL> drop database;
SQL> exit;
$ sqlplus "/ as sysdba"
SQL> startup nomount;
SQL> exit
Duplicate the Database using the Recovery Catalog
rman @duplicate_emrep_to_emtst.rman > duplicate_emrep_to_emtst.log &
You may have noticed the following error earlier on in this example:
"WARNING: DB_FILE_NAME_CONVERT resulted in invalid ASM names; names changed to diskgroup only"
Using OMF (Oracle Managed Files) and ASM with this parameter is a little tricky. I prefer to discontinue the use of OMF by relocating the files and renaming them; for example:
RMAN> COPY DATAFILE '+DATA2/emrep/datafile/undotbs1.258.665691079' TO '+DATA2/emrep/datafile/undotbs01.dbf';
SQL> alter database rename file '+DATA2/emrep/datafile/undotbs1.258.665691079' TO '+DATA2/emrep/datafile/undotbs01.dbf';
Once all the files have been explicitly named, file name conversion becomes much more straightforward.
If you are using explicitly named files on ASM, then you can alter db_file_name_convert; for example:
*.db_file_name_convert=('+DATA2/emrep','+DATA/emtst')Provided that the necessary directories are there, this should work. You will, however, see the aforementioned error message. The intended mapping should - in essence - have the intended effects.
How to Migrate a Standalone Database to ASM
Posted at Thursday, September 18, 2008
The following example shows how to migrate a standalone database to ASM. My environment uses an ASM instance which is version 11.1, ASMLib, and a 10.2.0.4 database (my Grid Control database) on Linux.
I will move the EM database to the DATA2 diskgroup:
In order to migrate the database, it must be in ARCHIVELOG mode. If not in ARCHIVELOG mode, place the database in it now
Backup the Database to the Diskgroup(s)
Output should be similar to the following:
You should see entries in the alert log similar to:
During the backup you should see files created in the Diskgroup:
Switch the Database to the Copy on ASM
You should see the datafiles assigned to their new locations:
Switch Tempfiles to the ASM Diskgroups
Switch the Online Redo Logs to the ASM diskgroup
You should see the Online Redo Logs created in the ASM instance:
Switch through Logfile Groups and drop as possible
Afterwards, only the Online Redo Logs on the ASM diskgroup should remain:
Migrate the Controlfiles to ASM
The control_files parameter should be updated via RMAN if you are using an spfile. If using a pfile, you will have to update this value yourself.
Optionally Migrate the Archive Log Files
Afterwards, you should see Archivelogs in the ASM instance:
Drop the Copy of the Database
Obviously, you should distribute your files in a way that maximizes I/O throughput, etc. This example simply demonstrates mechanically how to perform the migration.
I will move the EM database to the DATA2 diskgroup:
ASMCMD> lsdg
State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N 512 4096 1048576 25023 21597 0 21597 0 DATA/
MOUNTED EXTERN N 512 4096 1048576 25023 24930 0 24930 0 DATA2/
In order to migrate the database, it must be in ARCHIVELOG mode. If not in ARCHIVELOG mode, place the database in it now
$ sqlplus "/ as sysdba"
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 10
Current log sequence 13
SQL> alter system set log_archive_format='emrep_%t_%s_%r.arch' scope=spfile;
System altered.
SQL> alter system set log_archive_dest_1='LOCATION=/u02/oracle/arch/' scope=spfile;
System altered.
SQL> startup mount;
ORACLE instance started.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/oracle/arch/
Oldest online log sequence 13
Next log sequence to archive 16
Current log sequence 16
SQL> alter system switch logfile;
System altered.
SQL> alter system archive log current;
System altered.
SQL> ! ls -lart /u02/oracle/arch
total 28506
drwxr-xr-x 2 oracle dba 4096 Apr 16 15:31 lost+found
drwxrwxr-x 8 oracle dba 4096 Sep 14 12:39 ..
-rw-r----- 1 oracle dba 29053440 Sep 17 10:43 emrep_1_16_665591431.arch
drwxr-xr-x 3 oracle dba 24576 Sep 17 10:44 .
-rw-r----- 1 oracle dba 103424 Sep 17 10:44 emrep_1_17_665591431.arch
Backup the Database to the Diskgroup(s)
$ rman target=/
RMAN> backup as copy database format '+DATA2';
Output should be similar to the following:
Starting backup at 17-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=146 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u01/app/oracle/oradata/emrep/mgmt.dbf
output filename=+DATA2/emrep/datafile/mgmt_tablespace.256.665690321 tag=TAG20080917T175835 recid=1 stamp=665690971
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:10:57
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u01/app/oracle/oradata/emrep/system01.dbf
output filename=+DATA2/emrep/datafile/system.257.665690973 tag=TAG20080917T175835 recid=2 stamp=665691068
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:45
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u01/app/oracle/oradata/emrep/undotbs01.dbf
output filename=+DATA2/emrep/datafile/undotbs1.258.665691079 tag=TAG20080917T175835 recid=3 stamp=665691129
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u01/app/oracle/oradata/emrep/sysaux01.dbf
output filename=+DATA2/emrep/datafile/sysaux.259.665691133 tag=TAG20080917T175835 recid=4 stamp=665691166
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf
output filename=+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169 tag=TAG20080917T175835 recid=5 stamp=665691194
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u01/app/oracle/oradata/emrep/users01.dbf
output filename=+DATA2/emrep/datafile/users.261.665691205 tag=TAG20080917T175835 recid=6 stamp=665691204
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA2/emrep/controlfile/backup.262.665691205 tag=TAG20080917T175835 recid=7 stamp=665691207
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 17-SEP-08
channel ORA_DISK_1: finished piece 1 at 17-SEP-08
piece handle=+DATA2/emrep/backupset/2008_09_17/nnsnf0_tag20080917t175835_0.263.665691209 tag=TAG20080917T175835 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 17-SEP-08
You should see entries in the alert log similar to:
Starting background process ASMB
ASMB started with pid=52, OS id=10868
Starting background process RBAL
RBAL started with pid=53, OS id=10885
Wed Sep 17 17:22:20 2008
SUCCESS: diskgroup DATA2 was mounted
During the backup you should see files created in the Diskgroup:
$ export ORACLE_SID=+ASM1
$ . oraenv
ASMCMD> cd DATA2/EMREP/DATAFILE
ASMCMD> ls
MGMT_TABLESPACE.256.665690321
SYSTEM.257.665690973
Switch the Database to the Copy on ASM
RMAN> shutdown immediate;
RMAN> startup mount;
database mounted
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA2/emrep/datafile/system.257.665690973"
datafile 2 switched to datafile copy "+DATA2/emrep/datafile/undotbs1.258.665691079"
datafile 3 switched to datafile copy "+DATA2/emrep/datafile/sysaux.259.665691133"
datafile 4 switched to datafile copy "+DATA2/emrep/datafile/users.261.665691205"
datafile 5 switched to datafile copy "+DATA2/emrep/datafile/mgmt_tablespace.256.665690321"
datafile 6 switched to datafile copy "+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169"
RMAN> recover database;
Starting recover at 17-SEP-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=153 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-SEP-08
RMAN> alter database open;
You should see the datafiles assigned to their new locations:
SQL> col name format a60
SQL> select name from v$datafile;
NAME
------------------------------------------------------------
+DATA2/emrep/datafile/system.257.665690973
+DATA2/emrep/datafile/undotbs1.258.665691079
+DATA2/emrep/datafile/sysaux.259.665691133
+DATA2/emrep/datafile/users.261.665691205
+DATA2/emrep/datafile/mgmt_tablespace.256.665690321
+DATA2/emrep/datafile/mgmt_ecm_depot_ts.260.665691169
6 rows selected.
Switch Tempfiles to the ASM Diskgroups
SQL> alter tablespace temp add tempfile '+DATA2' size 250M;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------
/u01/app/oracle/oradata/emrep/temp01.dbf
+DATA2/emrep/tempfile/temp.264.665692035
SQL> alter database tempfile '/u01/app/oracle/oradata/emrep/temp01.dbf' drop including datafiles;
Database altered.
SQL> select name from v$tempfile;
NAME
------------------------------------------------------------
+DATA2/emrep/tempfile/temp.264.665692035
Switch the Online Redo Logs to the ASM diskgroup
SQL> alter database add logfile group 5 ('+DATA2','+DATA2') size 100M;
Database altered.
SQL> alter database add logfile group 6 ('+DATA2','+DATA2') size 100M;
Database altered.
SQL> alter database add logfile group 7 ('+DATA2','+DATA2') size 100M;
Database altered.
SQL> alter database add logfile group 8 ('+DATA2','+DATA2') size 100M;
Database altered.You should see the Online Redo Logs created in the ASM instance:
ASMCMD> cd DATA2/EMREP/ONLINELOG
ASMCMD> ls
group_5.265.665693021
group_5.266.665693027
group_6.267.665693117
group_6.268.665693121
group_7.269.665693133
group_7.270.665693141
group_8.271.665693163
group_8.272.665693169
Switch through Logfile Groups and drop as possible
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
Afterwards, only the Online Redo Logs on the ASM diskgroup should remain:
SQL> select bytes/1024/1024, group#, thread#, status from v$log;
BYTES/1024/1024 GROUP# THREAD# STATUS
--------------- ---------- ---------- ----------------
100 5 1 ACTIVE
100 6 1 ACTIVE
100 7 1 ACTIVE
100 8 1 CURRENT
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------------------------------
+DATA2/emrep/onlinelog/group_5.265.665693021
+DATA2/emrep/onlinelog/group_5.266.665693027
+DATA2/emrep/onlinelog/group_6.267.665693117
+DATA2/emrep/onlinelog/group_6.268.665693121
+DATA2/emrep/onlinelog/group_7.269.665693133
+DATA2/emrep/onlinelog/group_7.270.665693141
+DATA2/emrep/onlinelog/group_8.271.665693163
+DATA2/emrep/onlinelog/group_8.272.665693169
Migrate the Controlfiles to ASM
SQL> alter system set control_files='+DATA2','+DATA2','+DATA2' scope=spfile;
System altered.
SQL> shutdown immediate;
$ rman target=/
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 17 19:03:37 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
database is already started
RMAN> restore controlfile from '/u01/app/oracle/oradata/emrep/control01.ctl';
Starting restore at 17-SEP-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA2/emrep/controlfile/current.273.665694281
output filename=+DATA2/emrep/controlfile/current.274.665694285
output filename=+DATA2/emrep/controlfile/current.275.665694285
Finished restore at 17-SEP-08
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> alter database open;
The control_files parameter should be updated via RMAN if you are using an spfile. If using a pfile, you will have to update this value yourself.
Optionally Migrate the Archive Log Files
SQL> alter system set log_archive_dest_1='LOCATION=+DATA2' scope=spfile;
System altered.
SQL> shutdown immediate;
SQL> startup
SQL> alter system archive log current;
Afterwards, you should see Archivelogs in the ASM instance:
$ asmcmd
ASMCMD> ls DATA2/EMREP/ARCHIVELOG
2008_09_17/
ASMCMD> cd DATA2/EMREP/ARCHIVELOG/2008_09_17
ASMCMD> ls
thread_1_seq_61.276.665694577
Drop the Copy of the Database
$ rman target=/
Recovery Manager: Release 10.2.0.4.0 - Production on Wed Sep 17 19:12:26 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: EMREP (DBID=3888623110)
RMAN> list copy of database;
using target database control file instead of recovery catalog
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
8 1 A 17-SEP-08 2607286 17-SEP-08 /u01/app/oracle/oradata/emrep/system01.dbf
9 2 A 17-SEP-08 2607286 17-SEP-08 /u01/app/oracle/oradata/emrep/undotbs01.dbf
10 3 A 17-SEP-08 2607286 17-SEP-08 /u01/app/oracle/oradata/emrep/sysaux01.dbf
11 4 A 17-SEP-08 2607286 17-SEP-08 /u01/app/oracle/oradata/emrep/users01.dbf
12 5 A 17-SEP-08 2607286 17-SEP-08 /u01/app/oracle/oradata/emrep/mgmt.dbf
13 6 A 17-SEP-08 2607286 17-SEP-08 /u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf
RMAN> drop copy of database;
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/system01.dbf recid=8 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/undotbs01.dbf recid=9 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/sysaux01.dbf recid=10 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/users01.dbf recid=11 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/mgmt.dbf recid=12 stamp=665691542
deleted datafile copy
datafile copy filename=/u01/app/oracle/oradata/emrep/mgmt_ecm_depot1.dbf recid=13 stamp=665691542
Deleted 6 objects
Obviously, you should distribute your files in a way that maximizes I/O throughput, etc. This example simply demonstrates mechanically how to perform the migration.
How to Migrate an Existing RAC database to ASM
Posted at Sunday, May 25, 2008
This post covers how to migrate an existing RAC database to use ASM storage. In my case, I use a 2-node, 10gR2 database on Linux that is currently using OCFS2 filesystems for the database's files. I will migrate the database to ASM using ASMLib on top of iSCSI volumes. This example assumes that you have already prepared the relevant storage devices for recognition by the O/S.
Prepare the Storage
As root on one of the hosts, first identify the device names to format:
Format the devices you plan to use for the ASM migration:
Probe each host to pickup the changes via:
Configure ASMLib on each host via:
On one node as root, create the ASM Volumes via:
On all other nodes, scan to recognize the changes via:
Create the ASM Instances
Create directories for the clustered ASM instance on shared storage. In my case, I will put it on the same OCFS2 volumes hosting the voting and cluster registry files for CRS.
Create the initial ASM instance parameter file on the shared storage:
init+ASM.ora
Link to the parameter file from each host:
Create the passwordfile on shared storage and link to it from each ASM instance:
Link to the passwordfile on each node:
Update /etc/oratab on each node:
Create the ASM Diskgroups:
Now that you have confirmed that the ASM instance is functional, create the spfile from pfile:
Update each node to reflect use of the spfile:
init+ASM1.ora
init+ASM2.ora
Register the clustered ASM instance with srvctl
Start and verify ASM instances:
Prepare to migrate the database in question to use ASM as storage
Query the names of the database files:
Migrate the Database to ASM
Update the control_files parameter in preparation for migration:
Migrate the controlfile:
Migrate the Datafiles:
Open the Database by exiting RMAN and then issuing the following via SQL*Plus:
Startup the other instances:
Create a new Temporary Tablespace on the ASM Diskgroup:
Create new Online Redo Log Groups:
Create ASM Directories
Create New Online Redo Log Groups for each public thread before dropping the old ones
Drop the Old Online Redo Log Groups using the traditional process, for example:
Optionally, drop the old datafile images:
Migrate the Archive Log Destinations to ASM:
At this point, you should be able to query an ASM instance and find archivelogs in the BACKUPS diskgroup:
Prepare the Storage
As root on one of the hosts, first identify the device names to format:
# ./iscsi-map.sh
Host / SCSI ID SCSI Device Name iSCSI Target Name
---------------- ----------------------- -----------------
2 /dev/sdb asm2
3 /dev/sde asm1
4 /dev/sdc arch
5 /dev/sdf software
6 /dev/sdd logs
7 /dev/sdg ora2
8 /dev/sdi ora1
9 /dev/sdh crs
Format the devices you plan to use for the ASM migration:
# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 25024.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-25024, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-25024, default 25024):
Using default value 25024
Command (m for help): p
Disk /dev/sdb: 26.2 GB, 26239565824 bytes
64 heads, 32 sectors/track, 25024 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot Start End Blocks Id System
/dev/sdb1 1 25024 25624560 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
# fdisk /dev/sde
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel. Changes will remain in memory only,
until you decide to write them. After that, of course, the previous
content won't be recoverable.
The number of cylinders for this disk is set to 25024.
There is nothing wrong with that, but this is larger than 1024,
and could in certain setups cause problems with:
1) software that runs at boot time (e.g., old versions of LILO)
2) booting and partitioning software from other OSs
(e.g., DOS FDISK, OS/2 FDISK)
Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
Command (m for help): n
Command action
e extended
p primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-25024, default 1):
Using default value 1
Last cylinder or +size or +sizeM or +sizeK (1-25024, default 25024):
Using default value 25024
Command (m for help): p
Disk /dev/sde: 26.2 GB, 26239565824 bytes
64 heads, 32 sectors/track, 25024 cylinders
Units = cylinders of 2048 * 512 = 1048576 bytes
Device Boot Start End Blocks Id System
/dev/sde1 1 25024 25624560 83 Linux
Command (m for help): w
The partition table has been altered!
Calling ioctl() to re-read partition table.
Syncing disks.
Probe each host to pickup the changes via:
# partprobe
# fdisk -l
Configure ASMLib on each host via:
# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.
This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets ('[]'). Hittingwithout typing an
answer will keep that current value. Ctrl-C will abort.
Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Fix permissions of Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: [ OK ]
Loading module "oracleasm": [ OK ]
Mounting ASMlib driver filesystem: [ OK ]
Scanning system for ASM disks: [ OK ]
On one node as root, create the ASM Volumes via:
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk "/dev/sdb1" as an ASM disk: [ OK ]
# /etc/init.d/oracleasm createdisk VOL2 /dev/sde1
Marking disk "/dev/sde1" as an ASM disk: [ OK ]
On all other nodes, scan to recognize the changes via:
# /etc/init.d/oracleasm scandisks
Scanning system for ASM disks: [ OK ]
# /etc/init.d/oracleasm listdisks
VOL1
VOL2
Create the ASM Instances
Create directories for the clustered ASM instance on shared storage. In my case, I will put it on the same OCFS2 volumes hosting the voting and cluster registry files for CRS.
# mkdir -p /u02/oracle/crs/asm
# mkdir -p /u02/oracle/crs/asm/bdump
# mkdir -p /u02/oracle/crs/asm/cdump
# mkdir -p /u02/oracle/crs/asm/udump
# chown -R oracle:dba /u02/oracle/crs/asm
Create the initial ASM instance parameter file on the shared storage:
# touch /u02/oracle/crs/asm/init+ASM.ora
# vi /u02/oracle/crs/asm/init+ASM.ora
init+ASM.ora
*.asm_diskgroups='DATA','BACKUPS'
*.asm_diskstring='/dev/oracleasm/disks/*'
*.background_dump_dest='/u02/oracle/crs/asm/bdump'
*.cluster_database=true
*.core_dump_dest='/u02/oracle/crs/asm/cdump'
+ASM2.instance_number=2
+ASM1.instance_number=1
*.instance_type='asm'
*.large_pool_size=20M
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/u02/oracle/crs/asm/udump'
Link to the parameter file from each host:
# su - oracle
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/init+ASM.ora init+ASM1.ora
# su - oracle
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/init+ASM.ora init+ASM2.ora
Create the passwordfile on shared storage and link to it from each ASM instance:
# orapwd file=/u02/oracle/crs/asm/orapw+ASM password={password}Link to the passwordfile on each node:
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/orapw+ASM orapw+ASM1
# cd $ORACLE_HOME/dbs
# ln -s /u02/oracle/crs/asm/orapw+ASM orapw+ASM2
Update /etc/oratab on each node:
# echo "+ASM1:/u01/app/oracle/product/10.2:N" >> /etc/oratab
# echo "+ASM2:/u01/app/oracle/product/10.2:N" >> /etc/oratab
Create the ASM Diskgroups:
# export ORACLE_SID=+ASM1
# . oraenv
# sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 16:52:56 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ASM instance started
Total System Global Area 100663296 bytes
Fixed Size 1260312 bytes
Variable Size 74237160 bytes
ASM Cache 25165824 bytes
ORA-15032: not all alterations performed
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"
ORA-15063: ASM discovered an insufficient number of disks for diskgroup
"BACKUPS"
SQL> create diskgroup DATA external redundancy disk '/dev/oracleasm/disks/VOL1';
Diskgroup created.
SQL> create diskgroup BACKUPS external redundancy disk '/dev/oracleasm/disks/VOL2';
Diskgroup created.
Now that you have confirmed that the ASM instance is functional, create the spfile from pfile:
SQL> create spfile='/u02/oracle/crs/asm/spfile+ASM.ora' from pfile;
File created.
Update each node to reflect use of the spfile:
# cd $ORACLE_HOME/dbs
# vi init+ASM1.ora
init+ASM1.ora
spfile='/u02/oracle/crs/asm/spfile+ASM.ora'
# cd $ORACLE_HOME/dbs
# vi init+ASM2.ora
init+ASM2.ora
spfile='/u02/oracle/crs/asm/spfile+ASM.ora'
Register the clustered ASM instance with srvctl
# srvctl add asm -n rac1 -i +ASM1 -o $ORACLE_HOME
# srvctl add asm -n rac2 -i +ASM2 -o $ORACLE_HOME
# srvctl enable asm -n rac1 -i +ASM1
# srvctl enable asm -n rac2 -i +ASM2
Start and verify ASM instances:
# srvctl start asm -n rac1
# srvctl start asm -n rac2
# srvctl status asm -n rac2
ASM instance +ASM2 is running on node rac2.
# srvctl status asm -n rac1
ASM instance +ASM1 is running on node rac1.
Prepare to migrate the database in question to use ASM as storage
Query the names of the database files:
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u02/oracle/ora1/em/system01.dbf
/u02/oracle/ora1/em/undotbs01.dbf
/u02/oracle/ora1/em/sysaux01.dbf
/u02/oracle/ora1/em/users01.dbf
/u02/oracle/ora1/em/example01.dbf
/u02/oracle/ora1/em/undotbs02.dbf
/u02/oracle/ora2/em/mgmt.dbf
/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
8 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
/u02/oracle/ora1/em/control01.ctl
/u02/oracle/ora2/em/control02.ctl
SQL> select * from gv$logfile;
INST_ID GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- ------- ----------------------------------- ---
1 2 ONLINE /u02/oracle/ora1/em/redo_1_2a.log NO
1 2 ONLINE /u02/oracle/ora2/em/redo_1_2b.log NO
1 1 ONLINE /u02/oracle/ora1/em/redo_1_1a.log NO
1 1 ONLINE /u02/oracle/ora2/em/redo_1_1b.log NO
1 3 ONLINE /u02/oracle/ora1/em/redo_2_3a.log NO
1 3 ONLINE /u02/oracle/ora2/em/redo_2_3b.log NO
1 4 ONLINE /u02/oracle/ora1/em/redo_2_4a.log NO
1 4 ONLINE /u02/oracle/ora2/em/redo_2_4b.log NO
2 2 ONLINE /u02/oracle/ora1/em/redo_1_2a.log NO
2 2 ONLINE /u02/oracle/ora2/em/redo_1_2b.log NO
2 1 ONLINE /u02/oracle/ora1/em/redo_1_1a.log NO
INST_ID GROUP# STATUS TYPE MEMBER IS_
---------- ---------- ------- ------- ----------------------------------- ---
2 1 ONLINE /u02/oracle/ora2/em/redo_1_1b.log NO
2 3 ONLINE /u02/oracle/ora1/em/redo_2_3a.log NO
2 3 ONLINE /u02/oracle/ora2/em/redo_2_3b.log NO
2 4 ONLINE /u02/oracle/ora1/em/redo_2_4a.log NO
2 4 ONLINE /u02/oracle/ora2/em/redo_2_4b.log NO
16 rows selected.
Migrate the Database to ASM
Update the control_files parameter in preparation for migration:
$ srvctl stop database -d em
[oracle@rac1 dbs]$ export ORACLE_SID=em1
[oracle@rac1 dbs]$ . oraenv
[oracle@rac1 dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 17:38:38 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 243269900 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
SQL> alter system set control_files='+DATA/control.ctl' scope=spfile sid='*';
System altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
Migrate the controlfile:
# rman target=/
Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 25 17:40:41 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
RMAN> startup nomount;
Oracle instance started
Total System Global Area 268435456 bytes
Fixed Size 1261300 bytes
Variable Size 247464204 bytes
Database Buffers 12582912 bytes
Redo Buffers 7127040 bytes
RMAN> restore controlfile from '/u02/oracle/ora1/em/control01.ctl';
Starting restore at 25-MAY-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=em1 devtype=DISK
channel ORA_DISK_1: copied control file copy
output filename=+DATA/control.ctl
Finished restore at 25-MAY-08
Migrate the Datafiles:
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
RMAN> backup as copy database format '+DATA';
Starting backup at 25-MAY-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 instance=em1 devtype=DISK
channel ORA_DISK_1: starting datafile copy
input datafile fno=00007 name=/u02/oracle/ora2/em/mgmt.dbf
output filename=+DATA/em/datafile/mgmt_tablespace.257.655667175 tag=TAG20080525T174614 recid=10 stamp=655668164
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:16:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00001 name=/u02/oracle/ora1/em/system01.dbf
output filename=+DATA/em/datafile/system.258.655668171 tag=TAG20080525T174614 recid=11 stamp=655668229
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:05
channel ORA_DISK_1: starting datafile copy
input datafile fno=00003 name=/u02/oracle/ora1/em/sysaux01.dbf
output filename=+DATA/em/datafile/sysaux.259.655668235 tag=TAG20080525T174614 recid=12 stamp=655668262
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting datafile copy
input datafile fno=00006 name=/u02/oracle/ora1/em/undotbs02.dbf
output filename=+DATA/em/datafile/undotbs2.260.655668271 tag=TAG20080525T174614 recid=13 stamp=655668293
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile fno=00002 name=/u02/oracle/ora1/em/undotbs01.dbf
output filename=+DATA/em/datafile/undotbs1.261.655668297 tag=TAG20080525T174614 recid=14 stamp=655668309
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00005 name=/u02/oracle/ora1/em/example01.dbf
output filename=+DATA/em/datafile/example.262.655668311 tag=TAG20080525T174614 recid=15 stamp=655668322
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00008 name=/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
output filename=+DATA/em/datafile/mgmt_ecm_depot_ts.263.655668327 tag=TAG20080525T174614 recid=16 stamp=655668337
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile fno=00004 name=/u02/oracle/ora1/em/users01.dbf
output filename=+DATA/em/datafile/users.264.655668341 tag=TAG20080525T174614 recid=17 stamp=655668352
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
copying current control file
output filename=+DATA/em/controlfile/backup.265.655668357 tag=TAG20080525T174614 recid=18 stamp=655668358
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 25-MAY-08
channel ORA_DISK_1: finished piece 1 at 25-MAY-08
piece handle=+DATA/em/backupset/2008_05_25/nnsnf0_tag20080525t174614_0.266.655668361 tag=TAG20080525T174614 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 25-MAY-08
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DATA/em/datafile/system.258.655668171"
datafile 2 switched to datafile copy "+DATA/em/datafile/undotbs1.261.655668297"
datafile 3 switched to datafile copy "+DATA/em/datafile/sysaux.259.655668235"
datafile 4 switched to datafile copy "+DATA/em/datafile/users.264.655668341"
datafile 5 switched to datafile copy "+DATA/em/datafile/example.262.655668311"
datafile 6 switched to datafile copy "+DATA/em/datafile/undotbs2.260.655668271"
datafile 7 switched to datafile copy "+DATA/em/datafile/mgmt_tablespace.257.655667175"
datafile 8 switched to datafile copy "+DATA/em/datafile/mgmt_ecm_depot_ts.263.655668327"
Open the Database by exiting RMAN and then issuing the following via SQL*Plus:
# SQL> alter database open;
Database altered.
Startup the other instances:
# srvctl start instance -d em -i em2
Create a new Temporary Tablespace on the ASM Diskgroup:
SQL> CREATE TEMPORARY TABLESPACE TEMPTBS TEMPFILE '+DATA' SIZE 200M;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPTBS;
Database altered.
SQL> DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Create new Online Redo Log Groups:
SQL> alter system archive log all;
alter system archive log all
*
ERROR at line 1:
ORA-00271: there are no logs that need archiving
SQL> alter system archive log stop;
System altered.
Create ASM Directories
# export ORACLE_SID=+ASM1
# . oraenv
# sqlplus "/ as sysdba"
SQL*Plus: Release 10.2.0.3.0 - Production on Sun May 25 18:32:31 2008
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> alter diskgroup DATA add directory '+DATA/em/log1';
Diskgroup altered.
SQL> alter diskgroup DATA add directory '+DATA/em/log2';
Diskgroup altered.
Create New Online Redo Log Groups for each public thread before dropping the old ones
SQL> alter database add logfile thread 1 group 5
2 ('+DATA/em/log1/log_1_5a.log','+DATA/em/log1/log_1_5b.log') size 10M;
Database altered.
SQL> alter database add logfile thread 1 group 6
2 ('+DATA/em/log1/log_1_6a.log','+DATA/em/log1/log_1_6b.log') size 10M;
Database altered.
SQL> alter database add logfile thread 1 group 7
2 ('+DATA/em/log1/log_1_7a.log','+DATA/em/log1/log_1_7b.log') size 10M;
Database altered.
SQL> alter database add logfile thread 2 group 8
2 ('+DATA/em/log2/log_2_8a.log','+DATA/em/log2/log_2_8b.log') size 10M;
Database altered.
SQL> alter database add logfile thread 2 group 9
2 ('+DATA/em/log2/log_2_9a.log','+DATA/em/log2/log_2_9b.log') size 10M;
Database altered.
SQL> alter database add logfile thread 2 group 10
2 ('+DATA/em/log2/log_2_10a.log','+DATA/em/log2/log_2_10b.log') size 10M;
Database altered.
Drop the Old Online Redo Log Groups using the traditional process, for example:
SQL> alter system switch logfile;
System altered.
SQL> select group#, thread#, status from v$log;
GROUP# THREAD# STATUS
---------- ---------- ----------------
1 1 INACTIVE
5 1 ACTIVE
6 1 ACTIVE
7 1 CURRENT
8 2 CURRENT
9 2 ACTIVE
10 2 ACTIVE
7 rows selected.
SQL> alter database drop logfile group 1;
Database altered.
Optionally, drop the old datafile images:
# rman target=/
Recovery Manager: Release 10.2.0.3.0 - Production on Sun May 25 19:00:45 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: EM (DBID=1167076574)
RMAN> delete copy of database;
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=124 instance=em1 devtype=DISK
List of Datafile Copies
Key File S Completion Time Ckp SCN Ckp Time Name
------- ---- - --------------- ---------- --------------- ----
19 1 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/system01.dbf
20 2 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/undotbs01.dbf
21 3 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/sysaux01.dbf
22 4 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/users01.dbf
23 5 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/example01.dbf
24 6 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/undotbs02.dbf
25 7 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora2/em/mgmt.dbf
26 8 A 25-MAY-08 2754400 25-MAY-08 /u02/oracle/ora1/em/mgmt_ecm_depot1.dbf
Do you really want to delete the above objects (enter YES or NO)? YES
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/system01.dbf recid=19 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/undotbs01.dbf recid=20 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/sysaux01.dbf recid=21 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/users01.dbf recid=22 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/example01.dbf recid=23 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/undotbs02.dbf recid=24 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora2/em/mgmt.dbf recid=25 stamp=655668498
deleted datafile copy
datafile copy filename=/u02/oracle/ora1/em/mgmt_ecm_depot1.dbf recid=26 stamp=655668498
Deleted 8 objects
Migrate the Archive Log Destinations to ASM:
SQL> alter system set log_archive_dest_1='LOCATION=+BACKUPS' scope=BOTH sid='*';
System altered.
SQL> alter system archive log start;
System altered.
SQL> alter system archive log current;
System altered.
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +BACKUPS
Oldest online log sequence 342
Next log sequence to archive 344
Current log sequence 344
At this point, you should be able to query an ASM instance and find archivelogs in the BACKUPS diskgroup:
SQL> select count(*) from v$asm_file where type='ARCHIVELOG' and group_number = (select group_number from v$asm_diskgroup where name='BACKUPS');
COUNT(*)
----------
4
How to Backup and Restore via RMAN without a Recovery Catalog
Posted at Sunday, February 24, 2008
Although an RMAN repository is critical for most enterprises nowadays, there are circumstances in which you may want to backup and restore via RMAN utilizing a database's controlfile (i.e. in lieu of a recovery catalog).
It is possible to backup and restore your database via RMAN without a recovery catalog and to do so in such a way that you can recover from a loss of all database files; the following is an example in which I use an 11g (11.1.0.6) database called nf (short for new features):
Backup the ENTIRE database to RMAN backuppieces using your desired device type:
backup_nf.bsh
Of course, you will need to make small changes to the aforementioned to suit your environment. Once you have done so, run the script.
Afterwards, the backup_nf.log should have all the details necessary to troubleshoot, etc.
backup_nf.log
Now that I have a complete backup of the database - datafiles, archivelogs, spfile, and controlfile - I am ready to drop my database and prove that I can restore from a complete loss of all files.
Drop the Database:
delete_nf.bsh
After editing for your environment, run the script.
Now I am ready to restore and recover my database using only the backuppieces resulting from the aforementioned backup.
Restore and recover the Database:
Identify the backuppiece that has a copy of the spfile and controlfile via
The complete path to this backuppiece will need to be passed to the restore script.
Edit the restore script for your environment:
restore_nf.bsh
After editing as appropriate, run the script.
Afterwards, the restore_nf.log should have all the details necessary to troubleshoot, etc.
restore_nf.log
The files for this post - including complete log files - can be found here: rman_nf.tar
It is possible to backup and restore your database via RMAN without a recovery catalog and to do so in such a way that you can recover from a loss of all database files; the following is an example in which I use an 11g (11.1.0.6) database called nf (short for new features):
Backup the ENTIRE database to RMAN backuppieces using your desired device type:
backup_nf.bsh
#!/bin/bash
export ORACLE_SID=nf;
. oraenv;
export TNS_ADMIN=$ORACLE_HOME/network/admin;
export BACKUP_LOC=/u03/app/oracle/orabackup;
$ORACLE_HOME/bin/rman target=/ nocatalog <<EOF
configure backup optimization on;
configure default device type to disk;
crosscheck backup;
crosscheck archivelog all;
delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;
configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '$BACKUP_LOC/%d_%F_ctl.bckp';
backup full database format '$BACKUP_LOC/%d_%U.bckp' tag rman_nocat_${ORACLE_SID} plus archivelog format '$BACKUP_LOC/%d_%U.bckp' delete input tag rman_nocat_${ORACLE_SID} ;
list recoverable backup;
EOF
Of course, you will need to make small changes to the aforementioned to suit your environment. Once you have done so, run the script.
./backup_nf.bsh > backup_nf.log 2>&1 &
Afterwards, the backup_nf.log should have all the details necessary to troubleshoot, etc.
backup_nf.log
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 23 22:43:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: NF (DBID=2018903642)
using target database control file instead of recovery catalog
RMAN>
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN>
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored
RMAN>
RMAN>
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp RECID=56 STAMP=647476722
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp RECID=57 STAMP=647476933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp RECID=58 STAMP=647476979
Crosschecked 3 objects
RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
specification does not match any archived log in the recovery catalog
RMAN>
RMAN>
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found
RMAN>
using channel ORA_DISK_1
List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
56 47 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp
57 48 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp
58 49 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp RECID=56 STAMP=647476722
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp RECID=57 STAMP=647476933
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp RECID=58 STAMP=647476979
Deleted 3 EXPIRED objects
RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
specification does not match any archived log in the recovery catalog
RMAN>
RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/orabackup/%d_%F_ctl.bckp';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/orabackup/%d_%F_ctl.bckp';
new RMAN configuration parameters are successfully stored
RMAN>
RMAN>
Starting backup at 23-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=746 STAMP=647477049
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_38j9fdpp_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_7_647475391.arc RECID=746 STAMP=647477049
Finished backup at 23-FEB-08
Starting backup at 23-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u03/app/oracle/oradata/nf/undotbs01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/nf/sysaux01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/nf/users01.dbf
input datafile file number=00001 name=/u03/app/oracle/oradata/nf/system01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/nf/fba_tbs01.dbf
input datafile file number=00007 name=/u03/app/oracle/oradata/nf/enc_tbs02.dbf
input datafile file number=00006 name=/u03/app/oracle/oradata/nf/enc_tbs101.dbf
input datafile file number=00010 name=/u03/app/oracle/oradata/nf/users02.dbf
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:35
Finished backup at 23-FEB-08
Starting backup at 23-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=747 STAMP=647477793
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_8_647475391.arc RECID=747 STAMP=647477793
Finished backup at 23-FEB-08
Starting Control File and SPFILE Autobackup at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp comment=NONE
Finished Control File and SPFILE Autobackup at 23-FEB-08
RMAN>
RMAN>
List of Backup Sets
===================
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
50 35.00K DISK 00:00:11 23-FEB-08
BP Key: 59 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_38j9fdpp_1_1.bckp
List of Archived Logs in backup set 50
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 8095380 23-FEB-08 8095522 23-FEB-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 2.56G DISK 00:11:48 23-FEB-08
BP Key: 60 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp
List of Datafiles in backup set 51
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/system01.dbf
2 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/sysaux01.dbf
3 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/undotbs01.dbf
4 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/users01.dbf
5 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/fba_tbs01.dbf
6 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/enc_tbs101.dbf
7 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/enc_tbs02.dbf
10 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/users02.dbf
BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
52 834.00K DISK 00:00:12 23-FEB-08
BP Key: 61 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp
List of Archived Logs in backup set 52
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8 8095522 23-FEB-08 8096603 23-FEB-08
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 9.73M DISK 00:00:13 23-FEB-08
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20080223T225647
Piece Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
SPFILE Included: Modification time: 23-FEB-08
SPFILE db_unique_name: NF
Control File Included: Ckp SCN: 8096623 Ckp time: 23-FEB-08
RMAN>
Recovery Manager complete.
Now that I have a complete backup of the database - datafiles, archivelogs, spfile, and controlfile - I am ready to drop my database and prove that I can restore from a complete loss of all files.
Drop the Database:
delete_nf.bsh
#!/bin/bash
export ORACLE_SID=nf;
. oraenv;
sqlplus "/ as sysdba" <<EOF
shutdown abort;
EOF
rm -f /u03/app/oracle/oradata/nf/*.dbf*
rm -f /u03/app/oracle/oradata/nf/*.log*
rm -f /u03/app/oracle/oradata/nf/*.ctl*
rm -f /u03/app/oracle/oradata/nf/arch/*
rm -f $ORACLE_HOME/dbs/*${ORACLE_SID}*
After editing for your environment, run the script.
./delete_nf.bsh
Now I am ready to restore and recover my database using only the backuppieces resulting from the aforementioned backup.
Restore and recover the Database:
Identify the backuppiece that has a copy of the spfile and controlfile via
ls -lart /u03/app/oracle/orabackup/*ctl*
The complete path to this backuppiece will need to be passed to the restore script.
Edit the restore script for your environment:
restore_nf.bsh
#!/bin/bash
if [ "$1" == "" ]; then
echo "usage ${0} path to backuppiece with controlfile backup";
exit 1;
fi
CTL_PIECE=${1} ;
echo ${CTL_PIECE} ;
BACKUP_LOC=/u03/app/oracle/orabackup;
export ORACLE_SID=nf;
. oraenv;
export TNS_ADMIN=$ORACLE_HOME/network/admin ;
$ORACLE_HOME/bin/rman nocatalog <<EOF
connect target /;
startup force nomount;
restore spfile from '${CTL_PIECE}';
shutdown immediate;
startup nomount;
restore controlfile from '${CTL_PIECE}';
shutdown immediate;
startup mount;
catalog start with '${BACKUP_LOC}' noprompt;
restore database;
recover database;
sql 'alter database open resetlogs';
EOF
orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=password ignorecase=Y ;
sqlplus "/ as sysdba" <<EOF
alter tablespace temp add tempfile '/u03/app/oracle/oradata/${ORACLE_SID}/temp01.dbf' size 500M reuse;
EOF
After editing as appropriate, run the script.
./restore_nf.bsh /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp >restore_nf.log 2>&1 &
Afterwards, the restore_nf.log should have all the details necessary to troubleshoot, etc.
restore_nf.log
/u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle
Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 23 23:03:07 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
RMAN>
connected to target database (not started)
RMAN>
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u03/app/oracle/product/db/11.1.0.6/dbs/initnf.ora'
starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started
Total System Global Area 159019008 bytes
Fixed Size 1298584 bytes
Variable Size 67112808 bytes
Database Buffers 83886080 bytes
Redo Buffers 6721536 bytes
RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK
channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 23-FEB-08
RMAN>
Oracle instance shut down
RMAN>
connected to target database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 381683840 bytes
Database Buffers 33554432 bytes
Redo Buffers 6131712 bytes
RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u03/app/oracle/oradata/nf/control01.ctl
output file name=/u03/app/oracle/oradata/nf/control02.ctl
output file name=/u03/app/oracle/oradata/nf/control03.ctl
Finished restore at 23-FEB-08
RMAN>
Oracle instance shut down
RMAN>
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 381683840 bytes
Database Buffers 33554432 bytes
Redo Buffers 6131712 bytes
RMAN>
searching for all files that match the pattern /u03/app/oracle/orabackup
List of Files Unknown to the Database
=====================================
File Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/nf/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/nf/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/nf/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/nf/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/nf/fba_tbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u03/app/oracle/oradata/nf/enc_tbs101.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u03/app/oracle/oradata/nf/enc_tbs02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u03/app/oracle/oradata/nf/users02.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp
channel ORA_DISK_1: piece handle=/u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp tag=RMAN_NOCAT_NF
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:37
Finished restore at 23-FEB-08
RMAN>
Starting recover at 23-FEB-08
using channel ORA_DISK_1
starting media recovery
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp
channel ORA_DISK_1: piece handle=/u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp tag=RMAN_NOCAT_NF
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_8_647475391.arc thread=1 sequence=8
unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/23/2008 23:09:23
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 8096603
RMAN>
sql statement: alter database open resetlogs
RMAN>
Recovery Manager complete.
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Feb 23 23:11:46 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> alter tablespace temp add tempfile '/u03/app/oracle/oradata/nf/temp01.dbf' size 500M reuse
*
ERROR at line 1:
ORA-01537: cannot add file '/u03/app/oracle/oradata/nf/temp01.dbf' - file
already part of database
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
The files for this post - including complete log files - can be found here: rman_nf.tar
Labels: Backup and Recovery, RMAN
New in 11g: Active Database Duplication - Example
Posted at Monday, January 14, 2008
Another long-overdue feature, introduced in 11g, is the ability to clone/duplicate a database (for standby or otherwise) without the use of an RMAN backup. In previous versions, duplications were based upon backups of the TARGET database in question. Now, you can duplicate a database (over the network), without an RMAN backup.
Here is a complete example of duplicating an 11g database on the same machine (Linux) using the new, aforementioned active database duplication:
1. Create any needed directories
2. Create hard-coded network entries for your new, auxiliary database
Addition to SID_LIST_LISTENER in listener.ora:
Addition to tnsames.ora:
3. Create a passwordfile for new, auxiliary database (this MUST match the TARGET)
4. Create 'dummy' parameter file for auxiliary instance
5. Add relevant entry to oratab on non-Windows environments
6. 'Nomount' the auxiliary instance in preparation for duplication
7. Duplicate the database using RMAN
If successful, you should see output similar to the following:
Here is a complete example of duplicating an 11g database on the same machine (Linux) using the new, aforementioned active database duplication:
1. Create any needed directories
mkdir /u03/app/oracle/oradata/duptest
mkdir /u03/app/oracle/admin/duptest/adump
2. Create hard-coded network entries for your new, auxiliary database
Addition to SID_LIST_LISTENER in listener.ora:
(SID_DESC =
(GLOBAL_DBNAME = duptest.colestock.test)
(ORACLE_HOME = /u03/app/oracle/product/db/11.1.0.6)
(SID_NAME = duptest)
)
Addition to tnsames.ora:
DUPTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2.colestock.test)(PORT = 1523))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = duptest.colestock.test)
)
)
3. Create a passwordfile for new, auxiliary database (this MUST match the TARGET)
cd $ORACLE_HOME/dbs
orapwd file=orapwduptest password=password
4. Create 'dummy' parameter file for auxiliary instance
[/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ more initduptest.ora
db_name=duptest
control_files=/u03/app/oracle/oradata/duptest/control01.ctl, /u03/app/oracle/ora
data/duptest/control02.ctl, /u03/app/oracle/oradata/duptest/control03.ctl
5. Add relevant entry to oratab on non-Windows environments
[/u03/app/oracle/product/db/11.1.0.6/dbs nf@rac2]$ grep duptest /etc/oratab
duptest:/u03/app/oracle/product/db/11.1.0.6:N
6. 'Nomount' the auxiliary instance in preparation for duplication
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ export ORACLE_SID=duptest
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ . oraenv
[/u03/app/oracle/product/db/11.1.0.6/dbs duptest@rac2] $ sqlplus "/ as sysdba"
SQL*Plus: Release 11.1.0.6.0 - Production on Mon Jan 14 11:03:52 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1298472 bytes
Variable Size 92278744 bytes
Database Buffers 50331648 bytes
Redo Buffers 6758400 bytes
SQL> exit
7. Duplicate the database using RMAN
rman target=sys/password@nf auxiliary=sys/password@duptest
duplicate target database to duptest
2> from active database
3> db_file_name_convert '/nf/','/duptest/'
4> spfile
5> parameter_value_convert '/nf/','/duptest/'
6> set log_file_name_convert '/nf/','/duptest/'
7> set log_archive_dest_1='';
If successful, you should see output similar to the following:
Starting Duplicate Db at 14-JAN-08
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=98 device type=DISK
contents of Memory Script:
{
backup as copy reuse
file '/u03/app/oracle/product/db/11.1.0.6/dbs/spfilenf.ora' auxiliary format
'/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora' ;
sql clone "alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora''";
}
executing Memory Script
Starting backup at 14-JAN-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=116 device type=DISK
Finished backup at 14-JAN-08
sql statement: alter system set spfile= ''/u03/app/oracle/product/db/11.1.0.6/dbs/spfileduptest.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPTEST'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u03/app/oracle/admin/duptest/adump'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/app/oracle/oradata/duptest/control01.ctl'', ''/u03/app/oracle/oradata/duptest/control02.ctl'', ''/u03/app/oracle/oradata/duptest/control03.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/nf/'', ''/duptest/'' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
'''' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPTEST'' comment= ''duplicate'' scope=spfile
sql statement: alter system set audit_file_dest = ''/u03/app/oracle/admin/duptest/adump'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u03/app/oracle/oradata/duptest/control01.ctl'', ''/u03/app/oracle/oradata/duptest/control02.ctl'', ''/u03/app/oracle/oradata/duptest/control03.ctl'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/nf/'', ''/duptest/'' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = '''' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 406849664 bytes
Database Buffers 8388608 bytes
Redo Buffers 6131712 bytes
contents of Memory Script:
{
set newname for datafile 1 to
"/u03/app/oracle/oradata/duptest/system01.dbf";
set newname for datafile 2 to
"/u03/app/oracle/oradata/duptest/sysaux01.dbf";
set newname for datafile 3 to
"/u03/app/oracle/oradata/duptest/undotbs01.dbf";
set newname for datafile 4 to
"/u03/app/oracle/oradata/duptest/users01.dbf";
set newname for datafile 5 to
"/u03/app/oracle/oradata/duptest/flashback_data01.dbf";
set newname for datafile 6 to
"/u03/app/oracle/oradata/duptest/ts201.dbf";
set newname for datafile 7 to
"/u03/app/oracle/oradata/duptest/ts301.dbf";
set newname for datafile 8 to
"/u03/app/oracle/oradata/duptest/ts401.dbf";
backup as copy reuse
datafile 1 auxiliary format
"/u03/app/oracle/oradata/duptest/system01.dbf" datafile
2 auxiliary format
"/u03/app/oracle/oradata/duptest/sysaux01.dbf" datafile
3 auxiliary format
"/u03/app/oracle/oradata/duptest/undotbs01.dbf" datafile
4 auxiliary format
"/u03/app/oracle/oradata/duptest/users01.dbf" datafile
5 auxiliary format
"/u03/app/oracle/oradata/duptest/flashback_data01.dbf" datafile
6 auxiliary format
"/u03/app/oracle/oradata/duptest/ts201.dbf" datafile
7 auxiliary format
"/u03/app/oracle/oradata/duptest/ts301.dbf" datafile
8 auxiliary format
"/u03/app/oracle/oradata/duptest/ts401.dbf" ;
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting backup at 14-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u03/app/oracle/oradata/nf/undotbs01.dbf
output file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:28
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u03/app/oracle/oradata/nf/users01.dbf
output file name=/u03/app/oracle/oradata/duptest/users01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:37
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u03/app/oracle/oradata/nf/system01.dbf
output file name=/u03/app/oracle/oradata/duptest/system01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u03/app/oracle/oradata/nf/sysaux01.dbf
output file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:57
channel ORA_DISK_1: starting datafile copy
input datafile file number=00006 name=/u03/app/oracle/oradata/nf/ts201.dbf
output file name=/u03/app/oracle/oradata/duptest/ts201.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=/u03/app/oracle/oradata/nf/flashback_data01.dbf
output file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/u03/app/oracle/oradata/nf/ts301.dbf
output file name=/u03/app/oracle/oradata/duptest/ts301.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/u03/app/oracle/oradata/nf/ts401.dbf
output file name=/u03/app/oracle/oradata/duptest/ts401.dbf tag=TAG20080114T115711 RECID=0 STAMP=0
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
Finished backup at 14-JAN-08
sql statement: alter system archive log current
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/app/oracle/oradata/duptest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/app/oracle/oradata/duptest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/app/oracle/oradata/duptest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oracle/oradata/duptest/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
backup as copy reuse
archivelog like "/u03/app/oracle/oradata/nf/arch/nf_1_277_635081437.arc" auxiliary format
"/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc" ;
catalog clone archivelog "/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc";
switch clone datafile all;
}
executing Memory Script
Starting backup at 14-JAN-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log copy
input archived log thread=1 sequence=277 RECID=329 STAMP=643982787
output file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc RECID=0 STAMP=0
channel ORA_DISK_1: archived log copy complete, elapsed time: 00:00:01
Finished backup at 14-JAN-08
cataloged archived log
archived log file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc RECID=1 STAMP=643982804
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=643982804 file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts201.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts301.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=643982805 file name=/u03/app/oracle/oradata/duptest/ts401.dbf
contents of Memory Script:
{
set until scn 3167561;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 14-JAN-08
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=151 device type=DISK
starting media recovery
archived log for thread 1 with sequence 277 is already on disk as file /u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc
archived log file name=/u03/app/oracle/product/db/11.1.0.6/dbs/archnf_1_277_635081437.arc thread=1 sequence=277
media recovery complete, elapsed time: 00:00:03
Finished recover at 14-JAN-08
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount ;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 422670336 bytes
Fixed Size 1300352 bytes
Variable Size 406849664 bytes
Database Buffers 8388608 bytes
Redo Buffers 6131712 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUPTEST" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/app/oracle/oradata/duptest/redo01.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/app/oracle/oradata/duptest/redo02.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/app/oracle/oradata/duptest/redo03.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/app/oracle/oradata/duptest/system01.dbf'
CHARACTER SET WE8MSWIN1252
contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/app/oracle/oradata/duptest/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/sysaux01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/undotbs01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/users01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/flashback_data01.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts201.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts301.dbf";
catalog clone datafilecopy "/u03/app/oracle/oradata/duptest/ts401.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u03/app/oracle/oradata/duptest/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf RECID=1 STAMP=643982828
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf RECID=2 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/users01.dbf RECID=3 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf RECID=4 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts201.dbf RECID=5 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts301.dbf RECID=6 STAMP=643982829
cataloged datafile copy
datafile copy file name=/u03/app/oracle/oradata/duptest/ts401.dbf RECID=7 STAMP=643982829
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=643982828 file name=/u03/app/oracle/oradata/duptest/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/flashback_data01.dbf
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts201.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts301.dbf
datafile 8 switched to datafile copy
input datafile copy RECID=7 STAMP=643982829 file name=/u03/app/oracle/oradata/duptest/ts401.dbf
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 14-JAN-08
_507.png)
Subscribe
