Tuesday, August 23, 2011

Manual Database Creation for RAC

This document provides step by step instructions to create a RAC database manually. This procedure was tested on 11g, but should also work for other Oracle versions without or with some slight modifications. Following are the assumptions

  1. This is a 2 node RAC running on Solaris OS (All steps should also work on any other OS)
  2. Nodes names will be rac1 and rac2
  3. Oracle Clusterware (11.1.0.7) is installed and running
  4. Oracle Database Software (11.1.0.7) is installed
  5. Database name is saqdb and instances names will be saqdb1 and saqdb2
  6. Database will be same as option “custom database” selected during database creation using DBCA.
  7. ASM instances are up and running on both nodes with +DATA and +RCV (flash recovery area) disk group created.
  8. ORACLE_BASE= /u01/app/oracle
  9. ORACLE_HOME=/u01/app/oracle/product/11.1.0/db_1

Database Creation (all steps done by oracle user)
Create following directories on both nodes
mkdir -p /u01/app/oracle
mkdir -p /u01/app/oracle/admin/saqdb/adump
mkdir -p /u01/app/oracle/admin/saqdb/dpdump
mkdir -p /u01/app/oracle/admin/saqdb/hdump
mkdir -p /u01/app/oracle/admin/saqdb/pfile
mkdir -p /u01/app/oracle/cfgtoollogs/dbca/saqdb
On any node, set environment variable for ASM instance and create a directory “saqdb” on the disk group +DATA using ASMCMD
ASMCMD> cd DATAASMCMD> mkdir saqdb
ASMCMD> cd ..
ASMCMD> cd RCV
ASMCMD> mkdir saqdb
Add following entry in /var/opt/oracle/oratab file on both nodes. For other Unix bases systems, add this entry in /etc/oratab file.
saqdb:/u01/app/oracle/product/11.1.0/db_1:Y
Set environment variable for database home and create password file on both nodes
rac1
cd $ORACLE_HOME/dbs
orapwd file=orapwsaqdb1 password=mypassword
rac2
cd $ORACLE_HOME/dbs
orapwd file=orapwsaqdb2 password=mypassword
Create init file “initsaqdb1.ora” in $ORACLE_HOME/dbs directory with the following parameters on the rac1
   --------------------------------------------------------------------------------------------------

saqdb1.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
saqdb2.oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.sga_target=1024m
*.audit_file_dest='/u01/app/oracle/admin/saqdb/adump'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.cluster_database_instances=2
*.cluster_database=FALSE
*.compatible='11.1.0.0.0'
#*.control_files='+rcv/saqdb/controlfile/Current.3458.757175825'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_create_online_log_dest_1='+RCV'
*.db_domain=''
*.db_name='saqdb'
*.db_recovery_file_dest='+RCV'
*.db_recovery_file_dest_size=53687091200
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers=''
saqdb1.instance_number=1
saqdb2.instance_number=2
saqdb1.local_listener='LISTENER_SAQDB1'
saqdb2.local_listener='LISTENER_SAQDB2'
*.log_archive_dest_1='LOCATION=+RCV/'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=1000
*.pga_aggregate_target=125m
*.processes=1000
*.remote_listener='LISTENERS_SAQDB'
*.remote_login_passwordfile='exclusive'
*.sec_case_sensitive_logon=FALSE
*.sec_max_failed_login_attempts=3
*.sec_protocol_error_trace_action='ALERT'
*.sessions=335
saqdb2.thread=2
saqdb1.thread=1
saqdb1.undo_tablespace='UNDOTBS1'
saqdb2.undo_tablespace='UNDOTBS2'
 -------------------------------------------------------------------------------------------------- 


Note Following
1. control_files is commented out because this will be added later once controlfile will be created during database creation.
2. cluster_database is set to FALSE. It will set to TRUE in the last stages of this exercise.

Add following entries in TNS files of both nodes

LISTENER_SAQDB2 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))


LISTENER_SAQDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))

LISTENERS_SAQDB =
  (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))
  )

SAQDB1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))
    (CONNECT_DATA =
    (FAILOVER = true)
      (SERVER = DEDICATED)
      (SERVICE_NAME = COMFORT)
      (INSTANCE_NAME = COMFORT1)
      (failover_mode = (BACKUP=COMFORT2)(type=select)(method=basic)(RETRIES=20)(DELAY=3))
    )
  )

SAQDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))
    (FAILOVER = true)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = COMFORT)
      (INSTANCE_NAME = COMFORT2)
      (failover_mode = (BACKUP=COMFORT1)(type=select)(method=basic)(RETRIES=20)(DELAY=3))
    )
  )

SAQDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA01-VIP)(PORT = 1522))
    (ADDRESS = (PROTOCOL = TCP)(HOST = PLPORA02-VIP)(PORT = 1522))
    (LOAD_BALANCE = yes)
    (FAILOVER = true)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = COMFORT)
      (failover_mode = (type=select)(method=basic)(RETRIES=20)(DELAY=3))
    )
  )

On rac1, invoke SQLPLUS and startup the instance in nomount state
$export ORACLE_SID=saqdb1
SQL>startup nomount
Execute following command to create the database.
CREATE DATABASE "saqdb" MAXINSTANCES 32MAXLOGFILES 192MAXLOGMEMBERS 3MAXDATAFILES 1024DATAFILE SIZE 300M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITEDEXTENT MANAGEMENT LOCALSYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  10240K MAXSIZE UNLIMITEDSMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITEDSMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITEDCHARACTER SET WE8MSWIN1252NATIONAL CHARACTER SET AL16UTF16LOGFILE GROUP 1  SIZE 51200K,GROUP 2  SIZE 51200K,GROUP 3  SIZE 51200K;
 
Now use “asmcmd” cmmand to find out the name of the control file and update it in the init.ora file you created previously.
[rac1]$ asmcmd
ASMCMD> ls
DATA/
RCV/
ASMCMD> cd rcv
ASMCMD> ls
SAQDB/
MYDB/
YOURDB/
ASMCMD> cd saqdb
ASMCMD> ls
CONTROLFILE/
ONLINELOG/
ASMCMD> cd controlfile
ASMCMD> ls
Current.3458.757175825
ASMCMD>

Shutdown and startup the instance to let control_files init parameter take effect.
shutdown immediate
startup
Log in as SYS and create USERS tablespace
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE SIZE 5M AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT  AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";

Log in as SYS and create run following scripts to create catalog, pl/sql support and to build other required structures.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catalog.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catblock.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catproc.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catoctk.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/owminst.plb;
Connect as SYSTEM user and execute following script
@/u01/app/oracle/product/11.1.0/db_1/sqlplus/admin/pupbld.sql;
Exit from the session
Connect as SYSTEM user and execute following script
@/u01/app/oracle/product/11.1.0/db_1/sqlplus/admin/help/hlpbld.sql helpus.sql;
Connect as SYS and execute following scripts to add java support. @/u01/app/oracle/product/11.1.0/db_1/javavm/install/initjvm.sql;
@/u01/app/oracle/product/11.1.0/db_1/xdk/admin/initxml.sql;
@/u01/app/oracle/product/11.1.0/db_1/xdk/admin/xmlja.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catjava.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catexf.sql;
Connect as SYS and execute following scripts to add XML DB support.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catqm.sql change_on_install SYSAUX TEMP;
Exit from the session
Connect as SYS and execute following remaining scripts to add XML DB support.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catxdbj.sql;
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catrul.sql;
Connect as SYS and execute following script to install ORDINST schema.
@/u01/app/oracle/product/11.1.0/db_1/ord/admin/ordinst.sql SYSAUX SYSAUX;


Connect as SYS and execute following script to install Intermedia.
@/u01/app/oracle/product/11.1.0/db_1/ord/im/admin/iminst.sql;


Connect as SYS and execute following script to install APEX.
@/u01/app/oracle/product/11.1.0/db_1/apex/catapx.sql change_on_install SYSAUX SYSAUX TEMP /i/ NONE;


Connect as SYS and execute following script to install Cluster Views.
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catclust.sql;

Connect as SYS and execute following PL/SQL bock to lock all the users account except SYS and SYSTEM.
BEGIN
 FOR item IN ( SELECT USERNAME FROM DBA_USERS WHERE USERNAME NOT IN (
'SYS','SYSTEM') )
 LOOP
  dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
  execute immediate 'alter user ' || item.USERNAME || ' password expire account lock' ;
 END LOOP;
END;
/


Recompile all objects.
select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
execute utl_recomp.recomp_serial();
select 'utl_recomp_end: ' || to_char(sysdate, 'HH:MI:SS') from dual;


Run following to check whether any component is invalid and correct the problem before moving forward hereafter.
col comp_id format a15
col comp_name format a50
col version format a12
SELECT comp_id
      ,comp_name
      ,version
      ,status
  FROM dba_server_registry;

Create spfile on the ASM disk group
CREATE SPFILE='+DATA/saqdb/spfilesaqdb.ora' FROM PFILE;

Shutdown and then startup the database with spfile.
 
Now database is creation has been completed. Next steps will be to make it a RAC database and register with CRS.
Login using SQLPLUS as user SYS and turn on the cluster database parameter
ALTER SYSTEM SET cluster_database=true SCOPE=SPFILE;
Create undo tablespace for second instance
CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 200M AUTOEXTEND ON NEXT 10M;
Add thread 2 redo log files for the second instance.
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 SIZE 50M, GROUP 5  SIZE 50M, GROUP 6 SIZE 50M;
 
Enable redo log thread 2
ALTER DATABASE ENABLE PUBLIC THREAD 2;
Now on rac2 node, set environment variables for instance saqdb2 and start it.
startup
From any node, use srvctl utility to register this new database and its instance with the CRSsrvctl add database -d saqdb -o $ORACLE_HOME
srvctl add instance -d saqdb -i saqdb1 -n rac1
srvctl add instance -d saqdb -i saqdb2 -n rac2
srvctl enable database -d saqdb
srvctl enable instance -d saqdb -i saqdb1
srvctl enable instance -d saqdb -i saqdb2


Cleanly shutdown the instance on both nodes using SQLPLUS and then start them using srvctl. Now you can see the status of this new database and instances using crs_stat command. After shutting down instances manually, execute following from any node.
srvctl start database -d saqdb
srvctl status database -d saqdb
Instance saqdb1 is running on node rac1
Instance saqdb2 is running on node rac2
Tags:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Popular Posts - All Times