Create a non-RAC database
Create a pfile
1) Create a pfile for a non-RAC database on node 1 (you may keep RAC specific parameters also in it since it will be later converted to a RAC database)
Here's a sample pfile (notice that I commented out the cluster_database and undo_tablespace parameters but left other RAC specific parameter on) --
PLINKTST2.__db_cache_size=4395630592
PLINKTST1.__db_cache_size=4395630592
PLINKTST2.__java_pool_size=16777216
PLINKTST1.__java_pool_size=16777216
PLINKTST2.__large_pool_size=16777216
PLINKTST1.__large_pool_size=16777216
PLINKTST2.__shared_pool_size=905969664
PLINKTST1.__shared_pool_size=905969664
PLINKTST2.__streams_pool_size=0
PLINKTST1.__streams_pool_size=0
##*.cluster_database=true
*.cluster_database_instances=2
*.compatible='10.2.0.4'
*.db_block_size=8192
*.db_create_online_log_dest_1='+PLINKTST_DATA'
*.db_domain=''
*.db_file_multiblock_read_count=32
*.db_name='PLINKTST'
*.db_unique_name='PLINKTST'
*.db_create_file_dest='+PWTEST_DATA'
*.db_recovery_file_dest='+PWTEST_DATA'
*.db_recovery_file_dest_size=53687091200
*.job_queue_processes=0
*.log_archive_dest_1='LOCATION=+PWTEST_DATA'
*.log_buffer=20971520
*.open_cursors=300
*.optimizer_mode='ALL_ROWS'
*.pga_aggregate_target=500M
*.processes=100
*.remote_login_passwordfile='exclusive'
*.resource_limit=TRUE
*.sga_max_size=1G
*.sga_target=1G
*.star_transformation_enabled='TRUE'
*.undo_management='AUTO'
*.undo_retention=3600
PLINKTST1.local_listener='LISTENER_PLINKTST1' PLINKTST2.local_listener='LISTENER_PLINKTST2'
#PLINKTST1.undo_tablespace='UNDOTBS1'
#PLINKTST2.undo_tablespace='UNDOTBS2'
PLINKTST1.instance_number=1
PLINKTST2.instance_number=2
PLINKTST1.thread=1
PLINKTST2.thread=2
*.user_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/udump' *.control_files='+PWTEST_DATA/plinktst/controlfile/plinktst.ctl' *.audit_file_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/adump' *.background_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/bdump' *.core_dump_dest='/d01/app/pwprd/product/10.2.0/pwprddb/admin/PLINKTST/cdump' *.fal_server=PLINKTST
*.fal_client=PLINKTST
*.standby_file_management=AUTO
[Note: Use a node# in the instance name parameter (e.g. PLINKTST1) since it would later be converted into a RAC instance]
Startup nomount
PLINKTST1 > startup nomount
ORA-01078: failure in processing system parameters
ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_PLINKTST1'
PLINKTST1 >
Well, this needs a TNS entry for the local listener listed above (we can worry about the actual listener later - all it needs now is resolving the name LISTENER_PLINKTST1.
Open up the tnsnames.ora and add the following lines --
LISTENER_PLINKTST1 = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)) )
and start the instance.
PLINKTST1 > startup nomount
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
PLINKTST1 >
Create the database
If we forgot to remove the "cluster_database" parameter earlier, we'll see an error while creating database as shown below.
PLINKTST1 > create database;
create database
*
ERROR at line 1:
ORA-01501: CREATE DATABASE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
PLINKTST1 > show parameter cluster
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cluster_database boolean TRUE
cluster_database_instances integer 2
cluster_interconnects string
PLINKTST1 >
Remove the parameter from the pfile; startup nomount and execute the create database again.
Once the database is created, execute all the usual good stuff - catalog, catproc, pupbld scripts (all under
Add separate Undo Tablespaces
First, Create 2 UNDO tablespaces for the 2 instances.
PLINKTST1 > create undo tablespace UNDOTBS1 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
Tablespace created.
PLINKTST1 > create undo tablespace UNDOTBS2 datafile '+PWTEST_DATA' size 100M autoextend on next 50M maxsize 500M;
Tablespace created.
Add new REDO thread and enable it
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)
PLINKTST1 > alter database add logfile thread 2 group 10 '+PWTEST_DATA' size 100M;
Database altered.
PLINKTST1 > alter database add logfile thread 2 group 11 '+PWTEST_DATA' size 100M;
Database altered.
PLINKTST1 > alter database enable public thread 2;
Database altered.
RAC Specific parameters
Add all RAC specific parameters to the pfile (I simply uncommented the undo_tablespaces and the cluster_data parms as I already included those). If the RAC specific were not included earlier in the pfile, add those now. Here are the main ones -- *.cluster_database=true
*.cluster_database_instances=2
PLINKTST1.local_listener='LISTENER_PLINKTST1' PLINKTST2.local_listener='LISTENER_PLINKTST2'
PLINKTST1.undo_tablespace='UNDOTBS1'
PLINKTST2.undo_tablespace='UNDOTBS2'
PLINKTST1.instance_number=1
PLINKTST2.instance_number=2
PLINKTST1.thread=1
PLINKTST2.thread=2
Start the additional Listeners
We added a TNS entry for the LOCAL_LISTENER earlier. Lets start a new listener at that port (1522 in this case).
Update the following files::
listener.ora
----------------
LISTENER_PLINKTST_RPTDB01-PWPRD =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = rptdb01-pwprd)(PORT = 1522)(IP = FIRST))
)
)
SID_LIST_LISTENER_PLINKTST_DB01-PWPRD =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /d01/app/pwprd/product/10.2.0/pwprddb)
(PROGRAM = extproc)
)
Start the above listener. Also, add the TNS entry for the new database.
tnsnames.ora
--------------------
PLINKTST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb01-pwprd)(PORT = 1522))
(ADDRESS = (PROTOCOL = TCP)(HOST = vip-rptdb02-pwprd)(PORT = 1522))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PLINKTST)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
Shared pfile
Move the pfile to a shared location in ASM; restart instance 1; create all the appropriate directories on node2 and start the instance 2.
If you get the following error, make sure you've created the additional thread and enabled it (see the section "Add new REDO thread and enable it") --
PLINKTST2 > startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
ORA-01620: no public threads are available for mounting
..
..
..
From node 1:
PLINKTST1 > alter database enable public thread 2;
Database altered.
..
The database should come up ok now.
PLINKTST2 > startup
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2144432 bytes
Variable Size 257247056 bytes
Database Buffers 792723456 bytes
Redo Buffers 21626880 bytes
Database mounted.
Database opened.
PLINKTST2 >
This completes create a RAC database using SQL*Plus. Next we should register all the resources with CRS.
Registering with CRS
$ srvctl add database -d PLINKTST -o /d01/app/pwprd/product/10.2.0/pwprddb
$ srvctl add instance -d PLINKTST -i PLINKTST1 -n rptdb01-pwprd
$ srvctl add instance -d PLINKTST -i PLINKTST2 -n rptdb02-pwprd
$ srvctl add service -d PLINKTST -s PLINKTSTSVC -r "PLINKTST1,PLINKTST2"
Check for all the services just registered.
$ . oraenv
ORACLE_SID = [PLINKTST1] ? CRS
$ crs_stat |grep PLINK
NAME=ora.PLINKTST.PLINKTST1.inst
NAME=ora.PLINKTST.PLINKTST2.inst
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST1.srv
NAME=ora.PLINKTST.PLINKTSTSVC.PLINKTST2.srv
NAME=ora.PLINKTST.PLINKTSTSVC.cs
NAME=ora.PLINKTST.db
All the new resources have been registered with CRS.
