Recovering from Non critical Losses

Losing a TEMPFILE:-

Recreating tempfile

/u01/app/oracle/oradata/orcl/temp01.dbf

Recovering from a TEMPFILE Loss:-

You can recover from a lost TEMPFILE without restarting the database.

For example, to recover the database when the temp01.dbf data file belonging to the

default temporary tablespace TEMP has been deleted at the OS level, add a new data file,

and then drop the one that was deleted:

SQL> ALTER TABLESPACE temp ADD DATAFILE

'/u01/app/oracle/oradata/orcl/temp02.dbf' SIZE 20M;

Tablespace altered.

SQL> ALTER TABLESPACE temp DROP TEMPFILE

'/u01/app/oracle/oradata/orcl/temp01.dbf';

Tablespace altered.

Losing a Redo Log Group Member:-

Re-creating Redo Log Files

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS

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

1 YES ACTIVE

2 NO CURRENT

3 YES INACTIVE

ALTER DATABASE DROP LOGFILE GROUP 3;

shows the progression of log group #1 from CURRENT to

ACTIVE to INACTIVE:

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS

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

1 NO CURRENT

2 YES INACTIVE

3 YES INACTIVE

SQL> alter system switch logfile;

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS

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

1 YES ACTIVE

2 NO CURRENT

3 YES INACTIVE

SQL> alter system checkpoint;

System altered.

SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;

GROUP# ARC STATUS

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

1 YES INACTIVE

2 NO CURRENT

3 YES INACTIVE

ALTER DATABASE DROP LOGFILE MEMBER

'/u01/app/oracle/oradata/orcl/redo02b.log';

Re-creating Indexes:-

Use options to reduce the time it takes to create the

index:-

• PARALLEL

• NOLOGGING

SQL> CREATE INDEX rname_idx

2 ON hr.regions (region_name)

3 PARALLEL 4;

Re-creating a Password Authentication File:-

Oracle~$ orapwd file=$ORACLE_HOME/dbs/orapwORCL

          password=admin entries=5