Creating_Tablespace

•   CREATING LOCALLY MANAGED TABLESPACE WITH AUTOMATIC SEGMENT   SPACE MANAGEMENT

SQL> create tablespace tbs100 datafile '/u01/app/oracle/oradata/orcl/tbs100.dbf' size 50M segment space management auto;

• CREATING TABLESPACE WHERE EXTENT ALLOCATION IS AUTOMATIC

SQL> create tablespace tbs200 datafile '/u01/app/oracle/oradata/orcl/tbs200.dbf' size 50M extent management local autoallocate segment space management auto;

• CREATING TABLESPACE WHERE EXTENT ALLOCATION IS UNIFORM

SQL> create tablespace tbs300 datafile '/u01/app/oracle/oradata/orcl/tbs300.dbf' size 50M extent management local uniform size 64K segment space management auto;

• CREATE TABLES IN TABLESPACE TBS100

 
SQL> create table first (id number) tablespace tbs100;

• ENABLING AUTOEXTEND

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/tbs100.dbf' autoextend on;

• ENLARGING DATAFILE SIZE   

SQL> alter database datafile '/u01/app/oracle/oradata/orcl/tbs100.dbf' resize 100M;

Incomplete Recovery through RMAN

SQL> create table t (id number(3));

SQL> set time on;

15:36:16 SQL> insert into t values (101);

15:36:19 SQL> insert into t values (102);

15:36:21 SQL> insert into t values (103);

15:36:27 SQL> insert into t values (104);

15:36:29 SQL> insert into t values (105);

15:36:33 SQL> select * from t;

15:36:36 SQL> commit;

>>>>> export ORACLE_SID=orcl

>>>>> rman target /

RMAN> delete backup; RMAN> backup database;

15:36:48 SQL> insert into t values (106);

15:38:22 SQL> insert into t values (107);

15:38:25 SQL> insert into t values (108);

15:38:29 SQL> insert into t values (109);

15:38:31 SQL> commit;

15:38:34 SQL> alter system checkpoint;

15:38:38 SQL> alter system flush buffer_cache;

15:38:42 SQL> commit;

15:39:17 SQL> drop table t purge;

15:39:32 SQL> select * from t;

RMAN> run2> {3> shutdown abort;4> startup mount;5> set until time "to_date('30-10-2012 15:38:31','DD-MM-RRRR HH24:MI:SS')";6> restore database;7> recover database;8> sql 'alter database open resetlogs';9> }

15:59:24 SQL> conn / as sysdba

15:59:32 SQL> select * from t;

How To Create RECOVERY CATALOG

• Check the ORACLE_HOME and ORACLE_SID

export ORACLE_SID=test

sqlplus / as sysdba

• Create tablepspace

SQL>  create tablespace mumbai datafile '/u01/app/oracle/oradata/test/bangalore.dbf' size 100m;

SQL>  create user suresh identified by vivek default tablespace bangalore quota unlimited on bangalore;

SQL>  grant connect, resource to vivek;

SQL>  grant recovery_catalog_owner to vivek;

R==> export ORACLE_SID=test

rman target /

connect catalog suresh/suresh

create catalog tablespace bangalore;

register database;

backup tablespace users;

• How to check table of recovery catalog

SQL>  select table_name from dba_tables where tablespace_name='Bangalore';

• Now from user's login

 RMAN> show device type;

RMAN> show default device type;

RMAN> crosscheck backup;

RMAN> configure device type sbt_tape clear;

  

• To check the location of backup:

SQL> show parameter db_recovery_file_dest;

SQL> show parameter control_file_record_keep_time;

SQL> show parameter db_recovery_file_dest_size;

SQL> show parameter NLS_DATE_FORMAT;

SQL> show parameter NLS_LANG;

Rman_Scripts

• Taking Partial Backups(Specific datafiles):

     

run

        {

         allocate channel c1 type disk

         format='/u01/app/backup/example01.bak';

         backup datafile '/u01/app/oracle/oradata/testdb/example01.dbf';

         release channel c1;

        }

rman target / cmdfile=/u01/app/cmd/rm1

• Taking Database backup(all datafiles):

    run

   {

    allocate channel c1 type disk

    format ='/u01/app/backup/fullbac%U.bak';

    backup database;

    release channel c1;

   }

rman target / cmdfile=/u01/app/cmd/rm2

• Backup of current control file:

   run

   {

    allocate channel c1 type disk;

    backup

    format='/u01/app/backup/contl.bak'

    current controlfile;

    release channel c1;

   }

rman target / cmdfile=/u01/app/cmd/rm3

•  Whole database backup(datafile+archive file):

    run

   {

    allocate channel c1 type disk;

    backup

    format='/u01/app/%d_%p_%sabc.bak'

    database plus archivelog;

    release channel c1;

   }

rman target / cmdfile=/u01/app/cmd/rm4

• Parallelism of Backup

  run

   {

    allocate channel c1 type disk;

    allocate channel c2 type disk;

    allocate channel c3 type disk;

    backup

    incremental level 0

    format 'c:\bac\df_%d_%p_%s.bak'

   (datafile 1,2 channel c1 tag=dbf1)

   (datafile 3,4 channel c2 tag=dbf2)

   (datafile 5 channel c3 tag=dbf3);

    release channel c1;

    release channel c2;

    release channel c3;

   }

rman target / cmdfile=/u01/app/cmd/rm5

• Multiplexed backups

 run

  {

   allocate channel c1 type disk;

   backup copies 2 datafile 5

   format '/u01/app/copy1.bak' , '/u01/app/oracle/copy2.bak';

   release channel c1;

  }

rman target / cmdfile=/u01/app/cmd/rm6

• Taking image copies:

 run

  {

   allocate channel c1 type disk;

   copy datafile '/u01/app/oracle/oradata/testdb/example01.dbf'

                  to '/u01/app/bac/dat.bak';

   copy archivelog '/u01/app/oracle/flash_recovery_area/testdb/arc00044.001'

                  to '/u01/app/bac/arc.bak';

   release channel c1;

  }

rman target / cmdfile=/u01/app/cmd/rm7

• Backup archive file to diff location and then delete from primary location:

 run

   {

    allocate channel c1 type disk;

    backup

    format '/u01/app/bac/arc_%d_%t_%s.bak'

    archivelog all delete all input;

    release channel c1;

   }

rman target / cmdfile=/u01/app/cmd/rm8

Stand Alone Commands

• REPORT Command:

RMAN> report schema;

RMAN> report need backup;

RMAN> report need backup redundancy 2;

RMAN> report need backup recovery window of 3 days;

• LIST Command:

RMAN>list backup;

RMAN>list backup of tablespace users;

RMAN>list backup of datafile 4;

RMAN>list copy of datafile 4;

RMAN>list backup of database;

RMAN>list archivelog all;

RMAN>list backup of archivelog all;

• DELETE Command:

RMAN> delete backup;

RMAN> delete backup of archivelog all;

RMAN> delete backup of datafile 5;

RMAN> delete backupset 1;

• CROSS CHECK Commands:

RMAN> crosscheck archivelog all;

RMAN> crosscheck backup;

• EXPIRED BACKUP:

RMAN> list expired archivelog all;

RMAN> list expired backup;

• OBSOLETE BACKUPS:

RMAN> report obsolete;

RMAN> delete obsolete;

• BACKUP COMMANDS

RMAN> backup datafile 5,6,7;

RMAN> backup tablespace mumbai;

RMAN> backup database;

RMAN> backup datafile 5 tag='chicken';

RMAN> backup tag 'vivek' datafile 1,4,6;

RMAN> list backup tag='vivek';

RMAN> delete backupset tag 'vivek';

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

RMAN> backup incremental level 0 database;

RMAN> backup incremental level 0 tablespace mumbai;

RMAN> backup tag 'sqlstar' incremental level 0 datafile 7;

RMAN> backup incremental level 1 tablespace mumbai;

RMAN> backup incremental level 1 database;

RMAN> backup tag 'starsql' incremental level 1 tablespace mumbai;

RMAN> backup incremental level 1 cumulative database;

RMAN> backup incremental level 1 cumulative tablespace mumbai;

RMAN> backup tag 'vivek' incremental level 1 cumulative datafile 6;