Monday, 11 September 2017

How to configure Oracle RMAN backup in Oracle 12c

RMAN is a oracle utility to backup, restore & recovery of database.

Lets assume the database is in ARCHIVELOG mode.

Refer Oracle Installation document for setting database to ARCHIVELOG mode 

http://dptsource.blogspot.in/2017/09/oracle-database-12c-release-1-1210.html

It is strongly recommended & very good practice to configure RMAN backup with catalog/repository database.


Catalog/repository database: It’s central repository & it requires separate database for backup operation. All registered target databases information stored in catalog database.


Control file: It contains registered target database information at server level itself & RMAN utility directly connects to target database by command “RMAN target /”

Step 1 : Connect to Target database(Target DB: The database on which Backup & Recovery to be performed) as sysdba.

 Ensure the database has been configured with ARCHIVELOG mode or not?


SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE


SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

Step 2 : Ensure ARCHIVELOG destination.

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/product/12.2.0/dbhome_1/dbs/arch
Oldest online log sequence     14
Next log sequence to archive   16
Current log sequence           16

Step 3: Ensure the flash/fast recovery area location

SQL> show parameter db_recovery_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /u01/app/oracle/flash_recovery
                                                 _area
db_recovery_file_dest_size           big integer 5G

Step 4: Connect to RMAN prompt with target database


$ rman target /

Recovery Manager: Release 12.2.0.1.0 - Production on Sun Sep 10 20:36:54 2017

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: PRODSERV (DBID=3089719922)


Step 5: Configure RMAN with controlfile auto-backup feature that will be auto-backup controlfile in case of major changes done in database.


RMAN> configure controlfile autobackup on;

CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


Step 6:  To enable backup optimization run the following command, by default backup optimization has been configured OFF.


RMAN> configure controlfile autobackup on;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Step 7 :  Configure retention policy for backup


RMAN>  CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Step 9 : Connect to the recovery catalog database(RMAN Repository) & Create a tablespace to store RMAN catalog database objects.

SQL> select global_name from global_name;

GLOBAL_NAME
--------------------------------------------------------------------------------
PRODSERV

SQL> select name from v$database;

NAME
---------
PRODSERV

CREATE TABLESPACE CATALOGTBS DATAFILE '/U01/APP/ORACLE/ORADATA/PRODSERVER/CATALOGTBS1.DBF' SIZE 100M AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 10: Create a RMAN user, assign RMAN tablespace to RMAN user as a default & grant recovery catalog owner,connect & resource privileges to RMAN user.


CREATE USER RMAN IDENTIFIED BY rman123 DEFAULT TABLESPACE CATALOGTBS QUOTA UNLIMITED ON CATALOGTBS;

GRANT RECOVERY_CATALOG_OWNER TO RMAN;
GRANT CONNECT, RESOURCE TO RMAN; 
ALTER USER RMAN ACCOUNT UNLOCK;

Step 11: Connect to RMAN on target and recovery catalog database

rman target sys@prodserver catalog RMAN/rman123

Step 12: Create catalog by issuing the following command in RMAN prompt.

RMAN> create catalog;
recovery catalog created

Step 13 : After creating catalog, Ensure RMAN repository tables by logging into repository database as RMAN user.


$ sqlplus "RMAN/rman123@prodserv"

SQL*Plus: Release 12.2.0.1.0 Production on Sun Sep 10 20:57:14 2017

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

Last Successful login time: Sun Sep 10 2017 20:42:06 +05:30

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> show user;
USER is "RMAN"
SQL> select table_name from user_tables;


Step 14 : Register database with recovery catalog on RMAN


rman catalog RMAN/rman123@prodserv


RMAN> register database;

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

Step 15 : Check whether registration was successful.


RMAN> report schema;

Report of database schema for database with db_unique_name PRODSERVER

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    820      SYSTEM               YES     /u01/app/oracle/oradata/prodserver/system01.dbf
3    600      SYSAUX               NO      /u01/app/oracle/oradata/prodserver/sysaux01.dbf
4    65       UNDOTBS1             YES     /u01/app/oracle/oradata/prodserver/undotbs01.dbf
5    100      CATALOGTBS           NO      /u01/app/oracle/oradata/prodserver/catalogtbs1.dbf
7    5        USERS                NO      /u01/app/oracle/oradata/prodserver/users01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    32       TEMP                 32767       /u01/app/oracle/oradata/prodserver/temp01.dbf


RMAN> LIST INCARNATION OF DATABASE;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       17      PRODSERV 3089719922       PARENT  1          26-JAN-17
1       2       PRODSERV 3089719922       CURRENT 1408558    12-AUG-17


Step 16 : Backup 


$ rman catalog RMAN/rman123@prodserv
RMAN> CONNECT TARGET

Full database backup:
RMAN> BACKUP DATABASE;

Archive logs backup:
RMAN> BACKUP ARCHIVELOG ALL;

Database Plus Archivelog backup:
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

A FORMAT can be specified for each subclause in the BACKUP DATABASE PLUS ARCHIVELOG command. For example:


RUN
{
  ALLOCATE CHANNEL ch11 TYPE DISK MAXPIECESIZE 10G;
  BACKUP
  FORMAT '/u03/app/oracle/TEST/%d_D_%T_%u_s%s_p%p'
  DATABASE
  PLUS ARCHIVELOG
  FORMAT '/u03/app/oracle/TEST/%d_A_%T_%u_s%s_p%p';
  RELEASE CHANNEL ch11;
}



Step 17 : To view current RMAN configurations, execute “show all”.


RMAN> SHOW ALL;

RMAN configuration parameters for database with db_unique_name PRODSERVER are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/12.2.0/dbhome_1/dbs/snapcf_prodserver.f'; # default


Step 18 : Change Parameters 

Change the backup location 


RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/rman/full_%u_%s_%p';

To Clear a parameter 


RMAN> CONFIGURE RETENTION POLICY CLEAR;

Take a backup of only a specific table space


RMAN> BACKUP AS BACKUPSET TABLESPACE PRD01;

Backup Summary :


RMAN> LIST BACKUP SUMMARY;


List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
124     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T121947
125     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T164944
126     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T171450
127     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T172452
214     B  A  A DISK        10-SEP-17       1       1       NO         TAG20170910T174136
226     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T174146
309     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T174212
409     B  F  A DISK        10-SEP-17       1       1       NO         TAG20170910T174355

RMAN Status : 

RMAN> SELECT OPERATION, STATUS, MBYTES_PROCESSED, START_TIME, END_TIME from V$RMAN_STATUS;

OPERATION                         STATUS                  MBYTES_PROCESSED
--------------------------------- ----------------------- ----------------
START_TIM END_TIME
--------- ---------
RMAN                              RUNNING                                0
10-SEP-17 10-SEP-17

RMAN                              COMPLETED                              0
10-SEP-17 10-SEP-17

RMAN                              COMPLETED                              0
10-SEP-17 10-SEP-17

RMAN                              COMPLETED WITH ERRORS                  0
10-SEP-17 10-SEP-17

REPORT SCHEMA                     COMPLETED                              0
10-SEP-17 10-SEP-17

RMAN                              COMPLETED                              0
10-SEP-17 10-SEP-17

RMAN                              COMPLETED                              0
10-SEP-17 10-SEP-17

BACKUP                            COMPLETED                             88
10-SEP-17 10-SEP-17

RMAN                              COMPLETED                              0
10-SEP-17 10-SEP-17

BACKUP                            COMPLETED                           1439
10-SEP-17 10-SEP-17

RMAN                              COMPLETED                           1547
10-SEP-17 10-SEP-17

CONTROL FILE AND SPFILE AUTOBACK  COMPLETED                             10
10-SEP-17 10-SEP-17

LIST                              COMPLETED                              0
10-SEP-17 10-SEP-17

LIST                              COMPLETED                              0
10-SEP-17 10-SEP-17
                           



Backup Oracle Database :

We can take a backup using image copy or in backup set. 
It is strongly recommended to use RMAN backup sets to backup the database.

RMAN> BACKUP AS BACKUPSET DATABASE

To take a full backup of the database with the archive logs, do the following:


RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

Assign Backup TAG Name for Quick Identification :


RMAN> BACKUP AS BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;

Change Oracle RMAN Backup File Name Format :


RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   "/backup/rman/full_devdb_bk_%u_%s_%p" MAXPIECESIZE 2048 M;

Compress a RMAN Backup :


RMAN> BACKUP AS COMPRESSED BACKUPSET TAG 'WEEEKLY_PRD01_TBLS_BK_ONLY' TABLESPACE PRD01;

Datafile backups :

RMAN> BACKUP DATAFILE '/u01/app/oradata/TEST/users01.dbf';

Tablespace Backups :


To backup a tablespace use the BACKUP TABLESPACE command. For example:


RMAN> BACKUP TABLESPACE USERS;

Incremental Backups

By default backups are full (level 0). Backups can also be incremental (level 1).

Incremental backups can be:

Differential - includes all changes since the last full or incremental backup
Cumulative - includes all changes since the last full backup
Differential backups require less space. Cumulative backups are faster to restore

Differential backups are the default.


To run a diffential incremental backup use:


RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

To run a cumulative incremental backup use:


RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Note that in order to take a level 1 backup, a level 0 backup must already exist.

A full backup using BACKUP DATABASE is not the same as a level 0 backup. - the LV column of the LIST BACKUP output is NULL after a full backup.


In order to take a level 0 backup use

RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

To create an image copy of an entire database use:


RMAN> BACKUP AS COPY DATABASE;

To create an image copy of a specific datafile use:


RMAN> BACKUP AS COPY DATAFILE <file#>

Example :


RMAN> BACKUP AS COPY DATAFILE 4 FORMAT '/u01/app/oracle/copy/users01.dbf';

Alternatively specify the source file name. For example:


RMAN> BACKUP AS COPY DATAFILE '/u01/app/oradata/TEST/users01.dbf'
FORMAT '/u01/app/oracle/copy/users01.dbf';

0 comments:

Post a Comment