1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle RAC Database manual creation

Discussion in 'Server Administration and Options' started by basavraju, Jul 8, 2011.

  1. basavraju

    basavraju Guest

    Creating Database manually under oracle RAC

    Login into node1 as oracle user and set environment as follows

    Code (Text):
    [oracle@node1 ~]$ . oraenv
    ORACLE_SID = [oracle] ? test1
    ORACLE_HOME = [/home/oracle] ? /orasoft/10g
    Create the directory structure to keep alert log file and trace files

    Code (Text):
    [oracle@node1 ~]$ mkdir /orasoft/test
    [oracle@node1 ~]$ mkdir /orasoft/test/adump
    [oracle@node1 ~]$ mkdir /orasoft/test/bdump
    [oracle@node1 ~]$ mkdir /orasoft/test/cdump
    [oracle@node1 ~]$ mkdir /orasoft/test/udump
    [oracle@node1 ~]$
    Create pfile with the following parameters

    Code (Text):
    [oracle@node1 ~]$ vi /orasoft/10g/dbs/inittest1.ora
    [oracle@node1 ~]$ cat /orasoft/10g/dbs/inittest1.ora
    db_cache_size=402653184
    java_pool_size=4194304
    large_pool_size=4194304
    shared_pool_size=184549376
    streams_pool_size=0
    *.audit_file_dest='/orasoft/test/adump'
    *.background_dump_dest='/orasoft/test/bdump'
    *.compatible='10.2.0.1.0'
    *.control_files='+DG1'
    *.core_dump_dest='/orasoft/test/cdump'
    *.db_block_size=8192
    *.db_create_file_dest='+DG1'
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='test'
    *.job_queue_processes=10
    *.open_cursors=300
    *.pga_aggregate_target=200278016
    *.processes=150
    *.remote_login_passwordfile='exclusive'
    *.sga_target=600834048
    *.undo_management='AUTO'
    undo_tablespace='UNDOTBS1'
    *.user_dump_dest='/orasoft/test/udump'
    [oracle@node1 ~]$
    Connect to oracle and startup the instance in nomount

    Code (Text):
    [oracle@node1 ~]$ sqlplus / as sysdba
    SQL Plus comes up.

    Code (SQL):
    SQL*Plus: Release 10.2.0.4.0 - Production ON Wed Apr 20 11:24:53 2011

    Copyright (c) 1982, 2007, Oracle.  ALL Rights Reserved.

    Connected TO an idle instance.

    SQL> startup nomount;
    ORACLE instance started.

    Total System Global Area  603979776 bytes
    Fixed SIZE                  1268872 bytes
    Variable SIZE             192938872 bytes
    DATABASE Buffers          402653184 bytes
    Redo Buffers                7118848 bytes
    SQL>
     
    Create the database as follows

    Code (SQL):
    SQL> CREATE DATABASE test
      2  undo tablespace undotbs1
      3  DEFAULT TEMPORARY tablespace tmp1;

    DATABASE created.
     
    Run the following scripts

    Code (SQL):
    SQL> @/orasoft/10g/rdbms/admin/catalog.SQL;
    SQL> @/orasoft/10g/rdbms/admin/catproc.SQL;
    SQL> @/orasoft/10g/rdbms/admin/catclust.SQL;
    SQL> conn system/manager
    Connected.
    SQL> @/orasoft/10g/sqlplus/admin/pupbld.SQL;

    SQL> SELECT name FROM v$controlfile;

    NAME
    --------------------------------------------------------------------------------
    +DG1/test/controlfile/CURRENT.323.748956375

    SQL> shutdown immediate;
    DATABASE closed.
    DATABASE dismounted.
    ORACLE instance shut down.
    SQL>
    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
    [oracle@node1 ~]$ cd /orasoft/10g/dbs/
    Open the pfile and modify control_files parameter with above controlfile Value

    Create password file

    Code (Text):
    [oracle@node1 dbs]$ orapwd file=orapwtest1 password=oracle
    [oracle@node1 dbs]$
    Open tnsnames.ora file and add the following alias for remote_listener Parameter

    Code (Text):
    [oracle@node1 dbs]$ vi /orasoft/10g/network/admin/tnsnames.ora
    Add the following entry in both nodes

    Code (Text):
    LISTENERS_TEST =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = node1-vip)(PORT = 1521))
        (ADDRESS = (PROTOCOL = TCP)(HOST = node2-vip)(PORT = 1521))
      )
    Open pfile and add remote_listener parameter
    And also add remaining parameters for cluster database and for instance 2

    Code (Text):
     [oracle@node1 dbs]$ cat inittest1.ora
    db_cache_size=402653184
    java_pool_size=4194304
    large_pool_size=4194304
    shared_pool_size=184549376
    streams_pool_size=0
    *.audit_file_dest='/orasoft/test/adump'
    *.background_dump_dest='/orasoft/test/bdump'
    *.compatible='10.2.0.1.0'
    *.control_files='+DG1/test/controlfile/current.323.748956375'
    *.core_dump_dest='/orasoft/test/cdump'
    *.db_block_size=8192
    *.db_create_file_dest='+DG1'
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='test'
    *.job_queue_processes=10
    *.open_cursors=300
    *.pga_aggregate_target=200278016
    *.processes=150
    *.remote_login_passwordfile='exclusive'
    *.sga_target=600834048
    *.undo_management='AUTO'
    test1.undo_tablespace='UNDOTBS1'
    *.user_dump_dest='/orasoft/test/udump'
    cluster_database=true
    cluster_database_instances = 2
    test2.undo_tablespace = 'UNDOTBS2'
    test1.instance_number = 1
    test2.instance_number = 2
    test1.thread = 1
    test2.thread = 2
    remote_listener = 'LISTENERS_TEST'
    [oracle@node1 dbs]$
    Startup the database and create second undo tablespace , thread 2 for
    Instance 2

    [oracle@node1 ~]$ export ORACLE_SID=test1
    [oracle@node1 ~]$ sqlplus / as sysdba

    SQL*Plus: Release 10.2.0.4.0 - Production on Wed Apr 20 11:49:32 2011

    Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

    Connected to an idle instance.
    Carry the following commands

    Code (SQL):
    SQL> startup;
    ORACLE instance started.

    Total System Global Area  603979776 bytes
    Fixed SIZE                  1268872 bytes
    Variable SIZE             192938872 bytes
    DATABASE Buffers          402653184 bytes
    Redo Buffers                7118848 bytes
    DATABASE mounted.
    DATABASE opened.
    SQL>

    SQL> CREATE undo tablespace undotbs2;

    Tablespace created.

    SQL> ALTER DATABASE ADD logfile thread 2;

    DATABASE altered.

    SQL> ALTER DATABASE ADD logfile thread 2;

    DATABASE altered.

    SQL>

    SQL> ALTER DATABASE enable thread 2;

    DATABASE altered.

    SQL>
    After adding and enabling thread 2 shutdown the database.

    Code (SQL):
    SQL> shut immediate;
    DATABASE closed.
    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
    [oracle@node1 ~]$
    Code (Text):
    [oracle@node1 ~]$ crs_stat -t
    Name           Type           Target    State     Host        
    ------------------------------------------------------------
    ora....SM1.asm application    ONLINE    ONLINE    node1      
    ora....E1.lsnr application    ONLINE    ONLINE    node1      
    ora.node1.gsd  application    ONLINE    ONLINE    node1      
    ora.node1.ons  application    ONLINE    ONLINE    node1      
    ora.node1.vip  application    ONLINE    ONLINE    node1      
    ora....SM2.asm application    ONLINE    ONLINE    node2      
    ora....E2.lsnr application    ONLINE    ONLINE    node2      
    ora.node2.gsd  application    ONLINE    ONLINE    node2      
    ora.node2.ons  application    ONLINE    ONLINE    node2      
    ora.node2.vip  application    ONLINE    ONLINE    node2      
     
    Transfer pfile and password file to second node

    Code (Text):
    [oracle@node1 ~]$ scp /orasoft/10g/dbs/inittest1.ora [email]oracle@node2:/orasoft/10g/dbs/inittest2.ora[/email]
    inittest1.ora                                100%  909     0.9KB/s   00:00    
    [oracle@node1 ~]$ scp /orasoft/10g/dbs/orapwtest1 oracle@node2:/orasoft/10g/dbs/orapwtest2
    orapwtest1                                   100% 1536     1.5KB/s   00:00    
    [oracle@node1 ~]$
     
    Login into second node as oracle user and create the directory structure For alert log and trace files. Edit tnsnames.ora file and keep the alias for Remote_listener parameter.

    Code (Text):
    [oracle@node1 ~]$ ssh node2
    Last login: Wed Apr 20 14:30:19 2011 from node1
    [oracle@node2 ~]$ mkdir /orasoft/test
    [oracle@node2 ~]$ mkdir /orasoft/test/adump
    [oracle@node2 ~]$ mkdir /orasoft/test/bdump
    [oracle@node2 ~]$ mkdir /orasoft/test/cdump
    [oracle@node2 ~]$ mkdir /orasoft/test/udump
    [oracle@node2 ~]$
     
    Using srvctl command add database and instances to OCR

    Code (Text):
    [oracle@node1 ~]$ srvctl add database -d test -o /orasoft/10g
    [oracle@node1 ~]$ srvctl add instance -d test -i test1 -n node1
    [oracle@node1 ~]$ srvctl add instance -d test -i test2 -n node2
    [oracle@node1 ~]$

    [oracle@node1 ~]$ crs_stat -t
    Name           Type           Target    State     Host        
    ------------------------------------------------------------
     
    ora....SM1.asm application    ONLINE    ONLINE    node1      
    ora....E1.lsnr application    ONLINE    ONLINE    node1      
    ora.node1.gsd  application    ONLINE    ONLINE    node1      
    ora.node1.ons  application    ONLINE    ONLINE    node1      
    ora.node1.vip  application    ONLINE    ONLINE    node1      
    ora....SM2.asm application    ONLINE    ONLINE    node2      
    ora....E2.lsnr application    ONLINE    ONLINE    node2      
    ora.node2.gsd  application    ONLINE    ONLINE    node2      
    ora.node2.ons  application    ONLINE    ONLINE    node2      
    ora.node2.vip  application    ONLINE    ONLINE    node2      
    ora.test.db    application    OFFLINE   OFFLINE              
    ora....t1.inst application    OFFLINE   OFFLINE              
    ora....t2.inst application    OFFLINE   OFFLINE              
    [oracle@node1 ~]$

     
    Now start the database

    Code (Text):
    [oracle@node1 ~]$ srvctl start database -d test

     [oracle@node1 ~]$ crs_stat -t
    Name           Type           Target    State     Host        
    ------------------------------------------------------------
    ora.dev.db     application    OFFLINE   OFFLINE              
    ora....v1.inst application    OFFLINE   OFFLINE              
    ora....v2.inst application    OFFLINE   OFFLINE              
    ora....SM1.asm application    ONLINE    ONLINE    node1      
    ora....E1.lsnr application    ONLINE    ONLINE    node1      
    ora.node1.gsd  application    ONLINE    ONLINE    node1      
    ora.node1.ons  application    ONLINE    ONLINE    node1      
    ora.node1.vip  application    ONLINE    ONLINE    node1      
    ora....SM2.asm application    ONLINE    ONLINE    node2      
    ora....E2.lsnr application    ONLINE    ONLINE    node2      
    ora.node2.gsd  application    ONLINE    ONLINE    node2      
    ora.node2.ons  application    ONLINE    ONLINE    node2      
    ora.node2.vip  application    ONLINE    ONLINE    node2      
    ora.test.db    application    ONLINE    ONLINE    node2      
    ora....t1.inst application    ONLINE    ONLINE    node1      
    ora....t2.inst application    ONLINE    ONLINE    node2      
    [oracle@node1 ~]$
     
  2. harsh

    harsh Guest

    Nice Article...