Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 11 September 2012

Manually Creating Oracle 9i RAC Database

Oracle Corporation strongly recommends create the database through DBCA. You can create your database manually if you already have scripts, or if you have database requirements that differ greatly from the types of databases that the DBCA. You have to perform following task before actual creation of Oracle RAC database manually.
Overview: Consider in below example that you are already having ASM installation. For more about ASM installation check the earlier post and database is name is DB, instances are db1 and db2, hostnames are Clust1 and Clust2. The document is prepared by keeping both the platform in mind either Linux as well as windows.
Install Oracle Products: Run the OUI as per the platform specific documents and at the installation type select “Software only” Your installation should proceed automatically a RAC database.
Steps for Manual Creation of RAC database:                                                
Step1: Back Up Existing Databases
Step 2: Determine the Database and Instance Parameter Settings
Step 3: Create the Real Application Clusters Configuration with SRVCTL
Step 4: Configure the oratab File on UNIX
Step 5: Set ORACLE_SID for Each Node’s Instance
Step 6: Create the Server Parameter File
Step 7: Create the Password Files
Step 8: Prepare a CREATE DATABASE Script for the Cluster Database
Step 9: Create the Database.
Step 10: Back Up the Database
Step 11: Configure Oracle Net on All Nodes.
Backup Existing Database:
Oracle strongly recommends that you make complete backups of all databases before creating a new database in case database creation accidentally affects existing files. Your backups should include parameter files, datafiles, redolog files, control files, and network files.
Database and Instance Parameter Settings:
In RAC, each node typically has one instance. It should be easy to setup these parameters for you if you are aware of Basic Clustered database information:
Create the RAC Configuration with SRVCTL:
Run the SRVCTL Utility to create the configuration with srvconfig –init then start Global Services Daemon (GSD) on each node with the gsdctlstart command so that SRVCTL can access your cluster configuration information. Then execute the srvctladd command so that Server Management (SRVM) knows what instances belong to your cluster database.
For Example (If you are using Spfile)
srvctl add database -d db_name -m db_domain -o oracle_home -s spfile_name
If your database does not have a domain name, then do not specify the -m option. If you do not use the spfile, then do not specify the -s option. Then for each instance enter the command:
srvctl add instance -d db_name -i sid -n node
Configure the oratab File on LINUX:
To use Oracle Enterprise Manager (OEM), manually create an entry in the oratab file on each node: db_name:$ORACLE_HOME:N
[oracle@clust1 dbs]$ vi /etc/oratab  
For Example:
db1:/u01/app/oracle/product/9.2.0/db1:N  
Also in node clust2 /etc/oratab  
db2:/u01/app/oracle/product/9.2.0/db1:N
Where db_name is your database name ORACLE_HOME is the directory path to the database N indicates that the database should not be started at restart time.
Setting ORACLE_SID for Each Node’s Instance:
The SID must be defined for each node’s instance in the cluster database, and the value you set for ORACLE_SID must be unique for each instance. Oracle recommends you use SIDs that consists of the database name as the common base and the number of the thread assigned to the instance that you specified. For example, if db is the database name, then the first instance in the cluster has a SID of db1 and the second instance has a SID of db2.
Note: On Linux Set the ORACLE_SID environment variable or on Windows create an ORACLE_SID registry key under: HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\[HOMEID] then set the value of your instance SID in the ORACLE_SID registry key.
After creating SID, create an oracle service for each node. On each node, use the CRTSRV batch file to create a unique service corresponding to the instance on the node.
C:\%ORACLE_HOME%\bin\crtsrv.bat sid
For example, to create a service for a SID of db1, OracleServicedb1, enter the following:
C:\%ORACLE_HOME%\bin\crtsrv.bat db1
Run this command to verify the existence of oracle services:
C:\net start db1
Create the PFILE/SPFILE:
Oracle Corporation recommends that you use a single server parameter file to designate both global and instance-specific settings. Using single parameter file will simplifies the parameter administration.
To designate instance-specific settings in this file, use the SID prefix and either place these entries after the generic, global entries or create separate parameter file for that. You must specify instance-specific settings using the syntax: instance_name.parameter_ name=value
Alternatively you can edit the following parameters to prepare new Pfile:
1. EDIT Global_Entries:
BACKGROUND_DUMP_DEST , CONTROL_FILES, DB_DOMAIN, DB_NAME, DISPATCHERS, REMOTE_LISTENERS, SERVICE_NAMES, USER_DUMP_DEST, UNDO_MANAGEMENT
2. EDIT Instance specific Parameters to turn single instance DB to RAC DB
INSTANCE_NAME, INSTANCE_NUMBER, UNDO_TABLESPACE or ROLLBACK_SEGMENTS , THREAD, LOCAL_LISTENER
*.cluster_database_instances=2
*.cluster_database=true
DB1.instance_number=1
DB2.instance_number=2
DB2.thread=2
DB1.thread=1
*.undo_management='AUTO'
DB1.undo_tablespace='UNDOTBS1'
DB2.undo_tablespace='UNDOTBS2'
DB1.instance_name = DB1
DB1.instance_name = DB2
3. Ensure or Edit the REMOTE LOGIN PASSWORD is set to EXLUSIVE
Create the Password Files:
Use the Password Utility ORAPWD to create password files on each node (Clust1, Clust2
On UNIX:
orapwd file=$ORACLE_HOME/dbs/ORAPW$ORACLE_SID password=password
On Windows:
ORAPWD file=%ORACLE_HOME%\database\pwd%ORACLE_SID%.ora password=password
Example:
orapwd file=orapwDB1 password=oracle entries=5
Where FILE specifies the password file name and PASSWORD sets the password for the SYS account.
Prepare Script to Create RAC database:
Prepare a CREATE DATABASE script on one of the nodes by using the clustdb.sql sample script, located in the $ORACLE_HOME/srvm/admin directory on LINUX or in the %ORACLE_HOME%\srvm\admin directory on Windows. The sample script is for a two-node cluster. If you use the sample script, edit the following:
        Set PFILE to point to the location of the initdb_name.ora file.
        Modify oracle in the CONNECT SYS/oracle AS SYSDBA line to use the password you created.
        Modify the location of the data dictionary scripts, $ORACLE_ HOME/rdbms/admin on UNIX and %ORACLE_HOME%\rdms\admin on Windows, to reflect the Oracle home you specified.
        Modify the log file and datafile names with the file names or symbolic link names you created.
        Modify the log file and datafile sizes.
        If you are not using automatic undo management, then create enough private rollback segments for the number of concurrent users for each transaction. Oracle requires at least two rollback segments for each instance. With the exception of the SYSTEM rollback segment, instances cannot share public rollback segments. An instance explicitly acquires private rollback segments when it opens a database.
CREATE DATABASE DB
MAXINSTANCES  8
MAXLOGHISTORY 100
MAXLOGFILES   64
MAXLOGMEMBERS 3
MAXDATAFILES 150
DATAFILE SIZE 300M AUTOEXTEND ON NEXT 10240K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 200M AUTOEXTEND ON NEXT 10240K MAXSIZE 800M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
CHARACTER SET AR8MSWIN1256
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M,
GROUP 3 SIZE 50M
USER SYS IDENTIFIED BY "sysDB"
USER SYSTEM IDENTIFIED BY "systemDB";
Execute the Script to Create the RAC Database:
Now start the database with the help of edited pfile in nomount phase and Run the Create database script in SQL prompt.
SQL>@path/clustdb.sql;
Note: Once that command has finished, Enter "show parameter control" to get the location of the control file and add this to your init.ora file. If you do not do this, the database will not mount after the next bounce.

You should create a user tablespace and make it the database default tablespace to avoid objects stored in SYSTEM or SYSAUX. 
SQL> create tablespace users datafile 10M;
Tablespace created
SQL> Alter database default tablespace users; 
Database altered 
Turn Single instance database to RAC database:
Edit initDB1.ora and add the cluster parameters as mentioned above:
*.cluster_database_instances=2
*.cluster_database=true
DB1.instance_number=1
DB2.instance_number=2
DB2.thread=2
DB1.thread=1
*.undo_management='AUTO'
DB1.undo_tablespace='UNDOTBS1'
DB2.undo_tablespace='UNDOTBS2'
DB1.instance_name = DB1
DB1.instance_name = DB2
Again copy initDB1.ora on second node and rename it initDB2.ora
Now create the second undo tablespace:
SQL> Create undo tablespace undotbs2 datafile size 200M;
Tablespace created 
Create the second instance's redo log thread:
SQL> alter database add logfile thread 2
      group 4 size 50M,
      group 5 size 50M,
      group 6 size 50M;
Database altered 
Restart the database. It will be mounted now; check the alert.log there should be message stating that database is mounted in shared mode.
Now activate the 2nd redo log thread:          
SQL> alter database enable public thread 2;
Note: You must having proper tnsname.ora, password file and oratab entry on both the node.
Configure Oracle Net on All Nodes:
Now Configure the listener.ora, sqlnet.ora, and tnsnames.ora files. To configure the listener.ora you must Includes addresses of each network listener on a server, the SID of the databases for which they listen, and various control parameters used by the listener.
Run Data Dictionary Scripts:
@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catclust.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Note: finally run the above script. You must have SYSDBA privileges to run the above script.
Check v$active_instances to confirm your both the instance are active or running and finally create spfile from pfile.
SQL> create spfile='+data/DB/spfileDB.ora' from pfile;
Now Shutdown both instances and register the database and its instances with Clusterware:
srvctl add instance -d DB -i DB1 -n node1
srvctl add instance -d DB -i DB2 -n node2
srvctl start database -d DB
Back Up the Database:
Make a full backup of the database to ensure you have a complete set of files from which to recover in case of media failure.
References:

1 comments:

  1. TeethNightGuard is offering personalized fitting and high quality customized dental guards.

    ReplyDelete