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
Problems with ASM on 10.1.0.2
Posted at Saturday, June 11, 2005
There is a known bug when installing Oracle Database 10g version 10.1.0.2 on Windows platforms: Bug #3617107. Specifically, the OracleCSService doesn't install correctly (it will show as 'Starting'), when installing the software as a non-local user (i.e. using an Active Directory account).
To get the software installed correctly, you can reference the following Metalink notes - 276801.1 and 619166.999 - or you can insure that the 'ORA_DBA' group is added to local 'Administrator' group and that the local 'System' user is added to the local 'Administrator' group before running the installer executable.
Once you have a good software installation, you can either create the ASM instance using the dbca or you can create manually:
On Windows platforms, disks need to be 'stamped' using one of the ASMTool executables, before you can add them to a diskgroup, etc. The following document has a good description of this process on page 26: Dell Oracle Deployment White Paper.
To get the software installed correctly, you can reference the following Metalink notes - 276801.1 and 619166.999 - or you can insure that the 'ORA_DBA' group is added to local 'Administrator' group and that the local 'System' user is added to the local 'Administrator' group before running the installer executable.
Once you have a good software installation, you can either create the ASM instance using the dbca or you can create manually:
C:\Documents and Settings\oracle>set ORACLE_SID=+ASM
C:\Documents and Settings\oracle>set ORACLE_HOME=c:\apps\oracle\product\10g
C:\apps\oracle\product\10G\BIN>oradim -NEW -ASMSID +ASM -SYSPWD yomama
Instance created.
C:\apps\oracle\product\10G\BIN>cd ..\database
C:\apps\oracle\product\10G\database>notepad.exe INIT+ASM.ORA
C:\apps\oracle\product\10G\database>dir
Directory of C:\apps\oracle\product\10G\database
06/07/2005 02:53p <DIR> .
06/07/2005 02:53p <DIR> ..
06/07/2005 12:41p <DIR> archive
06/07/2005 02:54p 55 INIT+ASM.ORA
06/07/2005 12:32p 31,744 oradba.exe
06/07/2005 02:53p 2,560 PWD+ASM.ORA
3 File(s) 34,359 bytes
3 Dir(s) 6,115,950,592 bytes free
C:\apps\oracle\product\10G\database>sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.2.0 - Production on Tue Jun 7 14:55:06 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ASM instance started
Total System Global Area 100663296 bytes
Fixed Size 787648 bytes
Variable Size 99875648 bytes
Database Buffers 0 bytes
Redo Buffers 0 bytes
ORA-15110: no diskgroups mounted
On Windows platforms, disks need to be 'stamped' using one of the ASMTool executables, before you can add them to a diskgroup, etc. The following document has a good description of this process on page 26: Dell Oracle Deployment White Paper.
_507.png)
Subscribe
