RMAN NoCatalog Mode Backup

On Linux terminal or DOS prompt on windows we can execute the below commands. RMAN can be used for backup of oracle database in 2 modes:

1. No Catalog Mode
2. Catalog Mode

In no catalog mode the information regarding the backup will be stored in target db control file.  There is a limit to the amount of information can be stored in a control file. By default this information is stored for 7 days.  We can increase this time by setting the below parameter.

show parameter control_file_record_keep_time;

 

alter system set control_file_record_keep_time = 14 scope = both;

Once we login to RMAN the commands are same for both no catalog and catalog modes.

To use nocatalog mode do the followling:

export ORACLE_SID = prod

rman target / nocatalog

rman (enter)

rman> connect target /

RMAN> backup database;

It generates backup pieces and stores in $ORACLE_HOME/dbs folder and backup set (backup info) into the target db control file.

To see the datafiles and their respective numbers:

RMAN> report schema;

To backup a single datafile:

RMAN> backup datafile 4;

To backup a single tablespace:
RMAN> backup tablespace users;

To backup controlfile:

RMAN> backup current controlfile;

To backup all archivelog files:
RMAN> backup archivelog all;

To backup spfile:
RMAN> backup spfile;

To backup archivelogs and delete them:
RMAN> backup archivelog all delete input;

To backup database and archivelog files together:

RMAN> backup database plus archivelog;

To backup database, controlfile and archivelog files in a single command:

RMAN> backup database include current controlfile plus archivelog;

To store backup (piece) in a particular location for one time:
RMAN> backup database format '/u01/bkp/full_bkp_%U';

List Report Commands : To get information about backups:

RMAN> list backup; 

RMAN> list backup summary;

RMAN> list backup of datafile 2;

RMAN> list backup of archivelog all;

RMAN> list backup of tablespace users;

RMAN> report schema;

RMAN> report need backup;

RMAN> report obsolete;

To delete the obsolete or old backups:

RMAN> delete obsolete;

RMAN> delete backup; (will delete complete backupsets and pieces)

RMAN> quit;

RECYCLEBIN Concept

SQL> SELECT * FROM tab;
DEPT
EMP
BONUS
SALGRADE

SQL> DROP TABLE emp;
Table dropped.

SQL> SELECT * FROM tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
BIN$rPTa2P2JR0iX9PYUu38Wsw==$0 TABLE
DEPT                                TABLE
BONUS                            TABLE
SALGRADE                    TABLE

SQL> SHOW RECYCLEBIN
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
EMP              BIN$rPTa2P2JR0iX9PYUu38Wsw==$0 TABLE        2015-04-10:23:54:38

SQL> FLASHBACK TABLE EMP TO BEFORE DROP;
Flashback complete.

SQL> SELECT * FROM TAB;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE
DEPT                          TABLE
BONUS                       TABLE
SALGRADE               TABLE

The table is restored successfully.  In this concept the table doesn't go permanently from the database. It stays there in recyclebin. It still takes space.  If we want to get back that space and remove the table permanently from recyclebin do the following:

SQL> PURGE RECYCLEBIN;

If you don't want to send the table to recyclebin and delete it permanently then use the below command:

SQL> DROP TABLE EMP PURGE;

Control File

alter database backup controlfile to trace;

alter database backup controlfile to trace as '/u01/bkp/mycf.sql';

We need at least one control file to up and run the database.  It's suggested from oracle to have at least 3 control files, which are all same.  They can be kept on 3 different places or disks.  If any one file get corrupted we can recreate it by just copying it from another place.

If we lost all the 3 files we can recreate them using the below command. When we send the control file to trace it will have the following command.  Which contains both the commands for resetlogs or noresetlogs.  Control file is also a binary file which we cannot see in a word editor.  But once we send it to a trace file it can be editable and viewable in editors.

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/prod/REDO01.LOG' SIZE 50M,
GROUP 2 '/u01/prod/REDO02.LOG' SIZE 50M,
GROUP 3 '/u01/prod/REDO03.LOG' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/prod/SYSTEM01.DBF',
'/u01/prod/SYSAUX01.DBF',
'/u01/prod/USERS01.DBF',
'/u01/prod/UNDOTBS01.DBF'
CHARACTER SET AR8MSWIN1256
;

This command creates 3 control files.  Even we lost our exact control file also we can find the information of our CRD files and put them in this command to recreate the current control file.

To add a control file or multiplex a control file:

1.
col name for a40

select name from v$controlfile order by name;

/u01/prod/control01.ctl

/u01/prod/control02.ctl

suppose we have 2 files only and we want to add a 3rd control file

alter system set control_files='/u01/prod/control01.ctl','/u01/prod/control02.ctl','/u01/prod/control03.ctl'

scope=spfile;

shutdown immediate;

2. 

$cp /u01/prod/control02.ctl /u01/prod/control03.ctl

3.

startup

4.

select name from v$controlfile order by name;

Spfile and Pfile

Spfile means Server Parameter File.  Oracle reads this file first while starting oracle instance.  The sequence of files which oracle looks to start the instance are:

spfileOrcl.ora
spfile.ora
INITorcl.ora

Oracle will look for parameter file in the above sequence.  Here orcl is the oracle SID. This file is located in $ORACLE_HOME\database directory for windows and $ORACLE_HOME\dbs directory for Linux.

This is a binary file which we can't edit using a text editor.  To view this file in a text editor and take a backup of the same we can use the following command:

CREATE PFILE FROM SPFILE;

(this command will create parameter file editable version in database or dbs folder. If we want the file in our selected folder use the following command.

CREATE PFILE='c:\oracle\INITorcl.ora' FROM SPFILE;

We can start the oracle instance using this file also.  But it will be one way operation. Oracle will use this file to start its instance but from the instance we can't write anything to this file.  For two way operation we have to use spfile.  Oracle reads this spfile while starting its instance and from the commands it can save information to it also.

We can also create spfile from a pfile:

create spfile from pfile; 

(if the pfile is in database directory)

create spfile from pfile='c:\oracle\INITorcl.ora'; 

(When the pfile exists in another folder).

We can also create pfile and spfile from memory (running oracle instance);

create pfile from memory;

create spfile from memory;

(we can't create spfile from memory if we started the instance from it)

Changing a dynamic parameter (sga_target) in pfile:-

alter system set sga_target=3g;

(this change happened only in memory, to make it permanent open the initprod.ora pfile and bounce back the db)

$cd $ORACLE_HOME/dbs

vi initprod.ora

sga_target=3g

:wq

Changing a static parameter (sga_max_size) in pfile:-

The static parameter (sga_max_size) can be changed only in parameter file and the db must be bounce back.

Changing a dynamic parameter in spfile:-

alter system set sga_target = 300m;

alter system set sga_target = 500m scope=memory;

alter system set sga_target = 500m scope=both;

Changing a static parameter in spfile:-

alter system set sga_max_size = 1g scope=spfile;

alter system set sga_max_size = 500m comment='max size is 1GB' scope=spfile;