Oracle

Oracle Startup and shutdown options-08

Advertisements

Startup and shutdown options

 

Startup Options

Startup nomount – Read initialization parameter

Startup mount – Read Control file

Startup / Open

Startup force

Startup restrict

Startup upgrade/migrate

 

Startup nomount  — Read initialization parameter

Instance will start and parameter file read.

For database creation needs to start from here. Control file corruption can recover from here. Any parameter changes from pfile to spfile has to done  from here, if the  database uses a spfile.

Startup mount  — Read Control file

The followings are possibility

Read control file content

Data file rename, redo log file rename. Archive log to no archive mode vice vera.

Flash back database ON/OFF

 

Startup

This an open state for user accessing

Startup force

In this option (shutdown abort + start) will run.

Startup restrict

It’s an open state, but users cannot access it.  It’s mostly used in the database maintenance. System users can only login, if you want to allow users they needs “restricted session” permission.

Startup Upgrade/ migrate

For 10g & 11g – We can use this for upgrade.

9i and below – We need to use migrate for upgrade.

 

Shutdown Options

Shutdown abort

Shutdown Immediate

Shutdown Transnational

Shutdown

Shutdown abort Shutdown Immediate Shutdown Transactional Shutdown
Improper shutdown Proper shutdown Proper shutdown Proper shutdown
No checkpoint Checkpoint SCN Checkpoint SCN Checkpoint SCN
No new users No new users No new users No new users
Terminate existing session Terminate existing session Terminate existing session No, will wait for logout
Kill Transaction Kill Transaction Wait for Transaction Kill Transaction

 

Quiescing a Database

Occasionally you might want to put a database in a state that allows only DBA transactions, queries, fetches, or PL/SQL statements.

 

How to speed up the shutdown immediate?

Database creation – Silent Mode

 

We can create a database by using DBCA or manually. Manual mode will be used in secure environment, where the client will not give you RDP.

Manual database creation

We need to create folders manually to load and save the database and software files.

Fo 10g

ORACLE_BASE is the root level director, where the OH also resides.

/opt/oracle (ORACLE_BASE )–( Admin & Oradata)

Admin –DBname (pfile,adump,bdump,cdump,ddump,udump & create)

Oradata — DBnames

Fo 11g

/opt/oracle (ORACLE_BASE )— (Admin , Oradata & diag)

Admin –DBname (pfile,adump,ddump & create)

Oradata — DBnames

Diag — RDMBS–DBname–DBname–Trace (bdump,cdump & udump)

All in under trace, but without folders

ORACLE_HOME – /opt/oracle/product/11.2.0/dbhome

It is a separate one – It only has a binaries, OH/Network/admin (Listener & tnsnames), parameter files & PATH variables.

ORACLE_BASE: Specifies the directory at the top of the Oracle software

ORACLE_HOME: Specifies the directory where the Oracle software is installed.

ORACLE_SID: Specifies the instance name and must be unique for Oracle instances running

ORA_NLS33: Required when creating a database with a character set other than US7ASCII

PATH: Specifies the directories the operating system searches to find executables, such as SQL*Plus.

LD_LIBRARY_PATH: Specifies the directories for the operating system and Oracle library files.

 

Admin

Pfile:  Parameter file

You can copy it from other server rename it. open and replace the DBname.

Adump:  Audit_dump_dest

When we do database audit the trace files will be stored here.

Bdump: Background_dump_dest

  1. Alert log.

E.x: Alert_DBname.logs

Alert log is important for the DBAs to check the database related errors.

It will have log swith & startup and shutdown info. But, it does not store the unexpected reboots.

  1. Exception case error handling trace files will be stored here.

Cdump:  Core_dump_dest

OS and database related “kernel” errors are stored here.

Ddump:  Datapump_dump_dest

Datapump (Exoprt/Import) logs are stored here.

Udump:  Users_dump_dest

All users trace files are stored here

Create:

It will be empty. DBAs can use this as a centralized script part.

Note: We need to do a house cleanup for those folders. – Delete the logs older that 1 month.

Oradata

CRD files are stored here – Control, Redo & Data files are stored.

 

DB Creation

$             export ORACLE_SID=test

$             orapwd file =$ORACLE_HOME/dbs/orapwtest password = sys

 

$             sqlplus  /nolog

SQL> conn sys/sys as sysdba

SQL> startup pfile=’/opt/ oracle/admin/test/pfile/inittest.ora’ nomount

 

SQL> create database  test

datafile ‘/opt/ oracle/oradata/test/system01.dbf’ size 100m

sysaux datafile ‘/opt/ oracle/oradata/test/sysaux.dbf’ size 100m

logfile ‘/opt/ oracle/oradata/test/redolog01.log’ size 100m,

‘/opt/ oracle/oradata/test/redolog02.log’ size 100m

undo tablespace undotbs01

datafile  ‘/opt/ oracle/oradata/test/undo01.dbf’ size 100m

default temporary tablespace temp

tempfile  ‘/opt/ oracle/oradata/test/temp01.tmp’ size 100m;

What are the possible error may occur in silent DB creation

Instance terminated and disconnected forced.

  1. Password wrong or file corrupted.
  2. Undo tablespace name should be same in pfile and DB creation – case sensitive
  3. Any spelling mistake in the folder creation

 

Run the following script after DB creation

sql>@?/rdbms/admin/catlog.sql  — will take 15 min

(@ – is for run ? – is for $ORACLE_HOME)

This script will create the data dictionary view and dynamic views.

sql>@?/rdbms/admin/catproc.sql — will take 25 min

 

This script will create the SP,funcation,Views Package, base table, default etc..

sql> create spfile from pfile =’/opt/oracle/admin/test/pfile/inittest.ora’;

sql> Shutdown immediate

sql> Startup

sqlselect name from v$database;

 

In Windows, How to create the database in silent mode

Create all the folders on the desired drive.

C:\> aradim -new -sid test — Create the service for the DB.

C:\> set ORACLE_SID=test

C:\> orapwd  file=% ORACLE_HOME%\database\PWDtest.ora password=sys

 

Remaining are same as Linux but, we need to change the “/opt” to “D:\”.

One, all done you can set the service to automatic mode.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: