oracle DBA tips (PART-I)

ORACLE DBA TIPS:- PART-1
---------------------------------
1.To dynamically change the default tablespace type after database creation, use the SET
DEFAULT TABLESPACE clause of the ALTER DATABASE statement:
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;

2.You can determine the current default tablespace type for the database by querying the
DATABASE_PROPERTIES data dictionary view as follows:
SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';


3.To view the time zone names in the file being used by your database, use the following
query:
SELECT * FROM V$TIMEZONE_NAMES;


4.You can cancel FORCE LOGGING mode using the following SQL statement:
ALTER DATABASE NO FORCE LOGGING;

5.The V$SGA_TARGET_ADVICE view provides information that helps you decide on a
value for SGA_TARGET.

6.The fixed views V$SGA_DYNAMIC_COMPONENTS and V$SGAINFO display the current
actual size of each SGA component.

7.Checking Your Current Release Number
SELECT * FROM PRODUCT_COMPONENT_VERSION;

SELECT * FROM v$VERSION;

8.Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks. The maximum number of datafiles in an Oracle Database is limited (usually to 64K files).

9.Specifying a Flash Recovery Area with the following initialization parameters:
DB_RECOVERY_FILE_DEST
DB_RECOVERY_FILE_DEST_SIZE
In a RAC environment, the settings for these two parameters must be the same on all
instances.

10.DB_BLOCK_SIZE Initialization Parameter
You cannot change the block size after database creation except by re-creating the
database.

11.Nonstandard Block Sizes
Tablespaces of nonstandard block sizes can be created using the CREATE
TABLESPACE statement and specifying the BLOCKSIZE clause. These nonstandard
block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K.

12.All SGA components allocate and deallocate space in units of granules. Oracle Database tracks SGA memory use in internal numbers of granules for each SGA component.

13.Viewing Information about the SGA:
v$SGA
v$SGAINFO
v$SGASTAT
v$SGA_DYNAMIC_COMPONENTS
v$SGA_DYNAMIC_FREE_MEMORY
v$SGA_RESIZE_OPS
v$SGA_CURRENT_RESIZE_OPS
v$SGA_TARGET_ADVICE

14.An optional COMMENT clause lets you associate a text string with the parameter
update. When you specify SCOPE as SPFILE or BOTH, the comment is written to the
server parameter file.
example:ALTER SYSTEM
SET LOG_ARCHIVE_DEST_4='LOCATION=/u02/oracle/rbdb1/',MANDATORY,'REOPEN=2'
COMMENT='Add new destimation on Nov 29'
SCOPE=SPFILE;

15.Viewing Parameter Settings
show parameters, v$parameter, v$parameter2, v$spparameter.

16.You can find service information in the following service-specific views:

DBA_SERVICES

ALL_SERVICES or V$SERVICES

V$ACTIVE_SERVICES

V$SERVICE_STATS

V$SERVICE_EVENTS

V$SERVICE_WAIT_CLASSES

V$SERV_MOD_ACT_STATS

V$SERVICE_METRICS

V$SERVICE_METRICS_HISTORY

The following additional views also contain some information about services:

V$SESSION

V$ACTIVE_SESSION_HISTORY

DBA_RSRC_GROUP_MAPPINGS

DBA_SCHEDULER_JOB_CLASSES

DBA_THRESHOLDS

17.Viewing Information About the Database

DATABASE_PROPERTIES

GLOBAL_NAME

V$DATABASE

18.Starting an Instance, Mounting a Database, and Starting Complete Media Recovery
If you know that media recovery is required, you can start an instance, mount a
database to the instance, and have the recovery process automatically start by using
the STARTUP command with the RECOVER clause:
STARTUP OPEN RECOVER
If you attempt to perform recovery when no recovery is required, Oracle Database
issues an error message.

19.Placing a Database into a Quiesced State
To place a database into a quiesced state, issue the following statement:
ALTER SYSTEM QUIESCE RESTRICTED;
Non-DBA active sessions will continue until they become inactive.

20. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view:

select bl.sid, user, osuser, type, program
from v$blocking_quiesce bl, v$session se
where bl.sid = se.sid;

21.You cannot perform a cold backup when the database is in
the quiesced state, because Oracle Database background processes
may still perform updates for internal purposes even while the
database is quiesced. In addition, the file headers of online datafiles
continue to appear to be accessible. They do not look the same as if
a clean shutdown had been performed. However, you can still take
online backups while the database is in a quiesced state.

22.Restoring the System to Normal Operation
The following statement restores the database to normal operation:

ALTER SYSTEM UNQUIESCE;

23.Viewing the Quiesce State of an Instance
You can query the ACTIVE_STATE column of the V$INSTANCE view to see the current
state of an instance. The column values has one of these values:

NORMAL: Normal unquiesced state.
QUIESCING: Being quiesced, but some non-DBA sessions are still active.
QUIESCED: Quiesced; no non-DBA sessions are active or allowed.

24.Suspending and Resuming a Database
The ALTER SYSTEM SUSPEND statement halts all input and output (I/O) to datafiles (file header and file data) and control files. The suspended state lets you back up a database without I/O interference. When the database is suspended all preexisting I/O operations are allowed to complete and any new database accesses are placed in a queued state.

The following statements illustrate ALTER SYSTEM SUSPEND/RESUME usage. The
V$INSTANCE view is queried to confirm database status.
SQL> ALTER SYSTEM SUSPEND;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
SUSPENDED
SQL> ALTER SYSTEM RESUME;
System altered
SQL> SELECT DATABASE_STATUS FROM V$INSTANCE;
DATABASE_STATUS
---------
ACTIVE

25.The DB_WRITER_PROCESSES initialization parameter specifies the number of DBWn processes.
Oracle Database allows a maximum of 20 database writer processes
(DBW0-DBW9 and DBWa-DBWj).

hope, this will help you,
regards,
rajeshkumar g

Related Topics:
http://oracleinstance.blogspot.com/2009/12/oracle-dba-tips-part-ii.html