Creating a RAC Database.
DB Name : layatest
Instance 1 : layatest1
Instance 2 : layatest2
ASM Diskgroups - DATA1 and DATA3
1) Create the Required directories in both the nodes
mkdir -p /u01/app/oracle/admin/layatest/adump
mkdir -p /u01/app/oracle/admin/layatest/bdump
mkdir -p /u01/app/oracle/admin/layatest/cdump
mkdir -p /u01/app/oracle/admin/layatest/udump
mkdir -p /u01/app/oracle/admin/layatest/pfile
2) Create a parameter file.
###########################################
log_archive_dest_1='LOCATION=+DATA2/'
log_archive_format=%t_%s_%r.dbf
db_block_size=8192
db_file_multiblock_read_count=16
open_cursors=300
db_name=layatest
background_dump_dest=/u01/app/oracle/admin/layatest/bdump
core_dump_dest=/u01/app/oracle/admin/layatest/cdump
user_dump_dest=/u01/app/oracle/admin/layatest/udump
db_create_file_dest=+DATA1
compatible=10.2.0.3.0
processes=150
sga_target=285212672
audit_file_dest=/u01/app/oracle/admin/layatest/adump
remote_login_passwordfile=exclusive
pga_aggregate_target=94371840
undo_management=AUTO
control_files=("+DATA1","+DATA3")
cluster_database_instances=2
#cluster_database=true
layatest1.instance_number=1
layatest2.instance_number=2
layatest2.thread=2
layatest1.undo_tablespace=UNDOTBS1
layatest1.thread=1
3) Create password file in one node and copy that to all the nodes.
rhelrac1-> cd $ORACLE_HOME/dbs
rhelrac1-> orapwd file=orapwlayatest1 password=admin entries=5
rhelrac1-> scp orapwlayatest1 oracle@rhelrac2:/u01/app/oracle/product/10.2.0/db_1/dbs/orapwlayatest2
4) Create spfile using the pfile and the edit the init ora parameter to point the spfile in both the instance.
rhelrac1-> export ORACLE_SID=+ASM1
rhelrac1-> asmcmd
ASMCMD> lsdg
State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 3858 3760 0 3760 0 DATA1/
MOUNTED NORMAL N N 512 4096 1048576 3765 3483 1255 1114 0 DATA2/
MOUNTED EXTERN N N 512 4096 1048576 3000 2903 0 2903 0 DATA3/
MOUNTED EXTERN N N 512 4096 1048576 3858 3760 0 3760 0 FLASH/
MOUNTED EXTERN N N 512 4096 1048576 1137 1044 0 1044 0 ORADATA/
MOUNTED NORMAL N N 512 4096 1048576 1928 1742 0 871 0 ORANORMAL/
ASMCMD> cd data2
ASMCMD> ls
ASMCMD> mkdir layatest
ASMCMD> cd layatest
ASMCMD> mkdir spfile
ASMCMD> cd spfile
ASMCMD> pwd
+data2/layatest/spfile
Crate the spfile in the above location +DATA2/layatest/spfile
rhelrac1-> export ORACLE_SID=layatest1
rhelrac1-> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 14:47:25 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> create spfile='+DATA2/layatest/spfile/spfilelayatest.ora' from pfile='/u01/app/oracle/admin/layatest/pfile/initlayatest.ora';
File created.
SQL> exit
Now edit the pfiles in the oracle home location and point to the spfile. Repeat the same in all the nodes.
rhelrac1-> cd $ORACLE_HOME/dbs
rhelrac1-> vi initlayatest1.ora
spfile=+DATA2/layatest/spfile/spfilelayatest.ora
:wq!
rhelrac2-> cd $ORACLE_HOME/dbs
rhelrac2-> vi initlayatest2.ora
spfile=+DATA2/layatest/spfile/spfilelayatest.ora
4) Startup the instance in node1.
rhelrac1-> export ORACLE_SID=layatest1
rhelrac1-> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 14:52:30 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes
SQL> create database layatest
2 datafile '+DATA1' size 500m extent management local
3 sysaux datafile '+DATA1' size 500m
4 default temporary tablespace temp tempfile '+DATA1' size 100m
5 undo tablespace undotbs1 datafile '+DATA1' size 100m
6 logfile
7 group 1 ('+DATA1','+DATA2') size 10m,
8 group 2 ('+DATA1','+DATA2') size 10m
9 /
Database created.
SQL> @?/rdbms/admin/catalog.sql;
PL/SQL procedure successfully completed.
SQL> @?/rdbms/admin/catproc.sql;
PL/SQL procedure successfully completed.
-- Create undo tablespace for the 2nd node.
SQL> create undo tablespace undotbs2 datafile '+DATA1' size 100m;
Tablespace created.
SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='layatest2';
System altered.
-- Enable the cluster database.
SQL> alter system set cluster_database=true scope=spfile sid='*';
System altered.
--Create online redo log file for the 2nd node.
SQL> alter database add logfile thread 2 group 3 ('+DATA1','+DATA3') size 10m;
Database altered.
SQL> alter database add logfile thread 2 group 4 ('+DATA1','+DATA3') size 10m;
Database altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
--Startup the database.
SQL> startup
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 96471684 bytes
Database Buffers 184549376 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
-- Enable the thread 2
SQL> alter database enable public thread 2;
Database altered.
--Create RAC specific Views.
SQL> @?/rdbms/admin/catclust.sql;
PL/SQL procedure successfully completed.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
-- Register the databse in OCR.
rhelrac1-> srvctl add database -d layatest -o /u01/app/oracle/product/10.2.0/db_1 -p +DATA2/layatest/spfile/spfilelayatest.ora
rhelrac1-> srvctl add instance -d layatest -i layatest1 -n rhelrac1
rhelrac1-> srvctl add instance -d layatest -i layatest2 -n rhelrac2
rhelrac1-> srvctl config database -d layatest
rhelrac1 layatest1 /u01/app/oracle/product/10.2.0/db_1
rhelrac2 layatest2 /u01/app/oracle/product/10.2.0/db_1
rhelrac1-> srvctl start database -d layatest
rhelrac1-> srvctl status database -d layatest
Instance layatest1 is running on node rhelrac1
Instance layatest2 is running on node rhelrac2
--End of Database creation.
--Enable the archive log mode.
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +DATA2
Oldest online log sequence 42
Current log sequence 43
--Disable the cluster database.
SQL> alter system set cluster_database = false scope=spfile sid='layatest1';
System altered.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
-- Stop the database and mount the database in the node1
rhelrac1-> srvctl stop database -d layatest
-- Mount the database exclusively in node 1
rhelrac1-> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 15:32:47 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 96471684 bytes
Database Buffers 184549376 bytes
Redo Buffers 2924544 bytes
Database mounted.
--enable archive log
SQL> alter database archivelog;
Database altered.
--Enable the cluster database.
SQL> alter system set cluster_database = true scope=spfile sid='layatest1';
System altered.
SQL> shut immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
rhelrac1-> srvctl start database -d layatest
rhelrac1-> sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 15:35:01 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA2
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 43
SQL>exit
-------------------------------------------------------------------------------------
Setting local listeners:-
------------------------
alter system set local_listener = 'listener_layatest1' scope=spfile sid='layatest1';
alter system set local_listener = 'listener_layatest2' scope=spfile sid='layatest2';
srvctl stop database -d layatest
tnsnames.ora on both nodes
--------------------------
layatest1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac1-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest1))
)
listener_layatest1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac1-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest1))
)
layatest2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest2))
)
listener_layatest2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest2))
)
layatest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac1-vip) (PORT = 1532))
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532))
(LOAD_BALANCE = yes)
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest1))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest2 (INSTANCE_NAME = layatest2))
)
Listener.ora in node1
---------------------
lsnrlayatest_rhelrac1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = extproc_layatest1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhelrac1-vip)(PORT = 1532) (IP = FIRST))
)
)
ADMIN_RESTRICTIONS_lsnrlayatest_rhelrac1=ON
--------------------------------------------
Start the listener:-
lsnrctl start lsnrlayatest_rhelrac1
Listener.ora in node2
---------------------
lsnrlayatest_rhelrac2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = extproc_layatest2))
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532) (IP = FIRST))
)
)
ADMIN_RESTRICTIONS_lsnrlayatest_rhelrac2=ON
--------------------------------------------
Start the Listener
lsnrctl start lsnrlayatest_rhelrac2
-----------------------------------
start the database.
-------------------
srvctl start database -d layatest
Saturday, March 17, 2012
Tuesday, March 13, 2012
Oracle support period
1) Support Status of each Oracle Server (RDBMS) Release Note:161818.1
2) Client / Server / Interoperability Support Between Different Oracle Versions [ID 207303.1]
Source - Oracle support
Subscribe to:
Posts (Atom)