Wednesday, December 14, 2011

Create a recovery catalog #1

Creating and using a dedicate recovery catalogue has benefits over a standard control file based catalogue.
-A single location for RMAN info
-Recovery views are well documented so can one can write reports
-Makes global scripting easier as you can store scripts for all databases
-Extra options like keep forever or keep records for longer than a year

To create a recovery catalogue we need a new database. 


#Edit the vi tab and add a new database line

vi /etc/oratab
orcl:/u01/app/oracle/product/11.1.0/db_1
rcat:/u01/app/oracle/product/11.1.0/db_1
utf:/u01/app/oracle/product/11.1.0/db_1


. oraenv
rcat


sqlplus /nolog
conn / as sysdba

--CREATE RECOVERY DATABASE
STARTUP NOMOUNT
CREATE DATABASE rcat
   USER SYS IDENTIFIED BY SYS
   USER SYSTEM IDENTIFIED BY SYS
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/rcat/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/rcat/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/rcat/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET WE8ISO8859P15
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/app/oracle/oradata/rcat/system01.dbf' SIZE 325M REUSE EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/app/oracle/oradata/rcat/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users 
      DATAFILE '/u01/app/oracle/oradata/rcat/users01.dbf' SIZE 200M REUSE EXTENT MANAGEMENT LOCAL
   DEFAULT TEMPORARY TABLESPACE temp
      TEMPFILE '/u01/app/oracle/oradata/rcat/temp01.dbf' 
      SIZE 100M REUSE
   UNDO TABLESPACE undots 
      DATAFILE '/u01/app/oracle/oradata/rcat/undots01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE 300M;

CONNECT SYS/password AS SYSDBA
-- create a user tablespace to be assigned as the default tablespace for users
CREATE TABLESPACE users LOGGING 
     DATAFILE '/u01/oracle/oradata/mynewdb/users01.dbf' 
     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;
-- create a tablespace for indexes, separate from user tablespace
CREATE TABLESPACE indx LOGGING 
     DATAFILE '/u01/oracle/oradata/mynewdb/indx01.dbf' 
     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;

--Create catalog, views and procedures
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catalog.sql
@/u01/app/oracle/product/11.1.0/db_1/rdbms/admin/catproc.sql
exit

--Create recovery catalog tablespace
create tablespace rcat_data
datafile '/u01/app/oracle/oradata/rcat/rcat_data.dbf' size 60m autoextend on next 10m maxsize 200m;

--Create recovery catalog user
create user rcat_user identified by rcat_user 
default tablespace rcat_data
quota unlimited on rcat_data;

--Grant privileges
grant recovery_catalog_owner to rcat_user;

--Create catalog
[oracle@vmorcl u01]$ rman catalog=rcat_user/rcat_user

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Dec 13 06:43:06 2011

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

connected to recovery catalog database

RMAN> create catalog;

recovery catalog created

RMAN>

--Now that the catalogue has been created, we can make use of the catalogue views that come with the catalogue

All views start with “RC_”
-RC_DATABASE
-RC_BACKUP_PIECE
-RC_PIECE_DETAIL
-RC_ARCHIVED_LOG
-RC_BACKUP_ACRHIVELOG_SUMMARY
-RC_BACKUP_SET
-RC_BACKUP_SET_DETAILS
-RC_BACKUP_SPFILE

No comments:

Post a Comment