Tales From A Lazy Fat DBA

Love all databases! – Its all about performance, troubleshooting & much more …. ¯\_(ツ)_/¯

  • Prashant Dixit is the 'FatDBA' ...
  • Follow me on Twitter

Posts Tagged ‘SCRIPTS’

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 »

RMAN Command – (Host, Script & Spool)

Posted by FatDBA on July 20, 2012

============================
‘SCRIPT’ Command
============================

Create a stored script and store it in the recovery catalog

# creates recovery catalog script to back up database and archived logs
RMAN> CREATE SCRIPT backup_whole
COMMENT “backup whole database and logs”
{
BACKUP INCREMENTAL LEVEL 0 TAG b_whole_l0
DATABASE PLUS ARCHIVELOG;
}

RMAN> list script names;

List of Stored Scripts in Recovery Catalog

Scripts of Target Database QRCL

Script Name
Description
———————————————————————–
backup_whole
backup whole database and logs

RMAN>

The PRINT SCRIPT command is used to view the contents of a stored script.
The REPLACE SCRIPT command is used to update the contents of a stored script.
The EXECUTE SCRIPT command is used to execute the commands in the stored script.
The SCRIPT command line arguments for RMAN (described in “cmdLine”) runs a stored script automatically when starting RMAN.
The DELETE SCRIPT command is used to delete a stored script from the recovery catalog.

============================
‘HOST’ Command
============================
Invoke an operating system command-line subshell from within RMAN or run a specific operating system command.

RMAN> host;

[oracle@prashant1 tmp]$ ls
bkp.txt                  keyring-0ApY2V  keyring-bnJBhL  keyring-EU8HZP  keyring-gyrtQl  keyring-NhkTlq  keyring-TRWPLH

keyring-VRSr6r  mapping-root
df2log.f                 keyring-2DWGLn  keyring-BnLWvG  keyring-Fd4x0Y  keyring-hvDT3R  keyring-NkuSug  keyring-ud148y

keyring-yfJADW  VMwareDnD
gedit.oracle.1518151704  keyring-9YXYpF  keyring-bWWK07  keyring-FulTZv  keyring-M5RlDV  keyring-OKgmpF  keyring-VJRsde

keyring-z0NZhH  vmware-root
gedit.root.905835812     keyring-b0iCrd  keyring-edvXjr  keyring-GUQwrx  keyring-N7mw5M  keyring-tmfXqO  keyring-vMdOpj

mapping-oracle  vmware-tools-distrib

[oracle@prashant1 tmp]$ exit
exit
host command complete

RMAN>

=============================
‘SPOOL’  Command
=============================

Write RMAN output to a log file.

[oracle@prashant1 tmp]$ touch bkp.txt
[oracle@prashant1 tmp]$ ls
bkp.txt                  keyring-2DWGLn

[oracle@prashant1 tmp]$ rman target / catalog rman/oracle90@orcl;

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Jul 20 15:43:23 2012

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

connected to target database: QRCL (DBID=859215136)
connected to recovery catalog database

RMAN> @/tmp/bkp/txt;

RMAN> BACKUP DATAFILE 1;
Starting backup at 20-JUL-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=159 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/qrcl/system01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JUL-12
channel ORA_DISK_1: finished piece 1 at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/backupset/2012_07_20/o1_mf_nnndf_TAG20120720T154419_80lcvvwv_.bkp

tag=TAG20120720T154419 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 20-JUL-12

Starting Control File and SPFILE Autobackup at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/autobackup/2012_07_20/o1_mf_s_789147885_80lcwos9_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUL-12

RMAN> SPOOL LOG TO ‘/tmp/df2log.f’;RMAN>
RMAN>
Spooling for log turned off

Recovery Manager10.2.0.1.0

RMAN> **end-of-file**

RMAN>
RMAN> exit

Recovery Manager complete.

[oracle@prashant1 tmp]$ cat df2log.f

Spooling started in log file: /tmp/df2log.f

Recovery Manager10.2.0.1.0

RMAN> BACKUP DATAFILE 2;
Starting backup at 20-JUL-12
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00002 name=/u01/app/oracle/oradata/qrcl/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 20-JUL-12
channel ORA_DISK_1: finished piece 1 at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/backupset/2012_07_20/o1_mf_nnndf_TAG20120720T154449_80lcwslb_.bkp

tag=TAG20120720T154449 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 20-JUL-12

Starting Control File and SPFILE Autobackup at 20-JUL-12
piece handle=/u01/app/oracle/flash_recovery_area/QRCL/autobackup/2012_07_20/o1_mf_s_789147892_80lcwx78_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 20-JUL-12

RMAN> SPOOL LOG OFF;
[oracle@prashant1 tmp]$

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

 
%d bloggers like this: