DBA

Hi..You would like to uninstall linux ... Do the follow steps

1. Before removing a partition, boot rescue mode
2. Start parted ,where /dev/hda is the device on which to remove the partition
3. In command mode
4. >list hard-drives
5. >parted /dev/hda or /dev/sda
6. >print
7. >rm 2
8. exit and then you can install another OS on your system like windows xp or any.

1. Loss of datafile(s) System Tablespace :-

Action...

sql>shutdown immediate

Oracle Instance shutdown.

sql>host cp backupfile file

(Restore your data backup files from backup to related loss system’s datafiles)

SQL> CONNECT / AS SYSDBA

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE;

SQL> ALTER DATABASE OPEN;


----------------------------------------------------------------------------------------------------------------------------------------

2.Loss of Non-system(s) Tablespace :-

Action...

Don't need to shutdown database.

sql>alter tablespace ......... offline

SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

Or recover by using datafile(s)

SQL> ALTER DATAFILE ‘/u01/…’ OFFLINE;

SQL> host cp backupfile file
(Restore your data backup files from backup to related loss system’s datafiles)
SQL> RECOVER DATAFILE ‘/u01…’;

SQL> ALTER DATAFIL ‘/u01/…’ ONLINE;

Or shutdown database

SQL> SHUTDOWN IMMDIATE;

SQL> (Restore your data backup files from backup to related loss system’s datafiles)

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE;

SQL> ALTER DATABASE OPEN

----------------------------------------------------------------------------------------------------------------------------------------
3.Loss of data file(s) of rollback OR undo segments tablespace :-

A datafile in a rollback segment tablespace has been lost or damaged to a point that
Oracle cannot recognize it anymore.If you try to startup the database will result in ORA-1157, ORA-1110, and possibly an operating system level error such as ORA-7360. Trying to shut down the database
in normal or immediate mode will result in ORA-1116, ORA-1110, and possiblyan operating system level error such as ORA-7368.

PLEASE NOTE that before using _corrupted_rollback_segments parameter, you should
explore all other options. This will require extra caution. Call Oracle Customer Support if you have any questions or need any assistance

The Database is down:
----------------------------------------------------------------------------------------------------------------------------------------
Assuming that your database was cleanly shutdown (check your alert.log)

SQL> SHUTDOWN IMMEDIATE/NORMAL

SQL> STARTUP RESTRICT MOUNT

SQL> ALTER DATABASE DATAFILE '' OFFLINE DROP;

SQL> ALTER DATABASE OPEN

SQL> DROP TABLESPACE INCLUDING CONTENTS;

SQL> -- Recreate the rollback tablespace with all its rollback segments.
Remember to bring the rollbacks online after you create them.
----------------------------------------------------------------------------------------------------------------------------------------

Assuming that you cann't open your database and you get the following error

messages: ORA-604, ORA-376, and ORA-1110.
-- Comment out the ROLLBACK_SEGMENTS parameter and add the following line:
_corrupted_rollback_segments = ( ,...., )
with exact the same rollbacks.
WARNING: Use this parameter ONLY IN THIS SPECIFIC SCENARIO or as
instructed by Oracle Customer Support.

SQL> STARTUP RESTRICT

SQL> DROP TABLESPACE INCLUDING CONTENTS;

SQL> Recreate the rollback tablespace with all its rollback segments.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

-- put back your ROLLBACK_SEGMENTS parameter in the init.ora file.
-- remove _CORRUPTED_ROLLBACK_SEGMENTS PARAMETER

SQL> -- Rebuild the database.
----------------------------------------------------------------------------------------------------------------------------------------

Assuming that database was not cleanly shutdown:
-- Restore the lost file from a backup.

SQL> STARTUP MOUNT

SQL> SELECT FILE#, NAME, STATUS FROM V$DATAFILE;

SQL> ALTER DATABASE DATAFILE '' ONLINE;
(if datafile is offline)

SQL> SELECT V1.GROUP#, MEMBER, SEQUENCE#, FIRST_CHANGE#
FROM V$LOG V1, V$LOGFILE V2
WHERE V1.GROUP# = V2.GROUP# ;

SQL> SELECT FILE#, CHANGE# FROM V$RECOVER_FILE;

If NOARCHIVELOG Mode and the CHANGE# is GREATER than the minimum FIRST_CHANGE#
of your logs, the datafile can be recovered.

SQL> RECOVER DATAFILE '';

SQL> ALTER DATABASE OPEN;

----------------------------------------------------------------------------------------------------------------------------------------
Assuming that the database is up and it is in ARCHIVELOG mode:

SQL> ALTER DATABASE DATAFILE '' OFFLINE;

SQL> -- Restore it from your backup.

SQL> RECOVER DATAFILE '';

SQL> ALTER DATABASE DATAFILE '' ONLINE;


-OR-

SQL> ALTER SYSTEM KILL SESSION ' , ' -- The transaction in RBS involved.

SQL> ALTER ROLLBACK SEGMENT OFFLINE;
\
SQL> DROP ROLLBACK SEGMENT ;

SQL> DROP TABLESPACE INCLUDING CONTENTS;

SQL> CREATE TABLESPACE rbs ...;

SQL> CREATE ROLLBACK SEGMENT rbs01 ...;

SQL> ALTER ROLLBACK SEGMENT ... ONLINE;
----------------------------------------------------------------------------------------------------------------------------------------

In this case to access to data you will get a message like this:

ORA-00376: file 20 connot be read at this time
File 2 happens to be one of the data files that belongs to the rollback segment tablespace.

Solution:

SQL> select segment_name, status from dba_rollback_segs;
(If status are in “NEED RECOVERY”)

SQL> SHOW PARAMETER undo

SQL> CREATE UNDO TABLESPACE myundotbs DATAFILE ‘/u01/…’ SIZE 50M;

SQL> ALTER SYSTEM SET undo_tablespace=’myundotbs’;

SQL> DROP TABLESPACE undotbs;
(Remove all related OS datafiles)
----------------------------------------------------------------------------------------------------------------------------------------
Loss of Rollback segments while there were transaction activities;

For example:

SQL> CREATE TABLE mytest (col1 varchar2(20));

SQL> BEGIN
FOR this IN 1..100 LOOP
INSERT INTO mytest VALUSE (this);
END LOOP;
END;
/

SQL> /* Notice that we didn’t commit any of above transaction */

SQL> /* shutdown abort or any crash on rollback datafiles will cause

The following error messages if we start the database. */
ORA-01157: cannot identify/lock data file 20 – see DBWR trace file
ORA-01110: data file 20: ‘/u01/…’

Solution:

If you shutdown the server then you will get a message like this:
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: 'C:\MYUNDOTBS_01.DBF'

SQL> STARTUP MOUNT

SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;

SQL> ALTER DATABASE OPEN;

SQL> /* remember you can not access to that table with transaction */

SQL> SELECT segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)

SQL> /* replace your backup file and recover the tablespace */

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

SQL> ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)

If you are not shutdown:

SQL> ALTER DATABASE DATAFILE 'C:\MYUNDOTBS_01.DBF' OFFLINE;

SQL> /* remember you can not access to that table with transaction */

SQL> SELECT tablespace_name, segment_name, status FROM dba_rollback_segs;
(You will see that the segments are NEEDS RECOVERY)

SQL> /* replace your backup file and recover the tablespace */

SQL> RECOVER TABLESPACE your_tablespace_name;

SQL> ALTER TABLESPACE your_tablespace_name ONLINE;

SQL> ALTER ROLLBACK SEGMENT … ONLINE;
(Repeat this for all of your rollback segments)

----------------------------------------------------------------------------------------------------------------------------------------
. Loss of an UN-archived Online log file.

Solution:

SQL> /* Your database is done. */

SQL> /* Replace all your datafiles and online redo logfile, but not controlfiles. */

SQL> host cp … …

SQL> STARTUP MOUNT

SQL> RECOVER DATABASE UNTIL CANCEL; -- UNTIL TIME …

SQL> ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.

SQL> SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */

Loss of your Online Redo-log files.

Solution:

SQL> /* If you loss your online redo-log files, your instance will hung or crash.
If not, try to shutdown abort. Restore all files from your cold backup
with exception of your control files. */

SQL> STARTUP MOUNT

SQL> /* Now do an incomplete recovery recover using CANCEL or TIME option */

SQL> RECOVER DATABASE UNTIL CANCEL;
/* you should apply up to your last archive and then cancel the recovery process */

SQL> ALTER DATABASE OPEN RESETLOGS;
/* Since you did incomplete recovery, you must use ‘RESETLOGS’ option.

SQL> SHUTDOWN IMMEDIATE
/* Do your cold backup immediately */
--------------------------------------------------------------------------------------------------------------------------------------- Loss of your control file.

Solution:

SQL> /* Assuming you have already backup controlfile to trace */

SQL> ALTER DATABASE BACKUP CONTROLFILE TO trace;

SQL> /* modify and run your trace file and your control file is up to date */

SQL> /* if not, then copy a old control file */

SQL> STARTUP MOUNT;

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;

SQL> ALTER DATABASE OPEN;

Database Opened.

SQL> SELECT NAME FROM V$DATABASE;

Name
-------
localdb

sql>shutdown immediate

Oracle instance shutdown.

sql>startup mount

database mounted.

NID: it’s a naming utility; you can able to change your database name and database id.


C:\>nid help=y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 14 10:48:33 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Keyword Description (Default)
----------------------------------------------------
TARGET Username/Password (NONE)

DBNAME New database name (NONE)

LOGFILE Output Log (NONE)

REVERT Revert failed change NO

SETNAME Set a new database name only NO

APPEND Append to output log NO

HELP Displays these messages NO

Here, I have change my database name using NID utility.

C:\>NID TARGET=SYS/sys@localdb DBNAME=local SETNAME=Y

DBNEWID: Release 10.2.0.1.0 - Production on Mon May 14 10:37:36 2007Copyright (c) 1982, 2005, Oracle.

All rights reserved.Connected to database TAJ (DBID=1565772587)Connected to server version 10.2.0Control Files in database:

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL01.CTL

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL02.CTL

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL03.CTL
Change database name of database localdb to local ? (Y/[N]) => Y Proceeding with operationChanging database name from localdb to local
Control File F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL01.CTL - modifiedControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL02.CTL - modifiedControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL03.CTL - modifiedDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\SYSTEM01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\UNDOTBS01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\SYSAUX01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\USERS01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\EXAMPLE01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\RMAN01.DBF - wrote new nameDatafile

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\TEMP01.DBF - wrote new nameControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL01.CTL - wrote new nameControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL02.CTL - wrote new nameControl File

F:\ORACLE\PRODUCT\10.2.0\ORADATA\localdb\CONTROL03.CTL - wrote new nameInstance

shut downDatabase name changed to local.Modify parameter file and generate a new password file before restarting.Succesfully changed database name.DBNEWID - Completed succesfully

c:\>oradim -new -sid local
instance created.

c:\>net start oracle servicelocal
service started.

c:\>set oracle_sid=local

c:\>slplus /nolog

sql>conn sys as sysdba

enter new password: sys (choice)

connected to idle instane.

You should check your parameter file and modify it. and replace local instead of localdb

sql>startup mount

database mounted.

Note :- ora-02778 : Name given for the log directory is invalid. In this case, you should check your parameter file name

sql>alter database open;

database opened.

sql>select name from v$database;

Name

----------
local

Ora -32004 --- obsolete and/or deprecated parameter(s) specified

Actions....

See alert log for of parameters that are obsolete or deprecated .remove them from spfileor the server side pfile.

sql>select name,isspecified from v$obsolete_parameter where is isspecified ='TRUE';

In my case somebody had been messing around with the parameter log_archive_start;

sql>alter system reset log_archive_start scope=spfile sid='*';
system altered.

sql>shut

sql>startup

Ora-01092 - Oracle instance terminated

Examine the alert log files for more information.

Actions.......

• Shutdown abort
• Bad permissions of BDUMP, UDUMP or CDUMP Directories
• Missing Datafiles
• Renaming files when oracle is down
• & many other os related causes

Check the parameter files whether datafiles or some files are located correct or not.

Recover database....

recover database until cancel
recover database until time '2004-03-21:22:59:04'
recover database until change 123456

recover datafile 'filename' until cancel
recover datafile 'filename' until time '2004-03-21:22:59:04'
recover datafile 'filename' until change 123456

recover tablespace ts_name until cancel
recover tablespace ts_name until time '2004-03-21:22:59:04'
recover tablespace ts_name until change 123456

recover database using backup controlfile

I want to find the size....

sql>show parameter db_block_size;

Name Type Value
-----------------------------------------------------------
db_block_size integer 8192

How to check the size in Oracle 9i

sql>select segment_name ,bytes/1024 as totsize_bytes, (blocks * 8192)/1024 as totbytes from user_segments where segment_name = upper('&1') and segment_type='TABLE';

Enter value for 1:

If you put any segment name and then you will see that table size.

Step 1 :-

• Put the tablespace in backup mode
• Copy the datafiles which tablespace in backup mode
• Once copy the datafiles is completed putout from the backup mode
• switch logfile
• archive log mode is enabled

Step 2 :-

sql> select tablespace_name from dba_data_files;

tablespace_name

----------------------------------

system

sysaux

users

undo

temp

example

sql>alter tablespace system begin backup;

sql>host copy E:\oracle\product\10.2.0\oradata\localdb\system01.dbf e:\oracle\backup;

1 files copied

sql>select * from v$backup;

file status change# time

-----------------------------------------------

1 active 1134456 22-nov-08

2 not active 0

" " "

sql> alter tablespace system end backup;

system altered.

After exec, you should check the any datafiles in backup mode. If any datafiles in backup mode ,you should exec end backup condition after taken backup datafiles.

sql>alter system switch logfile;

system altered.

This is consistent backup that means all datafiles and controlfiles are consistent to point in time.database in no archive log mode.

Steps....

• shutdown the database ,sql>shutdown normal
• Copy the controlfiles and datafiles,parameter file and passwordfile os level and then paste it in another location.
• startup database ,sql>startup

Example.....

• shutdown the database, drop the all controlfiles and datafiles and then startup the database.
• Error ora-00205-Error in identifying the control files for the location.
• If you have 5 multiplexing controlfiles,2 multiplexing in different location and remain 3 controlfiles were dropped,you should modify the location in parameter file current controlfile and then save it.
• startup the database using parameter files.
• In mount database,error will appear.
• Recover datafile system and then some datafile , in this case you should copy the backup files and paste it in default location.

sql>recover database;

media recovery complete.

sql>alter database open;

Database opened.

ora-01031- insufficent privilege

Suppose these error occur means..your ora_dba group not configured in OS authentication.

Solve...

• Control Panel -----------> Adminstrative tools -------> Computer management
• Click Local groups and then users or groups
• on right side administrative is appear.right click on this and click properties.

In this case You should find Ora_dba ..its not appear in this. so you go to add the Ora_dba privilege to OS.

• run -------> cmd enter
• > net localgroup ora_dba /add enter

And then you find the Ora_dba is there and then add ora_dba to your administrator groups.

• >set oracle_sid=oracle
• sqlplus
• sys/sys@localdb as sysdba

database connected.........and then you can easily access it.

Due to error occur on Oracle Listner not properly mentioned

So.., you should check lisntener.ora file and modify

Example :-

(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)

< (SID_DESC =
(SID_NAME = localdb )
(ORACLE_HOME = E:\oracle\product\10.2.0\db_1)
(SID_NAME = localdb) >

Note : < ... > here check your service name either there or not?localdb -------- db name
And then go to
lsnrctl >stop

lsnrctl>start

Or

>set oracle_sid=localdb
>sqlplus
username : / as sysdba
>startup


& then you connect sys/sys@localdb as sysdba

Your service should be started
------------------------------------------------------------------------------------------
Thanks & Regards
S.Mohamed Azarudeen
azarmohds@gmail.com

Buffer Hit Ratio :-

Buffer hit ratio notes:

• Consistent Gets - The number of blocks accesses made to the block buffer to retrive data in a consistent mode.
• DB Blk gets - The number of blocks accessed via single block gets.
• Physical Reads - The cumulative number of blocks read from disk.
• Logical reads are the sum of consisent gets and db block gets.
• the db block gets statstic value is incremented when a block is read for update and when segment header blocks are accessed.
• Hit ratio should be > 80% , else increase db_block_buffers in init.ora

sql>select sum(decode(name, 'consistent gets',value,0)) "consistent gets",

sum(decode(name,'db block gets',value,0)) "db block gets",

sum(decode(name,'physical reads',value,0)) " physical reads",

round((sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0)) - sum(decode(name,'physical reads',value,0))) /

(sum(decode(name,'consistent gets',value,0)) +

sum(decode(name,'db block gets',value,0))) * 100,2) "Hit Ratio" from v$sysstat;

Data Dict Hit Ratio :-

• Gets - Total number of requests for information on the data object.
• Cache Misses -- Number of data requests resulting in cache misses.
• Hit ratio should be > 90%,else increse Shared_pool_size in init.ora

Sql> Select sum(gets),sum(getmisses), round((1-(sum(getmisses)/sum(gets))) * 100,2) from v$rowcache;

Library Cache Miss Ratio :-

• Executions - The number of times a pin was requested for objects of this namespace.
• Cache misses - any pin of an object that is not the first pin performed since the object handle was created and which requires loading the object from disk.
• Hit ratio should be <>

Sql> Select sum(pins) executions,sum(reloads) cache_misses,sum(reloads) / sum(pins) miss_ratio from v$librarycache;

How Do you move table from sys tablespace to user tablespace :-

sql>alter table tablename move tablespace tablespacename;

Finding out Table and Tablespace Location :-

sql>select owner,table_name,tablespace_name from dba_tables where table_name='tablename';


How many memory layers are in the shared pool?

Shared poll consists of two layers.

• Library Cache ---- parsed sql staments and cursor information its a symmentric check.
• Data Dictionary Cache --- user account,privilege,segments,datafile and extend information stored in the data dictionary cache.

Users Privilege :-

sql>create user azar idenitified by azar;

or

sql>create user azar idenitified by azar default tablespace temporary tablespace quota unlimited on password expire;

sql>grant create session,create table to azar;

or

sql>Grant connect,resource to azar;

sql>show user;
azar

sql>alter user azar account lock;

sql>alter user azar account unlock;

Password Change

sql>alter user azar identified by ;


sql>drop user azar cascade;


System Privilege

sql>Grant create any table to azar;

Revoke Privilege

sql>revoke create any table from azar;

View :- dba_sys_privs

sql>select * from dba_sys_privs where grantee= 'username';


Object Privilege :

sql>Grant select,update,insert,delete on to azar;

sql>Revoke select,update,insert,delete on from azar;

View :- dba_tab_privs


sql>grant dba to azar;

sql>revoke dba from azar;

sql>select * from dba_role_privs where grantee ='dba';

sql>select role from session_roles;

Tablespace :-

Logical

database ---- > tablespace --- > segment ---- > extent ---- > Oracle block

* An Oracle Database can be logically grouped into smaller logical areas of space Known as Tablespace

* Tablespace Consists of one or more datafiles.

* It consists of zero or more segments


Creating Tablespace

sql>create tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m;

Check your tablespace view dba_tablespaces

sql>select * from dba_tablespaces;

sql>alter tablespace azar read only;

Your tablespace in read only mode you can drop your tablespace contents.

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar read write;

sql>select tablespace_name,status from dba_tablespaces;

sql>alter tablespace azar offline;

sql>alter tablespace azar online;

Increase your size tablespace in three Ways :-

sql>alter tablespace azar datafile '\oracle\oradata\azar01.dbf' size 20m autoextend on;

sql>alter tablespace azar add datafile '\oracle\oradata\azar02.dbf' size 10m;

sql>alter database datafile '\oracle\oradata\azar01.dbf' resize 30m;

View:-

dba_data_files

dba_free_space

Moving tablespace from one location to another location

• Offline your tablespace
• Cut your datafile and paste datafile in target place
• sql>alter tablespace azar rename datafile '\oracle\oradata\azar01.dbf' to 'any path' ;
• Online your tablespace

Create temporary tablespace :-

Using for only sort operation

sql> Create temporary tablespace azardba tempfile '\oracle\oradata\temp02.dbf' size 2m;

If you want change your temporary tablespace to default tablespace

sql>alter database default temporay tablespace azardba;

View:-

dba_temp_files

v$tempfile

v$temp_space_header

• When a transaction commits
• Redolog buffer becomes one-third full
• More than a megabytes of changed record in the redo log buffer
• Before database writer writer to the datafile.

Control File :-

* Small Binary file necessry for the database to start and operate successfully.

* Each Control file associated with only one database.

* Before database opened, the control file is read to determine if the database is in a valid state to use.

* Control file is updated continously by the oracle server.

* It must be available for writing whenever the database is open.

* No databse administrator or end user can not edit this file.

* If all copies of a control file lost, the database must be recovered before database opened.

* Control file should be multiplexed upto eight times.

Control Files Contents :-

• Database name and identifier
• Time stamp of database creation
• Tablespaces names
• Names and locations of datafiles and redolog files.
• Current redo log file sequence number.
• Checkpoint information
• Begin and end of undo segments
• Redo log archive information
• Backuop information.

sql> show parameter control_files;

Multiplex control files using pfile

(alreay have two control files and then)

sql> Alter system set control_files= '\oracle\oradata\{sid}\control01.ctl',

'\oracle\oradata\{sid}\control02.ctl','\oracle\oradata\{sid}\control03.ctl' scope = spfile ;

system altered.

sql> show parameter control_files;

***** Now not effet right now and so you shutdown the database and startup manually and copy the controlfile os level and paste it and rename it.

(control01.ctl (copy) and paste it same location (copy of control01.ctl ) and then rename it (control03.ctl))

sql>shutdown immediate

copy and paste and rename it

sql> startup

Sql> show parameter control_files;

Backup the control file :-

sql> Alter database backup controlfile to 'filename';

sql>alter database backup controlfile to trace;

View :-

• v$controlfile
• v$parameter
• v$controlfile_record_section
• show parameter control_files
• v$backup
• v$datafile
• v$tempfile
• v$tablespace
• v$archive
• v$log
• v$logfile
• v$loghist
• v$archived_log
• v$database

Data Dictionary :-

* During Database creation, The oracle server crerates additional object structures within datafiles.

• Data Dictionary Tables
• Dynamic Performance Tables

Data Dictionary :-

* Set of read only tables and views that record,verify and provide information associated with database.

*Describe databases and its objects

Two types of objects

• Base Tables

--- Store description of database

--- Created with create database

• Data Dictionary Views

--- Summarize base table information

--- Created using catalog.sql script

Base Tables :-

* Base tables are the first objects created in any oracle database.

* They are automatically created when the Oracle server runs the sql.bsq script at the time database is created.

* Users rarely access them directly. because it stored in a cryptic format.

* Example -- IND$ table which contains information about the index in the database.

How to Create Database :-

When you install oracle ,database automatically created. If you want create manually doing following steps...( Windows )

• SID ---> Any name that is your database name

Step 1 :

• Copy the default parameter file and prepare it ( \Oracle\ora90\database)

Step 2 :

• You should change sid name as like your database name (put in pfile folder in \oracle\admin\{sid}\pfile)
• Put it five folder in \oracle\admin\{sid}\(put it and rename like create,pfile,udump,bdump,cdump)
• Save the parameter file

Step 3 :

* create password file

type on cmd platform

Run ---> cmd Enter

cmd>orapwd file= \oracle\ora90\database\pwd{sid}.ora pwd=sys (as your choice)

Step 4:

* In windows create services for instance

cmd>oradim -new -sid {sid}

* Set Oracle Sid

cmd>set oracle_sid={sid}

Step 5:

cmd>sqlplus

Enter username : sys/sys as sysdba

Connected to an idle instance

sql>Startup nomount

sql>create database {sid}

Logfile

group 1 ('\oracle\oradata\{sid}\redo01.log') size 100m,

group 2 ('\oracle\oradata\{sid}\redo02.log') size 100m

datafile '\oracle\oradata\{sid}\system01.dbf' size 20m autoextend on

undo tablespace undotbs

datafile '\oracle\oradata\{sid}\undotbs01.dbf' size 20m

default temporary tablespace tempts1

tempfile '\oracle\oradata\{sid}\tempts01.dbf'

size 20m

/

Database created

sql>@c:\oracle\ora90\rdbms\admin\catalog.sql

sql>@c:\oracle\ora90\rdbms\admin\catproc.sql

****

http://www.adp-gmbh.ch/ora/admin/creatingdbmanually.html

Shutdown Mode :-

• Shutdown immediate
• Shutdown Abort
• Shutdown Transactional
• Shutdown Normal

Sql> Shutdown [immediate Normal Abort Transactional ]

• Database buffer cache written to the data files.
• Uncommitted Changes Roll Back
• Resources released

Shutdown Normal :-

• No New Connections can be made.
• The oracle server waits for all users to disconnect before completing shutdown
• Database and redo buffers are written to disk
• Bg processes are terminated. and the sga is removed from the meory.
• Oracle closes and dismounts the database before shutting down the instance
• The next startup does not require an instance recovery.

Shutdown Transactional :-

* Transactional shutdown prevents client from losing work.

• No client can start a new transaction on this particular instance.
• A client is disconnected when the client ends the transaction
• When all transactions have finished ,a shutdown immediately occurs
• The next startup does not require an instance recovery.

Shutdown immediate:-

• Current sql statments being processed by oracle are not completed.
• Oracle server does not wait for users currently connected to the database to disconnect.
• Oracle rolls back active transactions.
• Oracle closes and dismounts the database before shutting the instance.
• The next startup does not require intance recovery.

Shutdown abort :-

• sql statments by the oracle server immediately terminated.
• oralce does not wait for users currently connected to the database to disconnect.
• database and redo buffers are not written to disk.
• Uncommited transactions are not rolled back.
• the instance is terminated without closing the files.
• database not closed or dismounted.
• Next startup needs instance recovery. which occurs automatically.

Starting up a database (Nomount) :-

* When starting the database, you select the states in which it starts.

• Usually start an instance without mounting a database only during database creation or the recreation of control-files.

sql>Startup nomount

*Starting an instance include following tasks,

• Reading the initializtion files
• Allocating the SGA
• Starting the background processes
• Opening the alertSID.log file and the trace files.
• The database must be named with the db_name parameter.

Starting up a database (Mount) :-

* The database must be mounted but not open during the following tasks,

• Renaming datafiles
• Enabling and disabling redo log archive options
• Performing full database recovery
• Locating and opening the control file specified initializtion parameters file.
• Reading the control files to obtain the name and status of the redo log files and datafiles.

Starting up a database (Open) :-

* An instance started and database mounted and then datbase opened. During database open ,the user can be attempt to open database and performing.

*During the database open following tasks,

• Datafiles and ONline redolog files are Available

Note :- When you open the database, a datafiles and online redo log file not avilable ,the oracle returns error. Mean time database needs recovery options.

*** If necessary,System Monitor background process (SMON) initiates instance recovery.

Startup Command :-

To startup an instance, use the following commands

• Startup [force] [restrict] [pfile=filename] [open [recover] [database] Mount Nomount ]

Note :- This is not the Complex syntax.

• Force ---> aborting the running instance before performing a startup
• Restrict ---> Enables only users with restricted session privilege to access the database
• Recover ---> can be media recovery when the database starts

**** Attempting to open Oracle utilities without start the service, Oracle returns a error Ora-12547 --TNS

The Alter Database Command :-

* Chang the states of the database from Nomount ot Mount stage,

sql> alter database db01 mount;

* Open the database read only mode

sql> alter database db01 open read only;

Alter database db01 open [read only read write]

where

• Read Write ---> Opens the database read write mode so users can generate redo logs.
• Read Only ----> Restrict users to read only transactions,preventing them from generating Redo log information.

Restrict Command :-

sql>startup restrict;

* Use the Alter system command in restriction mode,

sql> alter system enable restricted session;

**** When you perform struture maintanice or database export or import. The database can be restricted session so that it is available only to the restricted session privilige users.

Alter system [enable disable ] restricted session;

• Enable ---> who have restircted privilige users
• Disable ----> users who do not have the priviliges also log on.

Terminate Session :-

* After placing an instance in resticted mode, you may want to kill all current user sessions before performing administrative tasks.

• Alter system kill session 'integer1,integer 2'

Where

• Integer 1 ----> Value of the column in the v$session view
• Integer 2 ----> value of the serail# in the v$session view

***** http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

Efects of Terminating a Session :-

* Pmon perform following tasks,

• Roll back the users current transaction
• Release all currently held table or rows
• Free all resources currently reserved by the user

Opening database in Read Only Mode :-

• Executes query
• Execute disk sort using locally managed tablespace
• Take datafiles offline and Online ,.Not tablespace
• Perform recovery offlines of datafile and tablespace.

Initialization Parameter Files :-

sql>Connect / as sysdba

sql>startup

* To start an instance, the Oracle server must read on the Initializtion parameter Files.

* Changes to entries in the file take effect based on the type of initialization parameter file used.

• Static Parameter file , PFILE ----> commonly refered as initSID.ora
• Persistent Parameter file , SPFILE ----> Commonly refered as spfileSID.ora

Parameter File Contents,

• A list of instance parameters.
• The name of the database instance assoiciated with
• Allocations for memory strutures of the System global area
• what to do with filled Online redo log files
• The names and locations of control files
• Information on undo segments.

PFILE :-

• The pfile is a text file .it maintained by standard operating system editor.
• Paramerter file is read only during instance startup.
• If the file is modified,the instance must be shutdown and restarted in order to make the new parameter effective.

SPFILE :-

• Binary file
• Maintained by oracle server
• Alter system command using to changes made

sql> alter system set uno_tablespace='undo2';

sql> alter system set parameter = value [scope =memory spfile both]

• Memory ---> Changes the parameter values only in the currently running file.
• Spfile ----> changes the parameter values only in the spfile.
• Both -----> Currently running the instnace and spfile.

Creating a spfile,

sql>create spfile from pfile;

Background Processes :-

* The relationship between physical (database) and memory sturture maintain and enforced by the Oracle's Background processes.

* Mandatory backgroun processes,

• DBWR -- Database Writer
• LGWR -- Log writer
• PMON -- Process Monitor
• SMON -- System Monitor
• CKPT -- Checkpoint

Database Writer :-

* Database Writer writes when,

• Checkpoint Occur
• Dirty buffer thershold reached
• No free Buffers
• Timeout
• RAC ping request
• Tablespace offline
• Tablespace read only
• Table DROP or Truncate
• Tablespace BEGIN BACKUP

*db writer writes dirty buffer from the database buffer cache to the datafiles.

What is Dirty Buffer?

* A buffer that is currently not in use, and contains data that has been committed but not written back to the disk by the buffer writer process is called a dirty buffer.

* If a user process finds a dirty buffer in the least recently used (LRU) list, it moves the dirty buffer to the write list and continues searching for more dirty buffers.

Log Writer :-

• At commit
• When one-third full
• When there is 1MB of redo
• Every 3 seconds
• Befor DBWN writes

System Monitor :-

* Instance recovery

• Rolls forward changes in the redo logs.
• Opens the database for user access
• Roll back uncommitted transactions.

* Coalesces free space ever 3 sec

* Deallocates temporary segments.

Process Monitor :-

* Cleans up after failed processes by

• Rolling back the transaction
• Releasing locks
• Releasing Other Resources
• Restarts dead dispatchers

What is Checkpoint?

* A checkpoint occurs when the db writer process writes all modified buffers in the sga buffer cache to the database datafiles.

* Data file headers are also updated with the latest checkpoint SCN, even if the file had no changed blocks.

* Checkpoints occur AFTER (not during) every redo log switch.

* set parameter log_checkpoints_to_alert=true to observe checkpoint start and end times in the database alert log.

* Checkpoints can be forced with the Alter system checkpoint command.

What is SCN?

* System Change Number ---> A number,internal to Oracle that is incremented Over time as change vectors are generated,applied and written to the redo log.

* System commit Number ---> A number,internal to oracle that is incremented with each database commit.

Sytem Global Area ( Shared Global Area):-

• Shared Pool ( Library Cache and Data Dictionary Cache)
• Database buffer Cache
• Redo log buffer cache

two optionally memory struture

• Java Pool
• Large Pool

* Sga is used to store database information that is shared by database process.It Contains data and control information for the database.

* Its allocated for the virtual memory of the computer where oracle resides.

sql> show sga; (this is view of sga).

Dynamic Sga :-

* It allows sga configuration changes without Shutdown the instance . It Can be shared pool ,database buffer cache ,redo log buffer cache.

buffer ( temporarly storage one,example browsing any page when click on any link its seeking that page very slowly ,when you enterd backside button,its coming very fastly).

* A granule is a unit of contiguous(in contact or sharing an edge) virtual memory allocation.

* one granule size 4MB means if estimated sga size is <128mb.

*One granule size is 16MB Otherwise. >128MB.

*buffer cache,large pool,shared pool are allowed to shrink and grow based on granules boundries.

*Allocation of granules via ALTER SYSTEM

*Target size in granules tracked and displayed by the V$BUFFER_POOL view.

Shared Pool :-

* Its used to store the most recently executed sal statments and data definitions.

• Library cache
• Data dictionary cache

sql> alter system set shared_pool_size=64MB;

* It Contains both fixed and variable size.

* Variable Struture ---- It grow and shrink depends on user programme requirments.

** Total sga size cannot exceed sga_max_size.

Library Cache :-

* the query or sql statment against the database.

* It stores information about most recently executed used shared sql and pl/sql statments.

* It managed by least recently used (LRU) algorithm.

* size determined by shared pool size.

* If the size of the shared pool too small,statments are reloaded into the library cache ,which affects performance.

* As the cache fills, Less recently used execution paths and parse trees removed from the library cache to make room for the new entries. They eventually aged out.

Data Dictionary Cache :-

* Its a collection of most recently used definitions in the database.

* It includes information about

• Database files
• Indexes
• tables
• columns
• users
• priviliges and other database objects.

* During the parse phase ,the server process looks at the data dictionary information to resolve object names and validate access.

* Caching the data dictionary information into memory improves response time on queries.

* Size determined by shared pool sizing.

Database Buffer Cache :-

* It stores copies of data block that retrived from the datafiles.

* It enables great performance gains when you obtain and update data.

* It is managed by LRU algorithm

* db_block_size determined by the primary block size.

• query process --->server process looks ----> database buffer cache for any need blocks.
• If not found, ----> server process reads the block -----> datafiles.and places copies in db cache.

* Consistent of independent of sub-caches

• db_cache_size --> default buffer cache size only and cannot set to be zero.
• db_keep_cache_size ---> To retain blocks in memory that are likely to be reused.
• db_recycle_cache_size ---> eliminate block from the memory that have little reused.

* Db cache dynamically resized

• sql>alter system set db_cache_size = 96M;

* Size of each buffer cache is equal to the size of an Oracle Block.

* Non-standard block size any power of two value between 2kb and 32kb.

Redolog Buffer Cache :-

* It records all changes made to the database data blocks.

* Its primary purpose recovery.

* Changes recorded within are called redo entries.

* Redo entries contain information to the reconstruct or redo changes.

* size is defined by Log_buffer.

• Client-application server-server
• client-server
• host-based