Startup and shutdown options
Startup nomount – Read initialization parameter
Startup mount – Read Control file
Startup / Open
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
This an open state for user accessing
In this option (shutdown abort + start) will run.
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 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.
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
/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.
Pfile: Parameter file
You can copy it from other server rename it. open and replace the DBname.
When we do database audit the trace files will be stored here.
- Alert log.
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.
- Exception case error handling trace files will be stored here.
OS and database related “kernel” errors are stored here.
Datapump (Exoprt/Import) logs are stored here.
All users trace files are stored here
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.
CRD files are stored here – Control, Redo & Data files are stored.
$ 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.
- Password wrong or file corrupted.
- Undo tablespace name should be same in pfile and DB creation – case sensitive
- 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
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.