Tales From A Lazy Fat DBA

Den of an Oracle DB Performance freak & a fan of PostgreSQL & Cassandra …. \,,/

  • Likes

    • 192,763
  • Archives

  • ŗ•ź

  • Categories

  • Subscribe

  • Prashant Dixit is the FatDBA

  • Follow Tales From A Lazy Fat DBA on WordPress.com
  • My Twitter Feeds

  • Disclaimer!

    FatDBA or Oracle ‘Ant’ is an independent web-blog/site.The experiences, Test cases, views, and opinions expressed in this website are my own and does not reflect the views or opinions of my employer.

    This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.
    Product and company names mentioned in this website may be the trademarks of their respective owners.

Posts Tagged ‘Manual’

Using DBCA (DB Create) in Silent Mode.

Posted by FatDBA on August 23, 2013

DBCA is one of the most important and easiest utility to create new databases. Most of us have used it during our careers in GUI mode, but we have an option to launch it in non-graphical mode (Non-Interactive Mode) as well. Below provided steps can be performed to create database in silent/non-interactive mode. So you can launch DBCA in text mode from your favorite ssh client/terminal like putty, secureCRT, SSH Tectia Client etc. and can save yourself from that ‘old’ manual DB create methods (Using catprocs, catalog sql’s and creating new dump directories).

To create database: This requires a response file which includes all necessary details like GGDBNAME (Global Database Name), SID, Listener Name, SYS/SYSTEM/DBSNMP/SYSMAN Passwords etc.
Browser the response file (ORACLE_HOME/assistants/dbca) and make all mandatory changes.

Step 1: Location of response file
[oracle@prashant dbca]$ pwd
/u01/app/oracle/product/11.2.0/db_1/assistants/dbca¬†¬†¬† — Default Location of Response File.

[oracle@prashant dbca]$ ls
dbca.rsp  doc  jlib  logs  templates

Create a copy of original file.
[oracle@prashant dbca]$ cp dbca.rsp  dbcacreatedb.rsp

[oracle@prashant dbca]$ ls
dbcacreatedb.rsp  dbca.rsp  doc  jlib  logs  templates

Step 2:
Edit all mandatory changes in response file.
GDBNAME = “silent”
SID = “silent”
SYSPASSWORD = “oracle90”
SYSTEMPASSWORD = “oracle90”
SYSMANPASSWORD = “oracle90”
DBSNMPPASSWORD = “oracle90”
CHARACTERSET¬†¬† = “US7ASCII” (Default)
NATIONAL CHARACTERSET = “UTF8” (Default)

Although there are various sections inside the response file “.rsp” file like createdatabase, deleteDatabase, createTemplateFromDB or createCloneTemplate. In this post we have talk only about ‘createDatabase‘ and is the area of interest (Used to create new database).

All Examples :
—————————–
1. dbca -progress_only -responseFile <response file>
Display a progress bar depicting progress of database creation
process.

2. dbca -silent -responseFile <response file>
Creates database silently. No user interface is displayed.

3. dbca -silent -createDatabase -cloneTemplate
-responseFile <response file>    
Creates database silently with clone template. The template in
responsefile is a clone template.

4. dbca -silent -deleteDatabase -responseFile <response file>
Deletes database silently.

Step: 3

So, we are going to install the database using ‘STEP 3’ without using -cloneTemplate. This will create database silently and will show progress in percentages.
[oracle@prashant dbca]$ dbca -silent -createDatabase -responseFile dbcacreatedb.rsp
Copying database files
1% complete
3% complete
11% complete
18% complete
26% complete
37% complete
Creating and starting Oracle instance
40% complete
45% complete
50% complete
55% complete
56% complete
60% complete
62% complete
Completing Database Creation
66% complete
70% complete
73% complete
85% complete
96% complete
100% complete
Look at the log file “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log” for further details.

It seems that Database Creation is successful completed. Let’s check creation¬† log file.
File: “/u01/app/oracle/cfgtoollogs/dbca/silent/silent.log”

Okay, while checking log file i found some new entries after creating database:

Database creation complete. For details check the log-files at:
/u01/app/oracle/cfgtoollogs/dbca/silent.
Database Information:
Global Database Name:silent
System Identifier(SID):silent

[oracle@prashant dbca]$ cd /u01/app/oracle/cfgtoollogs/dbca/silent
[oracle@prashant silent]$ ls -ltr
total 104
-rw-r—– 1 oracle oinstall¬† 1372 Aug 23 11:47 rmanRestoreDatafiles.sql
-rw-r—– 1 oracle oinstall¬†¬† 330 Aug 23 11:49 CloneRmanRestore.log
-rw-r—– 1 oracle oinstall¬† 1135 Aug 23 11:50 cloneDBCreation.log
-rw-r—– 1 oracle oinstall¬†¬†¬†¬† 8 Aug 23 11:50 postScripts.log
-rw-r—– 1 oracle oinstall¬†¬†¬†¬† 6 Aug 23 11:50 lockAccount.log
-rw-r—– 1 oracle oinstall¬†¬† 349 Aug 23 11:51 postDBCreation.log
-rw-r—– 1 oracle oinstall¬†¬† 458 Aug 23 11:51 OraDb11g_home1_silent_creation_checkpoint.xml
-rw-r—– 1 oracle oinstall 66392 Aug 23 11:51 trace.log
-rw-r—– 1 oracle oinstall¬†¬† 654 Aug 23 11:51 silent.log

* There are some interesting files generated during DB creation like CloneRmanRestore.log, cloneDBCreation.log, postDBCreation.log and rmanRestoreDatafiles.sql.

Let’s check our new database ‘Silent’.
[oracle@prashant silent]$ ps -ef|grep pmon
oracle   12671     1  0 Aug20 ?        00:00:03 ora_pmon_sairam
oracle   20771     1  0 11:50 ?        00:00:00 ora_pmon_silent
oracle   22315 18308  0 13:06 pts/2    00:00:00 grep pmon

Okay we have a PMON for databse (Silent).
[oracle@prashant silent]$ . oraenv
ORACLE_SID = [sairam] ? silent
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 is /u01/app/oracle
[oracle@prashant silent]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 23 13:07:04 2013
Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ——————–
SILENT    READ WRITE

 

Thanks
Prashant Dixit
“Sharing is Good”

Posted in Advanced, Basics | Tagged: , , | Leave a Comment »

Manual Creation of Database.

Posted by FatDBA on August 6, 2012

Below provided are Steps to create a Database manually 9Without using any GUI tool – DBCA)

Manual Creation Of Database
================================

Create Directories in ORACLE_BASE to install physical files.

[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/adump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/bdump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/cdump
[oracle@localhost root]$ mkdir -p /u01/app/oracle/admin/doom/udump

[oracle@localhost root]$ mkdir -p /u01/app/oracle/oradata/doom

Create parameter file for new database
[oracle@prashant2 dbs]$ cp initarcl.ora initdoom.ora

Note: Use Find and Replace command in VI in order to replace all old SID terms from the param file.
ex: :%s/OLD/NEW/g
Capital ZZ to save all changes made.

create Password File using ORAPWD Utility.
[oracle@prashant2 dbs]$ orapwd file=orapwdoom password=oracle90 entries=19

Export newly created SID
[oracle@prashant2 dbs]$ export ORACLE_SID=doom

[oracle@prashant2 dbs]$ echo $ORACLE_SID
doom

Create a script/text file to create Database (Contains Steps to create all data-files, control files, System files, redo log files etc).
Something like:

create database doom
datafile ‘/u01/app/oracle/admin/doom/system.dbf’ size 250M
sysaux datafile ‘/u01/app/oracle/admin/doom/sysaux.dbf’ size 100m
undo tablespace undotbs1 datafile ‘/u01/app/oracle/admin/doom/undo.dbf’ size 100m
default temporary tablespace temp tempfile ‘/u01/app/oracle/admin/doom/tmp.dbf’ size 100m
logfile group 1 ‘/u01/app/oracle/admin/doom/log1.ora’ size 50m,group 2 ‘/u01/app/oracle/admin/doom/log2.ora’ size 50m;
Startup Database in ‘NOMOUNT’ Mode.
As we already created Parameter file for the Database but yet to create controlfiles so it’s not possibel to start DB in MOUNT mode.
Starting DB will allow us to run scripts from any locations.

[oracle@prashant2 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Aug 6 15:24:59 2012

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

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  306184192 bytes
Fixed Size                  1219112 bytes
Variable Size              96470488 bytes
Database Buffers          205520896 bytes
Redo Buffers                2973696 bytes
SQL> @dbcreate.sql

Database created.

SQL>

N.E.X.T is to load two sql scripts (files) which helps database to create all default tablespaces, views. rows, comments, indexes, packages, procedures, synonyms, libraries, grants and commits columns and other objects and create entries in data dictionary.

catalog.sql – create data dictionary views.
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catalog.sql

Grant succeeded.

Commit complete.

View created.

catproc.sql – run all sql scripts for the procedural option
SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql

@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/catproc.sql
DOC>######################################################################
DOC>######################################################################
DOC>The following PL/SQL block will cause an ORA-20000 error and
DOC>terminate the current SQLPLUS session if the user is not SYS.
DOC>Disconnect and reconnect with AS SYSDBA.
DOC>######################################################################
DOC>######################################################################
DOC>#

Once Created start Database and check Status of the Instance using:

SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
——— ———-
DOOM      READ WRITE

1 row selected.

SQL>

 

 

Thanks for Reading.
Prash’ant’ Dixit

Posted in Basics | Tagged: , | Leave a Comment »

 
%d bloggers like this: