RMAN Catalog Registration Configuration

RMAN Catalog Registration Configuration

STEPS
-----------------
1) SET CATALOG for RMAN user

create user rman identified by rman;

grant recovery_catalog_owner to rman;

2) CREATE CATALOG and REGISTER DATABASE

set oracle_sid=sns5
rman catalog rman/rman@sns0910srv

RMAN>create catalog;
RMAN>register database;

3) VERIFY REGISTRATION

RMAN>report schema;
exit

4) MANUAL BACKUP REGISTRATION

rman target rman/rman@sns0910srv nocatalog;

RMAN>backup current controlfile;


rman target rman/rman@sns0910srv catalog rman/rman@sns0910srv;

RMAN>catalog backuppiece 'c:\backup\rman\sns0910\C-...';


------------
ERRORS IN ABOVE PROCESS


ORACLE error from recovery catalog database: ORA-01950: no privileges o
ace 'SYSTEM'

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06433: error installing recovery catalog

---------
Solution

tablespace ---USR
Quota on USR unlimited

-----------

RMAN> create catalog;

recovery catalog created

-----------
Error

RMAN> register database;

RMAN-00571: ==========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: ==========================================================
RMAN-03002: failure of register command at 06/23/2009 15:50:47
RMAN-06171: not connected to target database


---------------
Solution

c:/> rman target sys/linux@sns0910srv catalog rman/rman@sns0910srv

==================

FINAL STEPS
-------

1) SET CATALOG for RMAN user

create user rman identified by rman
Temporary tablespace temp
Default tablespace usr
Quota unlimited on usr;


grant recovery_catalog_owner to rman;

2) CREATE CATALOG and REGISTER DATABASE

set oracle_sid=sns5
c:/> rman target sys/linux@sns0910srv catalog rman/rman@sns0910srv
RMAN>create catalog;
RMAN>register database;
RMAN>exit
3) VERIFY REGISTRATION
RMAN>report schema;
exit

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


Configure RMAN

C:\>SET ORACLE_SID=SNS5

C:\>RMAN TARGET SYS@SNS0910SRV

target database Password: oracle

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 1;

RMAN> CONFIGURE BACKUP OPTIMIZATION ON;

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN>CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'E:\backup\rman\sns0809\%F';

RMAN>SHOW ALL;

using target database control file instead of recovery catalog

RMAN configuration parameters are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

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 'e:\backup\rman\sns0809\%F';

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 ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFSNS5.ORA'; # default

RMAN BACKUP

C:\>SET ORACLE_SID=SNS5

C:\>RMAN TARGET SYS@SNS0910SRV

target database Password: oracle

RMAN> BACKUP DATABASE FORMAT 'E:\backup\rman\sns0809\%U';

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\backup\rman\sns0809\%U';

RMAN RECOVERY

SQL> SHUTDOWN IMMEDIATE;

SQL> STARTUP NOMOUNT;

C:\>SET ORACLE_SID=SNS5

C:\>RMAN TARGET SYS@SNS0910SRV

RMAN> RESTORE CONTROLFILE FROM 'E:\backup\rman\sns0809\C-1560435174-20081003-00'

SQL> STARTUP MOUNT;

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS

Restoring Default RMAN Configuration Settings:

CONFIGURE BACKUP OPTIMIZATION CLEAR;

ONFIGURE RETENTION POLICY CLEAR;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;

Configuring the Default Device Type for Backups

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

Configuring the Default Backup Type for Disk Backups

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # image copies

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # uncompressed

Configuring Compressed Backupsets as Default for Tape or Disk

RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;

RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;

Configuring Disk Devices and Channels

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ora_df%t_s%s_s%p';

Configuring Tape Devices and Channels

CONFIGURE CHANNEL DEVICE TYPE sbt PARMS='ENV=mml_env_settings';

CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

Configuring Control File and Server Parameter File Autobackup

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

Configuring the Control File Autobackup Format

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE deviceSpecifier TO 'string';

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT

FOR DEVICE TYPE DISK TO 'ora_home/oradata/cf_%F';

CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE DISK CLEAR;

CONFIGURE CONTROLFILE AUTOBACKUP FOR DEVICE TYPE sbt CLEAR;

Overriding the Configured Control File Autobackup Format

RMAN> SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE sbt TO 'controlfile_%F';

RMAN> BACKUP AS COPY DATABASE;

RMAN> RUN {

SET CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/tmp/%F.bck';

BACKUP AS BACKUPSET DEVICE TYPE DISK DATABASE;

}

Configuring a Recovery Window-Based Retention Policy

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

Configuring a Redundancy-Based Retention Policy

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Showing the Current Retention Policy

RMAN> SHOW RETENTION POLICY;

CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Disabling the Retention Policy

CONFIGURE RETENTION POLICY TO NONE;

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

Important Points

RMAN> BACKUP AS COMPRESSED BACKUPSET DATABASE FORMAT 'E:\backup\rman\sns0809\%U';

%F expands to “C-XXXXXXXXX-YYYYMMDD-NN”, where:

XXXXXXXXX – database id

YYYYMMDD – day, when backed up

NN – change number during day, starts with 00, and represented in hexadecimal

RMAN increments "1" to the last number string (sequence) for each autobackup that is performed in a day.

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

Run bplist as the root user on the client host.

# bplist -t 4 -l -R / > bp.list.out

# cat bp.list.out

----------

RMAN Reports

# Show all backup details

list backup;

# Show items that beed 7 days worth of archivelogs to recover completely

report need backup days = 7 database;

# Show/Delete items not needed for recovery

report obsolete;

delete obsolete;

# Show/Delete items not needed for point-in-time

# recovery within the last week

report obsolete recovery window of 7 days;

delete obsolete recovery window of 7 days;

# Show/Delete items with more than 2 newer copies available

report obsolete redundancy = 2 device type disk;

delete obsolete redundancy = 2 device type disk;

# Show datafiles that connot currently be recovered

report unrecoverable database;

report unrecoverable tablespace 'USERS';

Specifying the Location of RMAN Output

By default, RMAN writes command output to standard output. To redirect output to a log file, enter the LOG parameter on the command line when starting RMAN, as in the following example:

% rman LOG /tmp/rman.log

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

Different RMAN Backup

Back up the database, and then the control file:

(which contains a record of the backup)

RMAN> BACKUP DATABASE;

RMAN> BACKUP CURRENT CONTROLFILE;

Backup datafiles:

RMAN> BACKUP AS BACKUPSET DATAFILE

'ORACLE_HOME/oradata/trgt/users01.dbf',

'ORACLE_HOME/oradata/trgt/tools01.dbf';

Backup all datafiles in the database:

RMAN> BACKUP AS COPY DATABASE;

Backup archive logs:

RMAN> BACKUP ARCHIVELOG COMPLETION TIME BETWEEN 'SYSDATE-28' AND 'SYSDATE-7';

Backup tablespace:

RMAN> BACKUP TABLESPACE system, users, tools;

Backup controlfile:

RMAN> BACKUP CURRENT CONTROLFILE TO '/backup/cntrlfile.copy';

Backup parameter file:

RMAN> BACKUP SPFILE;

Backup everything:

RMAN> BACKUP BACKUPSET ALL;

Create a consistent backup and keep the backup for 1 year:

(exempt from the retention policy)

RMAN> SHUTDOWN;

RMAN> STARTUP MOUNT;

RMAN> BACKUP DATABASE UNTIL 'SYSDATE+365' NOLOGS;

Backup Validation confirms that a backup could be run, by confirming that all database files exist and are free of physical and logical corruption, this does not generate any output.

Example:

RMAN> BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

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

Restoring the Oracle SPFILE

To restore the spfile, you still need to start the database, but you can do so with a minimal database parameter file.

Startup nomount pfile=c:\oracle\product\10.1.0.3\database\initsns5.ora

Once the instance is started, you can start RMAN and restore the SPFILE as seen in this example:

RMAN>restore spfile from autobackup;

RMAN INCOMPLETE RECOVERY

Performing Incomplete Recovery

RMAN allows you to perform recovery of the whole database to a specified non-current time, SCN, or log sequence number. This

type of recovery is called incomplete recovery. Because you must open RESETLOGS after performing incomplete recovery, you

have to recover all datafiles. You cannot recover some datafiles before the RESETLOGS and others after the RESETLOGS. The

database must be closed to perform database point-in-time recovery.

run {

shutdown immediate;

startup mount;

set until time 'May 15 2009 09:00:00';

# set until scn 1000; # alternatively, you can specify SCN

# set until sequence 9923; # alternatively, you can specify log sequence number

restore database;

recover database;

alter database open resetlogs;

}

RMAN Disaster Recovery

C:>rman catalog=rman/rman@sns5 target=sys/password@sns5live

startup nomount;

restore controlfile;

alter database mount;

--------

SQL> SELECT archivelog_change#-1 FROM v$database;

run {

set until scn 1048438;

restore database;

recover database;

alter database open resetlogs;

}

------

sql "ALTER TABLESPACE temp ADD

TEMPFILE ''C:\Oracle\oradata\sns5live\temp01.dbf''

SIZE 100M

AUTOEXTEND ON NEXT 64K";

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

list incarnation;

reset database to incarnation x;

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

# Show all backup details

list backup;

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

RMAN point in time recovery (PITR)

Recovery Manager (RMAN) automatic tablespace point-in-time recovery (commonly abbreviated TSPITR) enables you to quickly

recover one or more tablespaces in an Oracle database to an earlier time, without affecting the state of the rest of the

tablespaces and other objects in the database.

run{

set until time "to_date('05/28/09 15:00:00' , 'mm/dd/yy hh24:mi:ss')";

restore database;

recover database;

alter database open resetlogs;}

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

Unregister database from recovery catalog

1)nregistering a Database That is Unique in Catalog

RMAN> CONNECT TARGET /
RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> UNREGISTER DATABASE NOPROMPT;
RMAN> LIST DB_UNIQUE_NAME ALL;


2) Unregistering a Database That is Not Unique in Catalog


RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> SET DBID 28014364;
RMAN> UNREGISTER DATABASE;

3)Unregistering a Standby Database

RMAN> CONNECT CATALOG rman@sns0910srv
RMAN> SET DBID 1627367554;
RMAN> UNREGISTER DB_UNIQUE_NAME sns0901test;