Understanding RMAN

Recovery manager (RMAN) is an oracle tool for backing up, restoring and recovering oracle databases. The major task of an oracle dba is to make sure data is available for use anytime and everytime the need arise. Availability and reliability is the key responsibility of a dba.

Connection
Logging into RMAN is very easy. Use this command “RMAN TARGET /” or “rman target my_db”

set ORACLE_SID=orcl
C:\Users\kelebute>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 11:48:33 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1328927802)
RMAN>

or

rman
RMAN> connect target sys/****@orcl

You don't have to specify the "as sysdba" because it is silently implied when you log into RMAN.

To view all the configurations in rman for a particular database, you can use “show all” command.

RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\KELEBUTE\PRODUCT\11.2.0\DBHOME_1\
DATABASE\SNCFORCL.ORA'; # default

checking your configuration

v$rman_configuration

This view provides the summary of RMAN configuration. You could also use show all to retrieve RMAN configuration.

SQL> SELECT * FROM V$RMAN_CONFIGURATION;
CONF# NAME VALUE
---------- ------------------------------ ------------------------
1 RETENTION POLICY TO REDUNDANCY 3
2 BACKUP OPTIMIZATION ON
3 DEFAULT DEVICE TYPE TO Disc
4 CONTROLFILE AUTOBACKUP ON
5 DEVICE TYPE DISK PARALLELISM 1

Backing up a database in noarchivelog mode

rman target /

run {
shutdown immediate;
startup mount;
backup database;
alter database open;
shutdown immediate;
}

Backing up a database in archivelog mode

man target /
RMAN> backup database plus archivelog;


Backup with archivelog delete input

backup database plus archivelog delete input;


Deleting archivelogs
delete expired archivelog all;

or

CROSSCHECK ARCHIVELOG ALL;
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-5';

You can change the date as you want.

incremental backup every night Mon - Sat
This is for updates on only the blocks that changed since last backup.

RMAN> backup incremental level 0 database tag="SUNDAY";
RMAN> backup incremental level 3 database tag="MONDAY";
RMAN> backup incremental level 3 database tag="TUESDAY";
RMAN> backup incremental level 3 database tag="WEDNESDAY";
RMAN> backup incremental level 2 database tag="THURSDAY";
RMAN> backup incremental level 3 database tag="FRIDAY";
RMAN> backup incremental level 3 database tag="SATURDAY";

According to Oracle documentation, RMAN is still the best way to backup database and also restore it. We will talk about restoring and recovery databases next time.

Here is the script I presently use. You can adapt to whatever will suit you. Remember to change the ORACLE_SID and location.

script for rman

set ORACLE_SID=ORCL
export ORACLE_SID
RMAN target / log=/home/oracle/rman.log
run{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4G;
crosscheck backup;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-7';
delete noprompt expired backup;
delete noprompt expired archivelog all;
sql 'alter system switch logfile';
backup database plus archivelog delete all input format '/disk1/backup/full_%U.bkp';
delete noprompt obsolete;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}

Thanks for visiting!

10 easy steps to configure Datagurad on Windows 7

Hey folks! I am not going to bore you with stories on dataguard; it is 90% practical. If you need definitions or explanations of why you need dataguard, please visit this oracle site

Here is the step by step guide on how to configure dataguard on a Windows 7 environment.

Version: Oracle 11.2.0
Hardware: Windows 7

Prerequisite:

a.) Archivelog mode. Here is the step to put your database in archivelog mode

SQL> shutdown immediate
startup mount;
alter database archivelog;
alter database force logging;
alter database open;

b.) Start your listener

c:\> LSNRCTL START

c.) create TNSNAMES.ORA

use netca to create tnsnames for 'orcl1' and 'orclstby'

Please Note:

1. You have to take a backup of your database before you start, otherwise you will get an error

c:\> set ORACLE_SID=orcl1
c:\> rman target /
RMAN> backup database including archivelog;

2. You need to create ORACLE_SID using oradim utility. Please use this command:

create instance
---------------------
C:\app\kelebute\product\11.2.0\dbhome_1\BIN>oradim -NEW -SID orclstby -SYSPWD oracle -pfile initorclstby.ora
Instance created.

3. Use NetMgr to register the databases ('orcl1' and 'orclstby'):

click Start
Click All Programs
Click Oracle Dbhome_1
Click Configuration and Migration tools
Then click "Net Mgr"

NOTE: NetMgr will not accept ORACLE_SID name that is more than 8 characters.

Make sure you verify your TNSNAMES registration in NetMgr.

4. Create a pfile using this command on primary:

SQL> create pfile='c:\app\kelebute\initorclstby.ora' from spfile;

5. create pfile for standby

C:\Windows\system32>sqlplus sys/oracle@psi as sysdba
SQL> create pfile='c:\APP\KELEBUTE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITPSI2.ORA
' from spfile;

6. Add standby redo log files on primary
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\APP\KELEBUTE\ORADATA\PSI\REDO03.LOG
C:\APP\KELEBUTE\ORADATA\PSI\REDO02.LOG
C:\APP\KELEBUTE\ORADATA\PSI\REDO01.LOG

SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO04.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO05.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO06.LOG' size 50m;
Database altered.

7. Set parameters on primary

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(psi,psi2)';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=psi2 async VALID_FOR=(ONLINE_L
OGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=psi2';
SQL> Alter system set standby_file_management=auto;
SQL> Alter system set fal_client =psi;
SQL> Alter system set fal_server=psi2;

8. Take a backup
C:\Windows\system32>rman target psi
RMAN> backup database plus archivelog;

9. Make sure you connect to both SIDs on RMAN
c:\>rman
RMAN> connect target sys/oracle@orcl1
RMAN> connect auxiliary sys/oracle@orclstby
RMAN> @c:\k\dataguard.txt

-------------------
Here is the script
-------------------

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
DB_FILE_NAME_CONVERT=('\ORCL1\','\ORCLSTBY\')
SPFILE
PARAMETER_VALUE_CONVERT '\ORCL1','\ORCLSTBY'
set db_unique_name='orclstby'
SET LOG_FILE_NAME_CONVERT '\ORCL1\','\ORCLSTBY\'
SET SGA_MAX_SIZE '200M'
SET SGA_TARGET '125M'
set control_files='c:\app\kelebute\oradata\orclstby\orclstby01.ctl'
set log_archive_max_processes='5'
set fal_client='orclstby'
set fal_server='orcl1'
set standby_file_management='AUTO'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL1,ORCLSTBY)'
SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL1'
NOFILENAMECHECK;
}
-------------------------------------

10. Put standby in recovery mode

sqlplus sys/oracle@psi2 as sysdba
SQL> Alter database recover managed standby database using current logfile disco
nnect from session;
SQL> Alter database recover managed standby database cancel;

Dataguard is configured!