Non-System tablespace recovery (online)

This test shows how to restore your non-system tablespace while the database is running.

Scenario

1. Create a user with the USERS tablespace it's default. Then add some test table and data.

2. Perfom your normal database backup

3. Add more data to the new schema

4. Now move the USERS tablespace e.g. mv users_01.dbf users_01.dbf.TMP

5. As SYSTEM resize users datafile, this will cause error since can't find tablespace e.g.

 SQL> alter database datafile '/u01/oracle/oradata/D1AR/users01.dbf' resize  105m;
 alter database datafile '/u01/oracle/oradata/D1AR/users01.dbf' resize  105m
 *
 ERROR at line 1:
 ORA-01565: error in identifying file '/u01/oracle/oradata/D1AR/users01.dbf'
 ORA-27037: unable to obtain file status
 Linux Error: 2: No such file or directory
 Additional information: 3

Recovery

6. Offline the tablespace, also no file 4 is not in the list

   SQL> alter tablespace users offline; -- or offline immediate;

 Tablespace altered.

 SQL> select * from v$backup;

      FILE# STATUS                CHANGE# TIME
 ---------- ------------------ ---------- ---------------
      1 NOT ACTIVE                  0
      2 NOT ACTIVE                  0
      3 NOT ACTIVE                  0

7. Restore the tablespace

  $ rman catalog rman/rman@rmandev

 connect target /

 restore tablespace users;
 recover tablespace users;

8. Restore and recover of TS done, now online TS

 SQL> alter tablespace users online;

 Tablespace altered.

9. Restart the db to make sure all is fine.

SQL> conn / as sysdba
Connected.
SQL> shut immediate;

SQL> startup

10. Do select on test table so see, all data are there.

11. Now do a backup if you wish

Dua's in prayer

Glory to my Lord, the Great

Allah hears those who praise Him (Sami' allahu liman hamida)

Our Lord, praise be to You (Rabbana lakal hamd)

Glory to my Lord, the Highest

My Lord forgive me, have mercy upon me, guide me, give me health and grant me sustenance.


Sura 6.79: Verily, I have turned my face towards Him who has created the heavens and the earth Hanifa, and I am not of Al-Mushrikun.

RMAN Complete Recovery

Database Disaster Recovery

Scenario

Backup was successful Wednesday night, RMAN backup started 22:00 GMT, and Tape backup started 11.30 GMT. Thursday morning due to disaster whole database box was lost.

Restore procedure from RMAN

1. Build new box from tape backup
2. Restore the Oracle software from tape
3. Restore the last three days RMAN backups
4. Test to see Oracle Software are valid, see if Sqlplus, rman works
5. Start the listener
6. Make sure all the databases are down, they mightily be partly up since they auto start when the box is booted
7. Follow the below RMAN recovery procedure

Export the SID e.g. export ORACLE_SID=P1AR

rman

connect catalog rman/@RMANPRD.jpldbpr01

connect target /

startup nomount;

set dbid=22397860;

restore controlfile from autobackup;

restore database;

alter database mount;

recover database;

alter database open resetlogs;

8. Test the restore

Take a RMAN backup

Important RMAN commands

set newname for datafile '/u01/oracle/D1NI/tools_01.dbf' to '/u01/oracle/D1NI/tools_01.dbf'

backup (archivelog all);
backup format '' (archivelog all);

RMAN Incomplete Recovery

Below is the expple for a timebase recover for a distaer (delete dbf, redo and ctl files).
16:18 (12 July 2007)
create table t1 (a number);

16:45 (12 July 2007)
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);

16:56 (12 July 2007)
insert into t1 values (222);
insert into t1 values (222);
insert into t1 values (222);
insert into t1 values (222);

16:59 (12 July 2007)
insert into t1 values (999);
insert into t1 values (999);
insert into t1 values (999);
insert into t1 values (999);

17:05 (12 July 2007) Delete all files form /u01 D1AR inc system 1 redo, 1 control
[D1AR]$ ls
control01.ctl D1AR_INDX_01.dbf redo01.log system01.dbf undotbs01.dbf
D1AR_DRSYS_01.dbf D1AR_INDX_02.dbf sysaux01.dbf temp01.dbf users01.dbf

[D1AR]$ rm *.dbf
[D1AR]$ rm *.log
[D1AR]$ rm *.ctl



$ export NLS_LANG=.UTF8
$ export NLS_DATE_FORMAT='Mon DD YYYY HH24:MI:SS'
$ rman catalog rman/rman@rmandev
RMAN> connect target /
run {
restore database;
restore controlfile;

set until time 'JUL 12 2007 16:57:00';
recover database;
}

sql "alter database open resetlogs";

shutdown immediate;
startup;

Successful TEST, database is usable and output is:

SQL> select * from t1;

A
----------
1
1
1
1
222
222
222
222

The Opening (Al-Fâtihah)

1.
In the Name of Allâh (God), the Most Beneficent, the Most Merciful.
2.
All the praises and thanks be to Allâh, the Lord of the 'Alamîn (mankind, jinns and all that exists).
3.
The Most Beneficent, the Most Merciful.
4.
The Only Owner (and the Only Ruling Judge) of the Day of Recompense (i.e. the Day of Resurrection)
5.
You (Alone) we worship, and You (Alone) we ask for help (for each and everything).
6.
Guide us to the Straight Way
7.
The Way of those on whom You have bestowed Your Grace, not (the way) of those who earned Your Anger, nor of those who went astray.

More reading: http://en.wikipedia.org/wiki/Al-Fatiha

How to find the current SCN

-- Works on 9i and 10g
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

-- Works on 10g
select CURRENT_SCN from v$database;

-- Other useful tables
select * from v$log_history
select * from v$log;

Temp tablespace usage Oracle 9i/10g

Below is the query that I would use to find out usage of temp tablespace:

select
  df.TABLESPACE_NAME,
  df.df_size/1048576 AS TOTAL,
  nvl(us.free_sum, 0)/1024/1024 AS USED,
    (df.df_size/1048576 - (nvl(us.free_sum, 0)/1024/1024)) AS FREE,
    df.FILE_COUNT
from
    ( select TABLESPACE_NAME,
            sum(bytes) as df_size,
            count(TABLESPACE_NAME) AS FILE_COUNT
     from dba_temp_files
     group by TABLESPACE_NAME ) df,
    ( select TABLESPACE,
            sum(BLOCKS)*(select value from v$parameter where upper(name) like '%DB_BLOCK_SIZE%') free_sum
     from v$sort_usage group by TABLESPACE ) us
where df.TABLESPACE_NAME = us.TABLESPACE(+)
order by TABLESPACE_NAME;

Sample output:

TABLESPACE_NAME                     TOTAL       USED       FREE FILE_COUNT
------------------------------ ---------- ---------- ---------- ----------
TEMP                                 1024          0       1024          1