DB NAME = RACTEST
VERSION 11.2.0.3
SERVER WINDOWS 2008R2
TYPE = SINGLE INSTANCE
--------------------------------------------------------------------
Migration from FileSystem to ASM
alter system set db_recovery_file_dest_size='50G' scope=spfile;
alter system set db_recovery_file_dest='+DBFLASH' scope=spfile;
alter system set db_create_file_dest= '+DBDATA' scope=spfile;
================================================================
Reset controlfile parameter so when we will restore it from current controlfile copy on disk it will create new controlfiles on ASM (Using db_create_file_dest parameter we set in previous step).
SQL> alter system reset control_files;
===============================================================
Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
================================================================
Start Instance in nomount state
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1570009088 bytes
Fixed Size 2255544 bytes
Variable Size 402654536 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7471104 bytes
==================================================================
Convert from file system to ASM
C:\Users\farif>rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Tue Mar 25 14:44:20 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: RACTEST (not mounted)
RMAN> restore controlfile from 'D:\app\Inam\oradata\ractest\CONTROL01.CTL';
Starting restore at 25-MAR-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=160 device type=DISK
channel ORA_DISK_1: copied control file copy
output file name=+DBDATA/ractest/controlfile/current.283.843144347
output file name=+DBFLASH/ractest/controlfile/current.256.843144347
Finished restore at 25-MAR-14
-----------------------------------------------------------------------------
Output files are automatically created on ASM because we reset control_files parameter and during new file creation database used parameter (db_create_file_dest)
========================================================================
Change spfile parameter control_files to newly created files
SQL> ALTER SYSTEM SET CONTROL_FILES='+DBDATA/ractest/controlfile/current.283.843144347','+DBFLASH/ractest/controlfile/current.256.843144347' scope=spfile;
System altered.
========================================================================
Restart database to use newly created control_files on ASM
RMAN> shutdown immediate;
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mounted
Total System Global Area 1570009088 bytes
Fixed Size 2255544 bytes
Variable Size 402654536 bytes
Database Buffers 1157627904 bytes
Redo Buffers 7471104 bytes
-----------------------------------------------------------------------------------------------
Backup database as copy and switch to new db
RMAN> backup as copy database format '+DBDATA';
Starting backup at 25-MAR-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=34 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=D:\APP\INAM\ORADATA\RACTEST\SYSTEM01.DBF
output file name=+DBDATA/ractest/datafile/system.302.843144749 tag=TAG20140325T145227 RECID=1 STAMP=843144751
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=D:\APP\INAM\ORADATA\RACTEST\SYSAUX01.DBF
output file name=+DBDATA/ractest/datafile/sysaux.290.843144751 tag=TAG20140325T145227 RECID=2 STAMP=843144754
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=D:\APP\INAM\ORADATA\RACTEST\UNDOTBS01.DBF
output file name=+DBDATA/ractest/datafile/undotbs1.262.843144755 tag=TAG20140325T145227 RECID=3 STAMP=843144756
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DBDATA/ractest/controlfile/backup.282.843144757 tag=TAG20140325T145227 RECID=4 STAMP=843144758
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=D:\APP\INAM\ORADATA\RACTEST\USERS01.DBF
output file name=+DBDATA/ractest/datafile/users.304.843144759 tag=TAG20140325T145227 RECID=5 STAMP=843144759
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 25-MAR-14
channel ORA_DISK_1: finished piece 1 at 25-MAR-14
piece handle=+DBDATA/ractest/backupset/2014_03_25/nnsnf0_tag20140325t145227_0.289.843144761 tag=TAG20140325T145227
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 25-MAR-14
=================================================================
RMAN> switch database to copy;
datafile 1 switched to datafile copy "+DBDATA/ractest/datafile/system.302.843144749"
datafile 2 switched to datafile copy "+DBDATA/ractest/datafile/sysaux.290.843144751"
datafile 3 switched to datafile copy "+DBDATA/ractest/datafile/undotbs1.262.843144755"
datafile 4 switched to datafile copy "+DBDATA/ractest/datafile/users.304.843144759"
=================================================================
RMAN> ALTER DATABASE OPEN;
database opened
==================================================================
Remove and recreate logfiles one by one to ASM
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------------
D:\APP\INAM\ORADATA\RACTEST\REDO01.LOG
D:\APP\INAM\ORADATA\RACTEST\REDO02.LOG
D:\APP\INAM\ORADATA\RACTEST\REDO03.LOG
SQL>
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';
System altered.
SQL> ALTER DATABASE ADD LOGFILE GROUP 1;
Database altered.
========================================================================
Confirm that new logfile group is added on ASM
SQL> SELECT MEMBER FROM V$LOGFILE;
MEMBER
--------------------------------------------------------------------------------
+DBDATA/ractest/onlinelog/group_1.303.843145147
D:\APP\INAM\ORADATA\RACTEST\REDO02.LOG
D:\APP\INAM\ORADATA\RACTEST\REDO03.LOG
+DBFLASH/ractest/onlinelog/group_1.257.843145147
--------------------------------------------------------------------------------
Add logfiles for thread 2 which we will use for second instance after conversion to RAC
We will create 3 logfile groups for each thread (total 6) on ASM
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 3;
Database altered.
------------------------------------------------------------------
Enable thread 2
SQL> ALTER DATABASE ENABLE THREAD 2;
Database altered.
=================================================================
Now if we check we will find that total 6 logfile groups are created 1-3-5 for thread 1 and 2-4-6 for thread 2 with a multiplexed copy on +DBFLASH
SQL> select member from v$logfile;
--------------------------------------------------------------------------------
+DBDATA/ractest/onlinelog/group_1.303.843145147
+DBDATA/ractest/onlinelog/group_2.263.843145283
+DBDATA/ractest/onlinelog/group_3.295.843145507
+DBFLASH/ractest/onlinelog/group_1.257.843145147
+DBFLASH/ractest/onlinelog/group_2.258.843145283
+DBDATA/ractest/onlinelog/group_4.285.843145339
+DBFLASH/ractest/onlinelog/group_4.259.843145341
+DBDATA/ractest/onlinelog/group_6.284.843145413
+DBFLASH/ractest/onlinelog/group_6.260.843145415
+DBDATA/ractest/onlinelog/group_5.286.843145467
+DBFLASH/ractest/onlinelog/group_5.4866.843145467
+DBFLASH/ractest/onlinelog/group_3.4871.843145507
12 rows selected.
------------------------------------------------------------------------------
ADD ANOTHER TEMPFILE TO TEMPORARY TABLESPACE which will be automatically created on ASM and drop old one which was on filesystem
SQL> SELECT NAME FROM V$TEMPFILE;
NAME
--------------------------------------------------------------------------------
D:\APP\INAM\ORADATA\RACTEST\TEMP01.DBF
SQL> ALTER TABLESPACE TEMP01 ADD TEMPFILE '+DBDATA' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
SQL> ALTER TABLESPACE TEMP01 DROP TEMPFILE 'D:\APP\INAM\ORADATA\RACTEST\TEMP01.DBF';
--------------------------------------------------------------------------------
ADD Second tablespace for instance 2
CREATE UNDO TABLESPACE UNTOTBS02 DATAFILE '+DBDATA' SIZE 500M;
--------------------------------------------------------------------------------
Convert to RAC
create pfile from spfile and add parameters for RAC (HIGHLIGHTED)
SQL > CREATE PFILE='D:\TEMP\INITRACTEST.TXT' from SPFILE;
File Created
Edit pfile
#############################################################
large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\inam\admin\RACTEST\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+dbdata/RACTEST/controlfile/control01.ctl'
#CONTROL_FILES='D:\app\Inam\fast_recovery_area\RACTEST\CONTROL01.CTL'
db_block_size=8192
db_domain=''
db_name='RACTEST'
#db_recovery_file_dest='D:\app\inam\fast_recovery_area'
#db_recovery_file_dest_size=572354723840
db_recovery_file_dest='+DBFLASH'
db_recovery_file_dest_size=10G
diagnostic_dest='D:\app\inam'
remote_login_passwordfile='exclusive'
RACTEST1.instance_number=1
RACTEST2.instance_number=2
RACTEST1.thread=1
RACTEST2.thread=2
RACTEST1.undo_tablespace='UNDOTBS01'
RACTEST2.undo_tablespace='UNDOTBS02'
cluster_database=true
cluster_database_instances=2
-------------------------------------------------------------------
Create SPFILE from PFILE
SQL> create spfile='+DBDATA' FROM PFILE='D:\TEMP\INITRACTEST.TXT';
-------------------------------------------------------------------
Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
---------------------------------------------
Check availability of RACTEST database in cluster
D:\app\11.2.0.3\grid\BIN>srvctl config database -d RACTEST
PRCD-1120 : The resource for database RACTEST could not be found.
PRCR-1001 : Resource ora.RACTEST.db does not exist
----------------------------------------------
Add RACTEST database to cluster
----------------------------------------------
D:\app\11.2.0.3\grid\BIN>srvctl add database -d RACTEST -o D:\app\Inam\product\11.2.0.3\dbhome_1
----------------------------------------------
Check database is added to cluster
----------------------------------------------
D:\app\11.2.0.3\grid\BIN>srvctl config database -d RACTEST
Database unique name: RACTEST
Database name:
Oracle home: D:\app\Inam\product\11.2.0.3\dbhome_1
Oracle user: nt authority\system
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACTEST
Database instances:
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
----------------------------------------------------
Now add RAC instances
D:\app\11.2.0.3\grid\BIN>srvctl add instance -d RACTEST -i RACTEST1 -n or-11
D:\app\11.2.0.3\grid\BIN>srvctl add instance -d RACTEST -i RACTEST2 -n or-12
Confirm that instances are added to cluster
D:\app\11.2.0.3\grid\BIN>srvctl config database -d RACTEST
Database unique name: RACTEST
Database name:
Oracle home: D:\app\Inam\product\11.2.0.3\dbhome_1
Oracle user: nt authority\system
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACTEST
Database instances: RACTEST1,RACTEST2
Disk Groups:
Mount point paths:
Services:
Type: RAC
Database is administrator managed
----------------------------------------
Create pfiles on both nodes
----------------------------------------
Create a parameter file on both cluster nodes and add only one parameter for SPFILE in these files
Node1
D:\app\Inam\product\11.2.0.3\dbhome_1\database\initRACTEST1.ora
Node2
D:\app\Inam\product\11.2.0.3\dbhome_1\database\initRACTEST2.ora
------------------------------------------
Add only one line in both above files.
------------------------------------------
SPFILE='+dbdata/RACTEST/parameterfile/spfile.333.816706063'
start cluster database
D:\app\11.2.0.3\grid\BIN>srvctl start database -d RACTEST
-----------------------------------------
D:\app\11.2.0.3\grid\BIN>srvctl config database -d RACTEST
Database unique name: RACTEST
Database name:
Oracle home: D:\app\Inam\product\11.2.0.3\dbhome_1
Oracle user: nt authority\system
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: RACTEST
Database instances: RACTEST1,RACTEST2
Disk Groups: DBDATA,DBFLASH
Mount point paths:
Services:
Type: RAC
Database is administrator managed
--------------------------------------------------
D:\app\11.2.0.3\grid\BIN>srvctl status database -d RACTEST
Instance RACTEST1 is running on node or-11
Instance RACTEST2 is running on node or-12
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
RACTEST1 OPEN OR-11
RACTEST2 OPEN OR-12
--------------------------------
D:\app\11.2.0.3\grid\BIN>crsctl status res ora.RACTEST.db -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.RACTEST.db
1 ONLINE ONLINE or-11 Open
2 ONLINE ONLINE or-12 Open
No comments:
Post a Comment