Create a single instance database on any of cluster nodes
Create instance
-------------------------------------------------------
C:\Users\farif>oradim -new -sid RCTEST
Instance created.
C:\Users\farif>set oracle_sid=RCTEST
C:\Users\farif>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Wed May 29 11:59:03 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=D:\rmanbackup\initrctest.ora
ORACLE instance started.
Total System Global Area 1553379328 bytes
Fixed Size 2255464 bytes
Variable Size 1224738200 bytes
Database Buffers 318767104 bytes
Redo Buffers 7618560 bytes
CREATE DATABASE RCTEST
LOGFILE GROUP 1 ('D:\APP\INAM\ORADATA\RCTEST\REDO1.LOG') SIZE 10M,
GROUP 2 ('D:\APP\INAM\ORADATA\RCTEST\REDO2.LOG') SIZE 10M,
GROUP 3 ('D:\APP\INAM\ORADATA\RCTEST\REDO3.LOG') SIZE 10M
CHARACTER SET WE8MSWIN1252
NATIONAL CHARACTER SET AL16UTF16
DATAFILE 'D:\APP\INAM\ORADATA\RCTEST\SYSTEM01.DBF' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE 'D:\APP\INAM\ORADATA\RCTEST\SYSAUX.DBF' SIZE 50M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE 'D:\APP\INAM\ORADATA\RCTEST\USERS01.DBF' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'D:\APP\INAM\ORADATA\RCTEST\TEMP01.DBF' SIZE 40M AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS01 DATAFILE 'D:\APP\INAM\ORADATA\RCTEST\UNDOTBS01.DBF' SIZE 50M
/
Run these scripts
@D:\app\Inam\product\11.2.0.3\dbhome_1\RDBMS\ADMIN\catalog.sql
@D:\app\Inam\product\11.2.0.3\dbhome_1\RDBMS\ADMIN\catproc.sql
@D:\app\Inam\product\11.2.0.3\dbhome_1\sqlplus\admin\pupbld.sql
shutdown database;
startup mount;
alter database archivelog;
-------------------------------------------
Backup database
-------------------------------------------
RUN {
ALLOCATE CHANNEL ch00 TYPE DISK FORMAT 'D:\rmanbackup\db\rctest\%d_DB_%u_%s_%p';
set controlfile autobackup format for device type disk to "D:\rmanbackup\ctl\cf_rctest.%F";
backup
filesperset 4
INCREMENTAL LEVEL 0 AS COMPRESSED BACKUPSET database;
sql "alter system archive log current";
release channel ch00;
ALLOCATE CHANNEL CH01 TYPE DISK FORMAT 'D:\RMANBACKUP\AR\rctest\al_%U';
backup
filesperset 4
AS COMPRESSED BACKUPSET archivelog all;
RELEASE CHANNEL CH01;
}
---------------------------------------------
Make Directories on ASM
----------------------------------------------
C:\Users\farif>set oracle_sid=+ASM1
C:\Users\farif>SET ORACLE_HOME=D:\app\Inam\product\11.2.0.3\dbhome_1
C:\Users\farif>asmcmd -p
ASMCMD [+] >cd DBDATA
ASMCMD [+DBDATA] >mkdir rctest
ASMCMD [+DBDATA] >cd rctest
ASMCMD [+DBDATA/rctest]mkdir controlfile
--------------------------------------------------
Change controlfile location parameter in parameter file
--------------------------------------------------
large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\inam\admin\rctest\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+dbdata/rctest/controlfile/control01.ctl'
#CONTROL_FILES='D:\app\Inam\fast_recovery_area\RCTEST\CONTROL01.CTL'
db_block_size=8192
db_domain=''
db_name='rctest'
#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'
#rctest1.instance_number=1
#rctest2.instance_number=2
#cluster_database=true
#cluster_database_instances=2
-------------------------------------------------
shutdown database start in nomount state and restore controlfile on ASM
-------------------------------------------------
RMAN> SHUTDOWN ABORT;
Oracle instance shut down
RMAN> STARTUP NOMOUNT PFILE=D:\rmanbackup\initrctest.ora
connected to target database (not started)
Oracle instance started
Total System Global Area 1553379328 bytes
Fixed Size 2255464 bytes
Variable Size 1224738200 bytes
Database Buffers 318767104 bytes
Redo Buffers 7618560 bytes
RMAN> RESTORE CONTROLFILE FROM 'D:\RMANBACKUP\CTL\RCTEST\CF_HOMEDB.C-492039321-20130529-01';
Starting restore at 29-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=790 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DBDATA/rctest/controlfile/control01.ctl
Finished restore at 29-MAY-13
RMAN> ALTER DATABASE MOUNT;
database mounted
released channel: ORA_DISK_1
RMAN>
------------------------------------------------------
Check backup and number of files in backup
------------------------------------------------------
RMAN> LIST BACKUP OF DATABASE;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4 Incr 0 52.91M DISK 00:00:13 29-MAY-13
BP Key: 4 Status: AVAILABLE Compressed: YES Tag: TAG20130529T135658
Piece Name: D:\RMANBACKUP\DB\RCTEST\RCTEST_DB_04OARPHA_4_1
List of Datafiles in backup set 4
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 0 Incr 205208 29-MAY-13 D:\APP\INAM\ORADATA\RCTEST\SYSTEM01.DBF
2 0 Incr 205208 29-MAY-13 D:\APP\INAM\ORADATA\RCTEST\SYSAUX.DBF
3 0 Incr 205208 29-MAY-13 D:\APP\INAM\ORADATA\RCTEST\UNDOTBS01.DBF
4 0 Incr 205208 29-MAY-13 D:\APP\INAM\ORADATA\RCTEST\USERS01.DBF
------------------------------------------------------
Restore database using following script
------------------------------------------------------
RMAN> RUN {
ALLOCATE CHANNEL CH00 DEVICE TYPE DISK;
#SET UNTIL SCN 2214806551;
SET NEWNAME FOR DATAFILE 1 TO '+DBDATA';
SET NEWNAME FOR DATAFILE 2 TO '+DBDATA';
SET NEWNAME FOR DATAFILE 3 TO '+DBDATA';
SET NEWNAME FOR DATAFILE 4 TO '+DBDATA';
RESTORE DATABASE;
SWITCH DATAFILE ALL;
RELEASE CHANNEL CH00;
}
allocated channel: ch00
channel ch00: SID=659 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 29-MAY-13
Starting implicit crosscheck backup at 29-MAY-13
Crosschecked 3 objects
Finished implicit crosscheck backup at 29-MAY-13
Starting implicit crosscheck copy at 29-MAY-13
Finished implicit crosscheck copy at 29-MAY-13
searching for all files in the recovery area
cataloging files...
no files cataloged
channel ch00: starting datafile backup set restore
channel ch00: specifying datafile(s) to restore from backup set
channel ch00: restoring datafile 00001 to +DBDATA
channel ch00: restoring datafile 00002 to +DBDATA
channel ch00: restoring datafile 00003 to +DBDATA
channel ch00: restoring datafile 00004 to +DBDATA
channel ch00: reading from backup piece D:\RMANBACKUP\DB\RCTEST\RCTEST_DB_04OARPHA_4_1
channel ch00: piece handle=D:\RMANBACKUP\DB\RCTEST\RCTEST_DB_04OARPHA_4_1 tag=TAG20130529T135658
channel ch00: restored backup piece 1
channel ch00: restore complete, elapsed time: 00:00:15
Finished restore at 29-MAY-13
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=816704947 file name=+DBDATA/rctest/datafile/system.313.816704931
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=816704947 file name=+DBDATA/rctest/datafile/sysaux.314.816704933
datafile 3 switched to datafile copy
input datafile copy RECID=7 STAMP=816704947 file name=+DBDATA/rctest/datafile/undotbs01.300.816704933
datafile 4 switched to datafile copy
input datafile copy RECID=8 STAMP=816704947 file name=+DBDATA/rctest/datafile/users.331.816704933
released channel: ch00
RMAN> RECOVER DATABASE;
Starting recover at 29-MAY-13
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=659 device type=DISK
starting media recovery
archived log for thread 1 with sequence 90 is already on disk as file +DBFLASH/rctest/archivelog/2013_05_29/thread_1_seq_90.2870.816703041
archived log for thread 1 with sequence 91 is already on disk as file +DBFLASH/rctest/archivelog/2013_05_29/thread_1_seq_91.2871.816703043
archived log for thread 1 with sequence 92 is already on disk as file D:\APP\INAM\ORADATA\RCTEST\REDO2.LOG
archived log file name=+DBFLASH/rctest/archivelog/2013_05_29/thread_1_seq_90.2870.816703041 thread=1 sequence=90
archived log file name=+DBFLASH/rctest/archivelog/2013_05_29/thread_1_seq_91.2871.816703043 thread=1 sequence=91
archived log file name=D:\APP\INAM\ORADATA\RCTEST\REDO2.LOG thread=1 sequence=92
media recovery complete, elapsed time: 00:00:00
Finished recover at 29-MAY-13
------------------------------------
SELECT MEMBER FROM V$LOGFILE;
SQL> ALTER DATABASE RENAME FILE 'D:\app\Inam\oradata\rctest\REDO01.LOG' TO '+DBDATA';
SQL> ALTER DATABASE RENAME FILE 'D:\app\Inam\oradata\rctest\REDO02.LOG' TO '+DBDATA';
SQL> ALTER DATABASE RENAME FILE 'D:\app\Inam\oradata\rctest\REDO03.LOG' TO '+DBDATA';
-----------------------------------
RMAN> ALTER DATABASE OPEN RESETLOGS;
database opened
RMAN>
-------------------------------------------------
Add temp file
------------------------------------------------
SQL> SELECT NAME FROM V$TEMPFILE;
NAME
--------------------------------------------------------------------------------
D:\APP\INAM\ORADATA\RCTEST\TEMP01.DBF
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DBDATA' SIZE 100M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;
SQL> ALTER TABLESPACE TEMP DROP TEMPFILE 'D:\APP\INAM\ORADATA\RCTEST\TEMP01.DBF';
Tablespace altered.
-----------------------------------------------------
SQL> SELECT NAME FROM V$DATAFILE;
NAME
-----------------------------------------------------
+DBDATA/rctest/datafile/system.313.816704931
+DBDATA/rctest/datafile/sysaux.314.816704933
+DBDATA/rctest/datafile/undotbs01.300.816704933
+DBDATA/rctest/datafile/users.331.816704933
-----------------------------------------------------
ADD Second tablespace for instance 2
CREATE UNDO TABLESPACE UNTOTBS02 DATAFILE '+DBDATA' SIZE 500M;
-----------------------------------------------------
CONVERT TO RAC
Change parameters in PFILE and create a spfile for cluster
---------------------------------------------------
large_pool_size=100m
shared_pool_size=1024m
db_cache_size=300m
audit_file_dest='D:\app\inam\admin\rctest\adump'
audit_trail='db'
compatible='11.2.0.0.0'
control_files='+dbdata/rctest/controlfile/control01.ctl'
#CONTROL_FILES='D:\app\Inam\fast_recovery_area\RCTEST\CONTROL01.CTL'
db_block_size=8192
db_domain=''
db_name='rctest'
#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'
rctest1.instance_number=1
rctest2.instance_number=2
rctest1.thread=1
rctest2.thread-2
rctest1.undo_tablespace='UNDOTBS01'
rctest2.undo_tablespace='UNDOTBS02'
cluster_database=true
cluster_database_instances=2
-------------------------------
---------------------------------------------------
Drop old redo logs and add new log files for both threads 2 groups for each thread
---------------------------------------------------
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance rctest1 (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '+DBDATA/rctest/onlinelog/group_3.301.816960063'
SQL> alter system switch logfile;
System altered.
SQL> alter system checkpoint;
System altered.
SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance rctest1 (thread 1)
ORA-00312: online log 3 thread 1: '+DBDATA/rctest/onlinelog/group_3.301.816960063'
SQL> alter database add logfile thread 1 group 2;
Database altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile thread 2 group 3;
Database altered.
SQL> alter database drop logfile group 4;
alter database drop logfile group 4
*
ERROR at line 1:
ORA-00359: logfile group 4 does not exist
SQL> alter database add logfile thread 2 group 4;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
----------------------------------------------------
SQL> create spfile='+DBDATA' FROM PFILE='D:\rmanbackup\initrctest.ora';
File created.
----------------------------------------------
Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
---------------------------------------------
Check availability of rctest database in cluster
D:\app\11.2.0.3\grid\BIN>srvctl config database -d rctest
PRCD-1120 : The resource for database rctest could not be found.
PRCR-1001 : Resource ora.rctest.db does not exist
----------------------------------------------
Add rctest database to cluster
----------------------------------------------
D:\app\11.2.0.3\grid\BIN>srvctl add database -d rctest -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 rctest
Database unique name: rctest
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: rctest
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 rctest -i rctest1 -n or-11
D:\app\11.2.0.3\grid\BIN>srvctl add instance -d rctest -i rctest2 -n or-12
Check instances are added to cluster or not
D:\app\11.2.0.3\grid\BIN>srvctl config database -d rctest
Database unique name: rctest
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: rctest
Database instances: rctest1,rctest2
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
Node1
D:\app\Inam\product\11.2.0.3\dbhome_1\database\initrctest1.ora
Node2
D:\app\Inam\product\11.2.0.3\dbhome_1\database\initrctest2.ora
------------------------------------------
Add only one line in both above files.
------------------------------------------
SPFILE='+dbdata/rctest/parameterfile/spfile.333.816706063'
start cluster database
D:\app\11.2.0.3\grid\BIN>srvctl start database -d rctest
-----------------------------------------
D:\app\11.2.0.3\grid\BIN>srvctl config database -d rctest
Database unique name: rctest
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: rctest
Database instances: rctest1,rctest2
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 rctest
Instance rctest1 is running on node or-11
Instance rctest2 is running on node or-12
SQL> select instance_name,status,host_name from gv$instance;
INSTANCE_NAME STATUS HOST_NAME
---------------- ------------ ----------------------------------------------------------------
rctest1 OPEN OR-11
rctest2 OPEN OR-12
--------------------------------
D:\app\11.2.0.3\grid\BIN>crsctl status res ora.rctest.db -t
--------------------------------------------------------------------------------
NAME TARGET STATE SERVER STATE_DETAILS
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.rctest.db
1 ONLINE ONLINE or-11 Open
2 ONLINE ONLINE or-12 Open
No comments:
Post a Comment