(e.g. from production to test environment).
– Target cluster name : cluster01.example.com
– Target cluster SCAN name : cluster01-scan.cluster01.example.com
– No. of nodes in the target cluster : host01, host02, host03
– Name of the database after conversion : newdb
On the source Server
———————-
– Start the database in READ ONLY mode
– Verify the database is ready for migration
– Make a folder to hold converted datafiles
– Use RMAN Convert command to create
. Transport script
. Initialization parameter file
. Converted Datafiles———————
On the target server
———————
– Create appropriate folders
– Transfer above files to the proper location on the target system.
– Edit initialization parameter file.
– Edit the transport script transport.sql
– Make an entry for the database in /etc/oratab
– Run transport script to create a single instance database on the target server.
– Register the database with clusterware
– Put the database in archivelog mode if not aLREADY
– Use rconfig to convert the single instance database to RAC database
– Create entries for the database in tnsnames.ora
Implementation:
———————-
On the source Server
———————
– Check prerequisites
– check the platform name of current database —
SQL>SELECT PLATFORM_NAME FROM V$DATABASE;
PLATFORM_NAME
——————————
Linux IA (32-bit)
— Start the database in READ ONLY mode
[oracle@host01]$srvctl stop database -d orcl
SQL> startup mount; alter database open read only;
— Verify the database is ready for migration
SQL> SET SERVEROUTPUT ON;
DECLARE
db_ready BOOLEAN;
BEGIN
db_ready :=
DBMS_TDB.CHECK_DB('Linux IA (32-bit)',DBMS_TDB.SKIP_READONLY);
if db_ready
then
dbms_output.put_line('YES your database can be transported. ');
else
dbms_output.put_line('NO your database can not be transported. ');
end if;
end;
/
– Make a folder to hold converted datafiles
[oracle@host01]$mkdir /home/oracle/convertdb
- Use RMAN Convert Database command to create
. Transport script
. Inittalization parameter file
. Converted Datafiles
The CONVERT DATABASE command specified in the example creates
– a transport script named /home/oracle/convertdb/transport.sql,
– a PFILE initnewdb.ora in file /home/oracle/convertdb/newdb
– a copy of all data files in the =/home/oracle/convertdb/newdb directory in the format
of the target platform ‘Linux IA (32-bit)’
RMAN>CONVERT DATABASE NEW DATABASE 'newdb' TRANSPORT SCRIPT '/home/oracle/convertdb/transport.sql' TO PLATFORM 'Linux IA (32-bit)' FORMAT '/home/oracle/convertdb/newdb/%U';
Starting conversion at source at 23-FEB-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 instance=orcl1 device type=DISK
External table SH.SALES_TRANSACTIONS_EXT found in the database
Directory SYS.SS_OE_XMLDIR found in the database
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.SUBDIR found in the database
Directory SYS.MEDIA_DIR found in the database
Directory SYS.LOG_FILE_DIR found in the database
Directory SYS.DATA_FILE_DIR found in the database
Directory SYS.XMLDIR found in the database
Directory SYS.ORACLE_OCM_CONFIG_DIR found in the database
BFILE PM.PRINT_MEDIA found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00001 name=+DATA/orcl/datafile/system.256.808138599
converted datafile=/home/oracle/convertdb/newdb/data_D-ORCL_I-1335919524_TS-SYSTEM_FNO-1_16o2n0vb
.
.
.
.
converted datafile=/home/oracle/convertdb/newdb/data_D-ORCL_I-1335919524_TS-USERS_FNO-4_1co2n13i
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Edit init.ora file /home/oracle/convertdb/newdb/init_00o2n0vb_1_0.ora. This PFILE will be used to
create the database on the target platform
Run SQL script /home/oracle/convertdb/transport.sql on the target platform to create database
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished conversion at source at 23-FEB-13
———————
On the target server
———————
- Create appropriate folders
[oracle@host01]$mkdir -p /home/oracle/convertdb/newdb mkdir -p /u01/app/oracle/admin/newdb/adump
- Transfer above files to the proper location on the target system
. transport.sql to /home/oracle/convertdb folder
. init….ora and converted datafiles to /home/oracle/convertdb/newdb folder
– Edit initialization parameter file
[oracle@host01]$vi /home/oracle/convertdb/newdb/init_00o2n0vb_1_0.ora
# Please change the values of the following parameters:
# db_create_file_dest = “/home/oracle/convertdb/newdb/+DATA”
db_create_file_dest = “+DATA”
# db_recovery_file_dest = “/home/oracle/convertdb/newdb/+FRA”
db_recovery_file_dest = “+FRA”
db_recovery_file_dest_size= 4039114752
# remote_listener = “NEWDBcluster01-scan.cluster01.example.com:1521″
remote_listener = “cluster01-scan.cluster01.example.com:1521″
# cluster_database = TRUE
cluster_database = FALSE
# audit_file_dest = “/home/oracle/convertdb/newdb/adump”
audit_file_dest = “/u01/app/oracle/admin/newdb/adump”
– Edit the transport script transport.sql
[oracle@host01]$vi /home/oracle/convertdb/transport.sql
– Delete the following lines to create redo threads 2 and 3
— Create log files for threads other than thread one.
SQL>ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 50M BLOCKSIZE 512 REUSE, GROUP 4 SIZE 50M BLOCKSIZE 512 REUSE; ALTER DATABASE ADD LOGFILE THREAD 3 GROUP 5 SIZE 50M BLOCKSIZE 512 REUSE, GROUP 6 SIZE 50M BLOCKSIZE 512 REUSE;
- Make following entry for the database in /etc/oratab
newdb:/u01/app/oracle/product/11.2.0/db_1:N
- Run transport script to create a single instance database on the target server
[oracle@host01]$. oraenv - newdb sqlplus / as sysdba SQL>@/home/oracle/convertdb/transport.sql
– check that database has been transported —
[oracle@host01]$. oraenv - newdb sqlplus / as sysdba SQL>startup select * from hr.employees;
- Register the database with clusterware
– Check the database is currently not registered with clusterware
[oracle@host01 ~]$ srvctl status database -d newdb
PRCD-1120 : The resource for database newdb could not be found.
PRCR-1001 : Resource ora.newdb.db does not exist
[root@host01 trace]# crsctl stat res -t |grep newdb
— Register the database
[oracle@host01 ~]$ srvctl add database -d newdb -o $ORACLE_HOME -p /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilenewdb.ora -a DATA,FRA
– Check the database is now registered with clusterware
[root@host01 trace]# crsctl stat res -t |grep newdb
ora.newdb.db
— Check that the database has no configured instances as it is a single
instance database
[oracle@host01 ~]$ srvctl start database -d newdb
– Put the database in archivelog mode if not already
( needed to convert it to a RAC database)
SQL> shu immediate; startup mount; alter database archivelog; alter database open;
- Use rconfig to convert the single instance newdb database to a RAC database running
on three nodes host01, host02 and host03 with
. Datafiles on +DATA diskgroup
. Flash recovery area on +FRA diskgroup
— copy ConvertToRAC_AdminManaged.xml to another file my.xml
[oracle@host01]$cd $ORACLE_HOME/assistants/rconfig/sampleXMLs cp ConvertToRAC_AdminManaged.xml my.xml
— Edit my.xml and make following changes :
. Specify current OracleHome of non-rac database for SourceDBHome
. Specify OracleHome where the rac database should be configured. It can be same as
SourceDBHome
. Specify SID of non-rac database and credential. User with sysdba role is required to perform
conversion
. Specify the list of nodes that should have rac instances running for the Admin Managed
Cluster Database. LocalNode should be the first node in this nodelist.
. Instance Prefix tag is optional starting with 11.2. If left empty, it is derived from
db_unique_name
. Specify the type of storage to be used by rac database. Allowable values are CFS|ASM
. Specify Database Area Location to be configured for rac database.
. Specify Flash Recovery Area to be configured for rac database.
— Run rconfig to convert orcl from single instance database to 2 instance RAC database
host01$rconfig my.xml
Converting Database “NEWDB” to Cluster Database. Target Oracle Home:
/u01/app/oracle/product/11.2.0/dbhome_1. Database Role: PRIMARY.
Setting Data Files and Control Files
Adding Database Instances
Adding Redo Logs
Enabling threads for all Database Instances
Setting TEMP tablespace
Adding UNDO tablespaces
Adding Trace files
Setting Flash Recovery Area
Updating Oratab
Creating Password file(s)
Configuring Listeners
Configuring related CRS resources
Starting Cluster Database
Operation Succeeded
<Oracle_Home>
/u01/app/oracle/product/11.2.0/dbhome_1
— Check the log file for rconfig while conversion is going on
oracle@host01$ls -lrt $ORACLE_BASE/cfgtoollogs/rconfig/*.log
tailf <logfilename>
— check that the database has been converted successfully
host01$srvctl status database -d newdb
Instance newdb1 is running on node host01
Instance newdb2 is running on node host02
Instance newdb3 is running on node host03
[root@host01 trace]# srvctl config database -d newdb
Database unique name: NEWDB
Database name: NEWDB
Oracle home: /u01/app/oracle/product/11.2.0/dbhome_1
Oracle user: oracle
Spfile: +DATA/spfilenewdb.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: NEWDB
Database instances: newdb1,newdb2,newdb3
Disk Groups: DATA,FRA
Services:
Database is administrator managed
[root@host01 trace]# crsctl stat res -t
ora.newdb.db
1 ONLINE ONLINE host01 Open
2 ONLINE ONLINE host02 Open
3 ONLINE ONLINE host03 Open
- Create entries for the database in tnsnames.ora
[oracle@host01]$vi $ORACLE_HOME/network/admin/tnsnames.ora
— Following is the entry I copied and added :
NEWDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = cluster01-scan.cluster01.example.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = newdb)
)
)
– check that database can be connected remotely from remote node.
host02$sqlplus system/oracle@newdb
— check that datafiles have converted to ASM
SQL>select name from v$datafile;
NAME
——————————————————————————–
+DATA/newdb/datafile/system.276.808311515
+DATA/newdb/datafile/sysaux.275.808311589
+DATA/newdb/datafile/undotbs1.272.808311655
+DATA/newdb/datafile/users.282.808311747
+DATA/newdb/datafile/example.273.808311721
+DATA/newdb/datafile/undotbs2.280.808311735
+DATA/newdb/datafile/undotbs3.281.808311743
I hope that you found this post useful. Your comments and suggestions are always welcome.
——————————————————————————————————-
11G R2 RAC Index
11g R2 RAC: Clone Database Home