RMAN backup and recovery for oracle database

RMAN
Backup and recovery manager...

Benefit of RMAN:
Database on Any OS, can be taken backup with only RMAN...
RMAN should be used for ASM.

RMAN Client --- > CATALOG Database
RMAN Server --- > TARGET Database

One RMAN catalog can take the backup and keep the backup information of any number of target databases on any OS.



RMAN Demo:

>>> RMAN Catalog Creation.

1 Identify the database to be used for CATALOG operations.
2 Create a tablespace of size 50m, to be used for RMAN catalog tables.
3 Create a user and grant rman permissions, to perform rman operations.
4 Connect to RMAN prompt and create catalog tables.


1. Identified orcl as catalog database.
set ORACLE_SID=orcl
sqlplus "/as sysdba"

2. Create a tablespace of size 50m.
create tablespace ts20 datafile'C:\app\prabhu\oradata\orcl\ts20_01.dbf' size 50m;

3. Create user and grant permissions..
SQL> create user suvendu identified by suvendu default tablespace ts20;
User created.
SQL> grant connect, resource to suvendu;
Grant succeeded.
SQL> grant recovery_catalog_owner to suvendu;
Grant succeeded.

4. Connect to RMAN prompt and create catalog tables.
SQL> host
Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Prabhu>rman catalog suvendu/suvendu

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 1 15:34:05 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to recovery catalog database

RMAN> create catalog;
recovery catalog created

RMAN> exit
Recovery Manager complete.

C:\Users\Prabhu>exit
SQL> conn suvendu/suvendu
Connected.
SQL> select count(*) from tab;
  COUNT(*)
----------
       142
SQL> conn /as sysdba
Connected.
SQL>

========== Completes Catalog Creation =============

>>> Target Configuration to take backup using catalog.

1. Identify target database to be backed up.
2. It has to be in Archive Mode.
3. Check target database password file availability.
4. Configure and check connectivity for SYSDBA from catalog to target.
rman target / catalog user/pwd@tns -- from target to catalog
rman target user/pwd@tns catalog user/pwd -- from catalog to target
rman target / nocatalog -- No Catalog, Target cfile will be used for info
5. Connect to RMAN and Register target database with rman catalog.
6. Configure rman parameters.
7. Take backup to backup location.

1. Identify target database to be backed up.
C:\Users\Prabhu>set ORACLE_SID=oracle
C:\Users\Prabhu>sqlplus "/as sysdba"

2. It has to be in Archive Mode.
SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled

3. Check target database password file availability.
SQL> select * from v$pwfile_users;
USERNAME                       SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                            TRUE  TRUE  FALSE
SQL> alter user sys identified by sys;
User altered.

Alternate Method to create password file..

C:\app\Prabhu\product\11.1.0\db_1\database>orapwd file='PWDoracle.ora' password=
sys force=y

4. Configure and check connectivity for SYSDBA from catalog to target.
tnsping oracle
sqlplus sys/pwd@oracle as sysdba

5. Connect to RMAN and Register target database with rman catalog.

C:\Users\Prabhu>rman catalog suvendu/suvendu target sys/sys@oracle

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 1 15:56:28 2012
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: ORACLE (DBID=1644963733)
connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

6. Configure rman parameters.
Rman> show all;

RMAN configuration parameters for database with db_unique_name ORACLE are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION on; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default (sbt_tape)
CONFIGURE CONTROLFILE AUTOBACKUP on; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'C:\app\Prabhu\rmanbkp\%F.ctl'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 2 BACKUP TYPE TO BACKUPSET; # default
configure channel 1 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf';
configure channel 2 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf';
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE BACKUPSET SIZE TO 20000m; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BZIP2'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\PRABHU\PRODUCT\11.1.0\DB_1\DATABASE\SNCFORACLE.ORA'; # default


7. Take backup to backup location.
open new terminal.

set ORACLE_SID=orcl
rman catalog suvendu/suvendu target sys/sys@oracle
rman> backup database;


============= Rman Configuration and backup complete ============

Q. What all the types of rman backup?
Ans Rman backups are of two types:
BACKUPSET -  Content bkp
COPY backup. -  As Is bkp.

Test For RMAN backup :Output



C:\Users\Prabhu>set ORACLE_SID=orclMicrosoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\Users\Prabhu>set ORACLE_SID=orcl

C:\Users\Prabhu>rman catalog ravi/ravi target sys/sys@oracle

Recovery Manager: Release 11.1.0.7.0 - Production on Thu Mar 1 16:14:46 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: ORACLE (DBID=1644963733)
connected to recovery catalog database

RMAN>

RMAN> backup database;

Starting backup at 01-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSAUX01.DBF
input datafile file number=00005 name=C:\APP\PRABHU\ORADATA\ORACLE\EXAMPLE01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSTEM01.DBF
input datafile file number=00004 name=C:\APP\PRABHU\ORADATA\ORACLE\USERS01.DBF
channel ORA_DISK_2: starting piece 1 at 01-MAR-12
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\01N4PSCQ_1_1.CTL tag=TAG20120301T161521 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\PRABHU\ORADATA\ORACLE\UNDOTBS01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\02N4PSCQ_1_1.CTL tag=TAG20120301T161521 comme
nt=NONE
channel ORA_DISK_2: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\03N4PSFH_1_1.CTL tag=TAG20120301T161521 comme
nt=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 01-MAR-12

Starting Control File and SPFILE Autobackup at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\C-1644963733-20120301-00.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-12

RMAN>

RMAN> configure channel 1 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf'
;

old RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.ctl';
new RMAN configuration parameters:
CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.dbf';
new RMAN configuration parameters are successfully stored
released channel: ORA_DISK_1
released channel: ORA_DISK_2
starting full resync of recovery catalog
full resync complete

RMAN> configure channel 2 device type disk format 'C:\app\Prabhu\rmanbkp\%U.dbf'
;

old RMAN configuration parameters:
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.ctl';
new RMAN configuration parameters:
CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT   'C:\app\Prabhu\rmanbkp\%U.dbf';
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

RMAN>

RMAN>

RMAN>

RMAN> list backup summary;

starting full resync of recovery catalog
full resync complete

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
58      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
59      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
60      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
73      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161702

RMAN> backup database tag Thu_01Mar12
2> ;

Starting backup at 01-MAR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=140 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSAUX01.DBF
input datafile file number=00005 name=C:\APP\PRABHU\ORADATA\ORACLE\EXAMPLE01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: starting full datafile backup set
channel ORA_DISK_2: specifying datafile(s) in backup set
input datafile file number=00001 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSTEM01.DBF
input datafile file number=00004 name=C:\APP\PRABHU\ORADATA\ORACLE\USERS01.DBF
channel ORA_DISK_2: starting piece 1 at 01-MAR-12
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\05N4PSIP_1_1.DBF tag=THU_01MAR12 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:28
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=C:\APP\PRABHU\ORADATA\ORACLE\UNDOTBS01.DBF

channel ORA_DISK_1: starting piece 1 at 01-MAR-12
channel ORA_DISK_2: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\06N4PSIP_1_1.DBF tag=THU_01MAR12 comment=NONE

channel ORA_DISK_2: backup set complete, elapsed time: 00:01:26
channel ORA_DISK_1: finished piece 1 at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\07N4PSLH_1_1.DBF tag=THU_01MAR12 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-MAR-12

Starting Control File and SPFILE Autobackup at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\C-1644963733-20120301-01.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-12

RMAN> list backup summary;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
58      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
59      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
60      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161521
73      B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T161702
116     B  F  A DISK        01-MAR-12       1       1       NO         THU_01MAR
12
117     B  F  A DISK        01-MAR-12       1       1       NO         THU_01MAR
12
118     B  F  A DISK        01-MAR-12       1       1       NO         THU_01MAR
12
136     B  F  A DISK        01-MAR-12       1       1       NO         TAG201203
01T162011

RMAN> backup as copy database;

Starting backup at 01-MAR-12
using channel ORA_DISK_1
using channel ORA_DISK_2
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSTEM01.DBF
channel ORA_DISK_2: starting datafile copy
input datafile file number=00002 name=C:\APP\PRABHU\ORADATA\ORACLE\SYSAUX01.DBF
output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-SYSTEM_FNO-
1_09N4PSQ7.DBF tag=TAG20120301T162231 RECID=3 STAMP=776795016
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:01:08
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=C:\APP\PRABHU\ORADATA\ORACLE\EXAMPLE01.DBF

output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-SYSAUX_FNO-
2_0AN4PSQ7.DBF tag=TAG20120301T162231 RECID=2 STAMP=776795016
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:01:01
channel ORA_DISK_2: starting datafile copy
input datafile file number=00003 name=C:\APP\PRABHU\ORADATA\ORACLE\UNDOTBS01.DBF

output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-EXAMPLE_FNO
-5_0BN4PSSB.DBF tag=TAG20120301T162231 RECID=4 STAMP=776795025
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:06
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=C:\APP\PRABHU\ORADATA\ORACLE\USERS01.DBF
output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-UNDOTBS1_FN
O-3_0CN4PSSC.DBF tag=TAG20120301T162231 RECID=5 STAMP=776795028
channel ORA_DISK_2: datafile copy complete, elapsed time: 00:00:03
output file name=C:\APP\PRABHU\RMANBKP\DATA_D-ORACLE_I-1644963733_TS-USERS_FNO-4
_0DN4PSSJ.DBF tag=TAG20120301T162231 RECID=6 STAMP=776795029
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 01-MAR-12

Starting Control File and SPFILE Autobackup at 01-MAR-12
piece handle=C:\APP\PRABHU\RMANBKP\C-1644963733-20120301-02.CTL comment=NONE
Finished Control File and SPFILE Autobackup at 01-MAR-12


RMAN> exit

Recovery Manager complete.

============Hope It will help you to learn about RMAN======================

Grant statement

Grant privileges to a user (or to a user role)

Syntax:

Grant System-wide Privs:

   GRANT system_priv(s) TO grantee

      [IDENTIFIED BY password] [WITH ADMIN OPTION]

   GRANT role TO grantee

      [IDENTIFIED BY password] [WITH ADMIN OPTION]

   GRANT ALL PRIVILEGES TO grantee

      [IDENTIFIED BY password] [WITH ADMIN OPTION]

Grant privs on specific objects:

   GRANT object_priv [(column, column,…)]

      ON [schema.]object

         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

   GRANT ALL PRIVILEGES [(column, column,…)]

      ON [schema.]object

         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

   GRANT object_priv [(column, column,…)]

      ON DIRECTORY directory_name

         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

   GRANT object_priv [(column, column,…)]

      ON JAVA [RE]SOURCE [schema.]object

         TO grantee [WITH GRANT OPTION] [WITH HIERARCHY OPTION]

grantee:

   user

   role

   PUBLIC

system_privs:

   CREATE SESSION - Allows user to connect to the database

   UNLIMITED TABLESPACE  - Use an unlimited amount of any tablespace.

   SELECT ANY TABLE - Query tables, views, or mviews in any schema

   UPDATE ANY TABLE - Update rows in tables and views in any schema

   INSERT ANY TABLE - Insert rows into tables and views in any schema

   Also System Admin rights to CREATE, ALTER or DROP:

     cluster, context, database, link, dimension, directory, index,

     materialized view, operator, outline, procedure, profile, role,

     rollback segment, sequence, session, synonym, table, tablespace,

     trigger, type, user, view. (full list of system privs)

object_privs:

   SELECT, UPDATE, INSERT, DELETE, ALTER, DEBUG, EXECUTE, INDEX, REFERENCES

  

roles:

   SYSDBA, SYSOPER, OSDBA, OSOPER, EXP_FULL_DATABASE, IMP_FULL_DATABASE

   SELECT_CATALOG_ROLE,  EXECUTE_CATALOG_ROLE, DELETE_CATALOG_ROLE

   AQ_USER_ROLE, AQ_ADMINISTRATOR_ROLE - advanced queuing

   SNMPAGENT - Enterprise Manager/Intelligent Agent.

   RECOVERY_CATALOG_OWNER - rman

   HS_ADMIN_ROLE - heterogeneous services

   plus any user defined roles you have available

Notes:

Several Object_Privs can be assigned in a single GRANT statement
e.g.
GRANT SELECT (empno), UPDATE (sal) ON scott.emp TO emma

WITH HIERARCHY OPTION will grant the object privilege on all subobjects, including any created after the GRANT statement is issued.

WITH GRANT OPTION will enable the grantee to grant those object privileges to other users and roles.

"GRANT ALL PRIVILEGES…" may also be written as "GRANT ALL…"

SYSDBA role permissions:

CREATE DATABASE
CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up, or change character set
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

SYSOPER role permissions:

CREATE SPFILE
STARTUP and SHUTDOWN
ALTER DATABASE: open, mount, back up
ARCHIVELOG and RECOVERY
Includes the RESTRICTED SESSION privilege

The roles CONNECT, RESOURCE and DBA are now deprecated (supported only for backwards compatibility) unless you are still running Oracle 6.0 use CREATE ROLE instead. Using deprecated roles is likely to result in a database installation that is not secure. Also beware of 3rd party packages that issue GRANT CONNECT instead of GRANT CREATE SESSION.

•       Create a Role

•       Grant object and system priviliges to the role

•       Grant the role to individual Users.

When using roles, parse performance is faster (one role vs multiple permissions)

Changes in a grant applied to a role will be immediately picked up by all logged-in users.
Changes in a grant applied directly to a user require a logoff and re-login.

Grants of UNLIMITED TABLESPACE must be direct.
Grants to users running background jobs (dbms_job.submit) have to be direct; when the job starts running no roles are active.

GRANT CREATE session, CREATE table, CREATE view,

      CREATE procedure,CREATE synonym,

      ALTER table, ALTER view, ALTER procedure,ALTER synonym,

      DROP table, DROP view, DROP procedure,DROP synonym,

      TO MyRole;

Related Commands:

AUDIT
CREATE ROLE
CREATE USER
REVOKE
ORA-01031 - Insufficient privileges
To grant permissions in bulk to the objects owner by a user, see the USER_OBJECTS view (with example script.)

Related Views:

DBA_COL_PRIVS     ALL_COL_PRIVS     USER_COL_PRIVS
COLUMN_PRIVILEGES
                  ALL_COL_PRIVS_MADE  USER_COL_PRIVS_MADE
                  ALL_COL_PRIVS_RECD  USER_COL_PRIVS_RECD
DBA_ROLE_PRIVS                      USER_ROLE_PRIVS     ROLE_ROLE_PRIVS
DBA_SYS_PRIVS                       USER_SYS_PRIVS     ROLE_SYS_PRIVS
SESSION_PRIVS
DBA_TAB_PRIVS     ALL_TAB_PRIVS     USER_TAB_PRIVS
TABLE_PRIVILEGES
ROLE_TAB_PRIVS
                  ALL_TAB_PRIVS_MADE     USER_TAB_PRIVS_MADE
                  ALL_TAB_PRIVS_RECD     USER_TAB_PRIVS_RECD

Command to change database sys user password

We can change oracle database sys user password with below command.

Password file location:

location in linux is $ORACLE_HOME/dbs

location in Windows is $ORACLE_HOME/database

Take backup of the existing file and try below command in  respective platform, 

$ orapwd file=orapw$ORACLE_SID.ora entries=100 ignorecase=y password=manager force=y (in linux)

C:\>orapwd file=PWD$ORACLE_SID.ora entries=100 ignorecase=y password=manager force=y

SES installation

  

 

 To resolve

Click next and go ahead.

Admin password: manager123

Post installation:

Define the Data source for the Oracle Web Services Manager (OWSM) Template

This is a workaround for bug #9268418. After installing/upgrading to the Oracle SES 11.1.2.2 , when you log in to the Web logic Server, the status of the server is shown as Critical instead of OK.
To solve this issue, you need to define the datasource for the OWSM template.

To define the datasource for the OWSM template 

1.      Perform the following steps to create the database user and password for the OWSM connection pool.

1.      Start SQL*Plus and connect as the SYSDBA user.

2.      Run the following SQL statements:

3.  SQL> create user owsmuser identified by welcome1;

4.   

5.  SQL> grant connect, resource to owsmuser;

2.      Log in to the WebLogic admin console (http://host:port/console).

3.      Set the domain instance to lock/edit mode.

4.      In the left navigation menu, select Services > JDBC > Data Sources.

5.      Replace the value of mds-owsm > Connection Pool > URL with that from EnterpriseSearchDS > Connection Pool > URL.

6.      In the Properties section, change the user name to owsmuser and enter its password.

7.      Click Save.

8.      Click Activate Changes.

9.      Restart the middle tier with the following command:

10.$ORACLE_HOME/bin/searchctl restart