Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Loading...

Thursday, September 3, 2015

Creating database using dbca -silent

How to create a database using dbca -silent ..

The bare minimum configuration is :

[oracle@collabn1 oracle]$ dbca -silent -gdbname SHAIKDB.shaiksameer -sid SHAIKDB -datafileDestination /u01/app/oracle/SHAIKDB -createdatabase -templatename General_Purpose.dbc
Enter SYS user password:

Enter SYSTEM user password:

Copying database files
1% complete
3% complete
35% complete
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SHAIKDB/SHAIKDB0.log" for further details

or you can provide more info as below:

[oracle@collabn1 ~]$ dbca -silent -createdatabase -templatename General_Purpose.dbc -gdbname SHAIKDB.shaiksameer  -sid SHAIKDB -syspassword test123 -systempassword test123 -emconfiguration none   -datafiledestination /u01/app/oracle/SHAIKDB -recoveryAreaDestination /u01/app/oracle/SHAIKDB/fra  -memorypercentage 30 -redologfilesize 100


Copying database files
1% complete
3% complete
35% complete
Creating and starting Oracle instance
Creating and starting Oracle instance
37% complete
42% complete
47% complete
52% complete
53% complete
56% complete
58% complete
Registering database with Oracle Restart
64% complete
Completing Database Creation
68% complete
71% complete
75% complete
85% complete
96% complete
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/SHAIKDB/SHAIKDB.log" for further details.


[oracle@collabn1 ~]$ env | grep ORA
ORACLE_SID=SHAIKDB
ORACLE_BASE=/u01/app/oracle
ORAENV_ASK=NO
ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/SHAIKPROD

SHAIKDB>select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


select comp_name,version,status from dba_registry;

COMP_NAME                 VERSION            STATUS
---------------------------------------- ------------------------------ -----------
OWB                     11.2.0.1.0            VALID
Oracle Application Express         3.2.1.00.10            VALID
Oracle Enterprise Manager         11.2.0.1.0            VALID
OLAP Catalog                 11.2.0.1.0            VALID
Spatial                  11.2.0.1.0            VALID
Oracle Multimedia             11.2.0.1.0            VALID
Oracle XML Database             11.2.0.1.0            VALID
Oracle Text                 11.2.0.1.0            VALID
Oracle Expression Filter         11.2.0.1.0            VALID
Oracle Rules Manager             11.2.0.1.0            VALID
Oracle Workspace Manager         11.2.0.1.0            VALID
Oracle Database Catalog Views         11.2.0.1.0            VALID
Oracle Database Packages and Types     11.2.0.1.0            VALID
JServer JAVA Virtual Machine         11.2.0.1.0            VALID
Oracle XDK                 11.2.0.1.0            VALID
Oracle Database Java Packages         11.2.0.1.0            VALID
OLAP Analytic Workspace          11.2.0.1.0            VALID
Oracle OLAP API              11.2.0.1.0            VALID

18 rows selected.

dbca -h will give you all the available options:

dbca -h
dbca  [-silent | -progressOnly | -customCreate] {<command> <options> }  | { [<command> [options] ] -responseFile  <response file > } [-continueOnNonFatalErrors <true | false>]
Please refer to the manual for details.
You can enter one of the following command:

Create a database by specifying the following parameters:
    -createDatabase
        -templateName <name of an existing  template>
        [-cloneTemplate]
        -gdbName <global database name>
        [-policyManaged | -adminManaged <Policy managed or Admin managed Database, default is Admin managed database>]
            [-createServerPool <To create ServerPool which will be used by the database to be created>]
            [-force <To create serverpool by force when adequate free servers are not available. This may affect already running database>]
            -serverPoolName <One serverPool Name in case of create server pool and comma separated list of serverPool name in case of use serverpool>
            -[cardinality <Specify cardinality for new serverPool to be created, default is the number of qualified nodes>]
        [-sid <database system identifier prefix>]
        [-sysPassword <SYS user password>]
        [-systemPassword <SYSTEM user password>]
        [-emConfiguration <CENTRAL|LOCAL|ALL|NOBACKUP|NOEMAIL|NONE>
            -dbsnmpPassword <DBSNMP user password>
            -sysmanPassword <SYSMAN user password>
            [-hostUserName <Host user name for EM backup job>
             -hostUserPassword <Host user password for EM backup job>
             -backupSchedule <Daily backup schedule in the form of hh:mm>]
            [-smtpServer <Outgoing mail (SMTP) server for email notifications>
             -emailAddress <Email address for email notifications>]
            [-centralAgent <Enterprise Manager central agent home>]]
        [-disableSecurityConfiguration <ALL|AUDIT|PASSWORD_PROFILE|NONE>
        [-datafileDestination <destination directory for all database files> |  -datafileNames <a text file containing database objects such as controlfiles, tablespaces, redo log files and spfile to their corresponding raw device file names mappings in name=value format.>]
        [-redoLogFileSize <size of each redo log file in megabytes>]
        [-recoveryAreaDestination <destination directory for all recovery files>]
        [-datafileJarLocation  <location of the data file jar, used only for clone database creation>]
        [-storageType < CFS | ASM >
            [-asmsnmpPassword     <ASMSNMP password for ASM monitoring>]
             -diskGroupName   <database area disk group name>
             -recoveryGroupName       <recovery area disk group name>
        [-nodelist <node names separated by comma for the database>]
        [-characterSet <character set for the database>]
        [-nationalCharacterSet  <national character set for the database>]
        [-registerWithDirService <true | false>
            -dirServiceUserName    <user name for directory service>
            -dirServicePassword    <password for directory service >
            -walletPassword    <password for database wallet >]
        [-listeners  <list of listeners to configure the database with>]
        [-variablesFile   <file name for the variable-value pair for variables in the template>]]
        [-variables  <comma seperated list of name=value pairs>]
        [-initParams <comma seperated list of name=value pairs>]
        [-memoryPercentage <percentage of physical memory for Oracle>]
        [-automaticMemoryManagement ]
        [-totalMemory <memory allocated for Oracle in MB>]
        [-databaseType <MULTIPURPOSE|DATA_WAREHOUSING|OLTP>]]


Create Database using SQLPLUS


How to create a 11gR2 database using SQLPLUS.

Create directories:

rm -rf /u01/app/oracle/SHAIKDB
mkdir -p /u01/app/oracle/SHAIKDB/fra
mkdir -p /u01/app/oracle/SHAIKDB/adump
mkdir -p /u01/app/oracle/SHAIKDB/

cd $ORACLE_HOME/dbs
cp init.ora initSHAIKDB.ora

Change the directory locations and DBNAME.

vi initSHAIKDB.ora
db_name='SHAIKDB'
memory_target=450m
processes = 150
audit_file_dest='/u01/app/oracle/SHAIKDB/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/SHAIKDB/fra'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle/SHAIKDB'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u01/app/oracle/SHAIKDB/control01.ctl','/u01/app/oracle/SHAIKDB/control02.ctl'
compatible ='11.2.0'

Create DB creation script:

create database SHAIKDB
 user sys identified by test1
  user system identified by test1
logfile group 1 ('/u01/app/oracle/SHAIKDB/redo1.log','/u01/app/oracle/SHAIKDB/redo2.log') size 100m,
group 2 ('/u01/app/oracle/SHAIKDB/redo3.log','/u01/app/oracle/SHAIKDB/redo4.log') size 100m,
group 3 ('/u01/app/oracle/SHAIKDB/redo5.log','/u01/app/oracle/SHAIKDB/redo6.log') size 100m
character set al32utf8
national character set al16utf16
datafile '/u01/app/oracle/SHAIKDB/sys01.dbf' size 100m autoextend on maxsize unlimited
sysaux datafile '/u01/app/oracle/SHAIKDB/sysaux01.dbf' size 100m autoextend on maxsize unlimited
default tablespace users datafile '/u01/app/oracle/SHAIKDB/users01.dbf' size 100m autoextend on maxsize unlimited
default temporary tablespace temp tempfile '/u01/app/oracle/SHAIKDB/temp01.dbf' size 100m autoextend on maxsize unlimited
undo tablespace undotbs1 datafile '/u01/app/oracle/SHAIKDB/undo01.dbf' size 100m autoextend on maxsize unlimited;


vi postdb_create.sql
@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/sqlplus/admin/pupbld.sql



[oracle@collabn1 oracle]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Sep 3 08:15:12 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size            2214456 bytes
Variable Size          301991368 bytes
Database Buffers      163577856 bytes
Redo Buffers            4046848 bytes
SQL> @createdb.sql

--
---
---
database created.




SQL>@postdbcreate.sql
--
--

Synonym created.

SQL>
SQL> -- End of pupbld.sql



SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production


COMP_NAME                 STATUS           VERSION
---------------------------------------- -------------------- ------------------------------
Oracle Database Catalog Views         VALID              11.2.0.1.0
Oracle Database Packages and Types     VALID              11.2.0.1.0

Tuesday, August 25, 2015

Oracle Fast Start Failover

Enable & Test Fast Start Failover:


[oracle@collabn2 admin]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/XXXXXX
Connected.
DGMGRL> show configuration

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

For FastStartFailover set the property FastStartFailoverTarget :

DGMGRL> show database 'orcl' FastStartFailoverTarget                              
 FastStartFailoverTarget = ''
DGMGRL> show database 'msft' FastStartFailoverTarget
 FastStartFailoverTarget = ''

DGMGRL> edit database 'orcl' set property FastStartFailoverTarget=msft;
Property "faststartfailovertarget" updated
DGMGRL> edit database 'msft' set property FastStartFailoverTarget=orcl;
Property "faststartfailovertarget" updated

DGMGRL> show database 'msft' FastStartFailoverTarget
 FastStartFailoverTarget = 'orcl'
DGMGRL> show database 'orcl' FastStartFailoverTarget
 FastStartFailoverTarget = 'msft'
DGMGRL>

Verify LogXptMode is SYNC:
DGMGRL> show database 'orcl' LogXptMode
 LogXptMode = 'SYNC'
DGMGRL> show database 'msft' LogXptMode
 LogXptMode = 'SYNC'

Start Observer:  <- Remember after you start the observer CTRL will not be returned

DGMGRL>
DGMGRL> start observer
Observer started

Enable FAST START FAILOVER:

DGMGRL> show configuration

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS


DGMGRL> show configuration verbose

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    msft - Primary database
    orcl - (*) Physical standby database

 (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           orcl
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


DGMGRL> show fast_start failover

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           orcl
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configurable Failover Conditions
 Health Conditions:
    Corrupted Controlfile          YES
    Corrupted Dictionary           YES
    Inaccessible Logfile            NO
    Stuck Archiver                  NO
    Datafile Offline               YES

 Oracle Error Conditions:
    (none)



Terminate the primary to initiate the Fast-Start Failover:


[oracle@collabn2 ~]$ pgrep -lf "smon.*msft"
31665 ora_smon_msft

[oracle@collabn2 ~]$ pgrep -f  "smon.*msft" | kill -9

[oracle@collabn2 ~]$ pgrep -lf "smon.*msft"

In Alert_log:
Tue Aug 25 11:54:46 2015
PMON (ospid: 31629): terminating the instance due to error 474
Tue Aug 25 11:54:46 2015
System state dump is made for local instance
System State dumped to trace file /u01/app/oracle/diag/rdbms/msft/msft/trace/msft_diag_31637.trc
Trace dumping is performing id=[cdmp_20150825115446]
Termination issued to instance processes. Waiting for the processes to exit
Tue Aug 25 11:54:56 2015
Instance termination failed to kill one or more processes
Instance terminated by PMON, pid = 31629


DGMGRL>
DGMGRL> start observer
Observer started

11:55:17.29  Tuesday, August 25, 2015
Initiating Fast-Start Failover to database "orcl"...
Performing failover NOW, please wait...
Failover succeeded, new primary is "orcl"
11:55:20.65  Tuesday, August 25, 2015


11:58:37.45  Tuesday, August 25, 2015
Initiating reinstatement for database "msft"...
Reinstating database "msft", please wait...
Operation requires shutdown of instance "msft" on database "msft"
Shutting down instance "msft"...
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance "msft" on database "msft"
Starting instance "msft"...
Unable to connect to database
ORA-12541: TNS:no listener

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps and reissue the REINSTATE command:
   start up and mount instance "msft" of database "msft"

11:58:55.43  Tuesday, August 25, 2015


DGMGRL> connect sys
Password:
Connected.

DGMGRL> show configuration verbose

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    orcl - Primary database
     Warning: ORA-16817: unsynchronized fast-start failover configuration

    msft - (*) Physical standby database (disabled)
     ORA-16661: the standby database needs to be reinstated

 (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           msft
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configuration Status:
WARNING

DGMGRL> reinstate database msft
Reinstating database "msft", please wait...
Reinstatement of database "msft" succeeded


DGMGRL> show configuration verbose

Configuration - DG_PROD

 Protection Mode: MaxAvailability
 Databases:
    orcl - Primary database
    msft - (*) Physical standby database

 (*) Fast-Start Failover target

Fast-Start Failover: ENABLED

 Threshold:        30 seconds
 Target:           msft
 Observer:         collabn1.shaiksameer
 Lag Limit:        30 seconds (not in use)
 Shutdown Primary: TRUE
 Auto-reinstate:   TRUE

Configuration Status:
SUCCESS


After the fast start failover automatic reinstate of standby database failed and I have to issue the command “reinstate database msft” manually from Primary dgmgrl.

SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE
------------------------------ -------------------- ----------------
msft                  MOUNTED           PHYSICAL STANDBY


SQL> select db_unique_name,open_mode,database_role from v$database;

DB_UNIQUE_NAME              OPEN_MODE        DATABASE_ROLE
------------------------------ -------------------- ----------------
orcl                  READ WRITE        PRIMARY