Quiescing a Database - Suspending a Database - Dropping a Database- Managing Initialization Files - Managing Parameters in SPFILE

Quiescing a Database - Suspending a Database -  Dropping a Database- Managing Initialization Files - Managing Parameters in SPFILE

Quiescing a Database

 Users will remain logged in, and they can continue to execute their requests that are in

progress, while the database is in the quiesced state. The database, however, will block all

new transactional requests except those made by the users SYS and SYSTEM (not DBA

grantees).

ALTER SYSTEM QUIESCE RESTRICTED;

ALTER SYSTEM UNQUIESCE;

SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;

Suspending a Database

 All reads from and writes to the datafiles and control files are suspended.

ALTER SYSTEM SUSPEND;

ALTER SYSTEM RESUME;

SELECT instance_name, status, shutdown_pending, active_state FROM v$instance;

Dropping a Database

 Datafiles, redo log files, and control files are removed automatically. Alert log and

parameter file are not deleted.

CONNECT sys/sys_passwd AS SYSDBA

SHUTDOW IMMEDIATE

STARTUP RESTRICT MOUNT

SELECT name FROM v$database;

DROP DATABASE;

Initialization Files

Managing Initialization Files

 During startup, in $ORACLE_HOME/dbs (for UNIX/Linux) Oracle will look for the correct

initialization file to use, in the following order:

o spfile$ORACLE_SID.ora

o spfile.ora

o init$ORACLE_SID.ora

# currently used SPFiles (if null, pfile was used)

select * from v$parameter where name = 'spfile'

# create pfile

CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora' FROM

PFILE='/u01/oracle/dbs/test_init.ora'

# use pecific spfile during startup

STARTUP PFILE = $ORACLE_HOME/dbs/initDBA1.ora

# PFILE can indicate to use SPFILE

SPFILE = /database/startup/spfileDBA1.ora

In Oracle 11g

# write current values of instance parameters

CREATE PFILE FROM MEMORY;

CREATE SPFILE FROM MEMORY;

Managing Parameters in SPFILE

-- display current value of a parameter

select * from v$parameter where name = 'spfile'

-- parameter values set in SPFILE

select NAME, ISSPECIFIED from V$SPPARAMETER where name like '%dest%';

-- in SQL Plus

show parameter target

-- set parameter value

alter system set parameter=value scope=SPFILE|MEMORY|BOTH

-- delete a parameter from SPFILe

ALTER SYSTEM RESET undo_suppress_errors SCOPE=BOTH SID='node01';

-- Only sessions started after the statement is executed are affected

-- this option is a must for the parameters whose ISSYS_MODIFIABLE column

-- in V$PARAMETER is DEFERRED. You cannot use it, if the column value is

FALSE

ALTER SYSTEM SET parameter_name DEFERRED;

-- undocumented parameters

SELECT

a.ksppinm parameter, a.ksppdesc description,

b.ksppstvl session_value, c.ksppstvl instance_value

FROM x$ksppi a, x$ksppcv b, x$ksppsv c

WHERE

a.indx = b.indx

AND a.indx = c.indx

AND SUBSTR (a.ksppinm,1,1) = '_'

ORDER BY a.ksppinm;

Alert and Trace Files

Monitoring Alert and Trace Files

# to obtain the Location of Trace Files
column NAME format a35
column VALUE format a35
select name, value from v$parameter
where name in ('background_dump_dest','user_dump_dest')
# to set maximum size of trace files (excluding the alert file)
# in number of block unless you sepcify K or M
select * from v$parameter where upper(name )= 'MAX_DUMP_FILE_SIZE'
alter session set MAX_DUMP_FILE_SIZE='100M'