DBA

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.