IT Architecture

Contents:- IT Architecture Types

1^st Tier Architecture

In 1st-tier architecture there is only one database server and others are clients.

Business logic and database are into the database server.

All clients are thin client means they have only harware i.e CPU,motherboard,memory,mouse etc.. here to access business logic all clients have to connect to database server.

Problem:- whenever client encreases then we have to increase server also because of work-load.

2nd Tier Architecture

In 2-tier architecture client is not thin client. here business logic(software) is located in client computer and database is located into the database server.

Problem:- change-management-system not supported in 2-tier architecture. means business logic(software) needs to update oftenly so whenever we make change into business logic(software) then we have to install or update business logic(software) into all client computer. so here this is the problem.

3^rd Tier Architecture  

In this

Client computer             -        Contain only browser

Application server         -        Contain business logic

Database server             -        Contain Database

Change-management-system supported in 3-tier architecture because for making change we have to change only in application server..

Note:- Oracle's first application server is 9

Nth Tier Architecture

In this architecture there are multiple application server and database server for load-balancing.

RMAN Sample Backup Script

Contents:-  Backup Script

Create Global Script backup_script

{

allocate channel c1 type disk;

backup database format '/u01/app/oracle/backup/db_%u_%d_%s';

sql 'alter system archive log current';

backup format '/u01/app/oracle/backup/archivelog/log_t%t_s%s_p%p'

(archivelog all delete all input);

};

*** BACKUP.BAT file

         

          rman target / catalog rman_user/rman_user@rdb

          cmdfile=f:\script\backup.rcv

          log=f:\script\rman.log

RMAN Backup Setting

Contents:- Oracle Backup Retention Concept,  Configure Backup Setting, Show Existing Company Backup Policy

Retention Policy

A retention policy specifies which backup will be kept and for how long.

There are two types of retention policy

Recovery Window

Establishes a period of time within which point-in-time recovery is possible.

Rman> Configure retention policy to recovery window of <days> days;

If you are not using  a recovery catalog, then you should keep the recovery window time period <= the value of the control file parameter CONTROL_FILE_RECORD_KEEP_TIME to prevent the record of older backups from being overwritten in the control file. And if you are using a recovery catalog, then make sure that CONTROL_FILE_RECORD_KEEP_TIME is greater than the time period between catalog resynchronization.

Catalog Resynchronization happens when you

          Create a backup. In this case synchronization is done implicitly.

          Execute the resync catalog command.

Redundancy

Establishes a fixed number of backup that must be kept.

Rman> Configure retention policy to redundancy <copies>;

Disable Retention Policy

Rman >Configure retention policy to none;

Backup Setting

Show all Existing Backup Setting.

$> rman target /

Rman> show all;

Rman> show exclude;

Rman> show controlfile autobackup format;

Or

$> sqlplus sys as sysdba

Sql> select * from v$rman_configuration;

Configure New Backup Setting

$> rman target /

Controlfile Autobackup Setting

Rman> configure controlfile autobackup format for device type disk to ‘/u01/app/oracle/backup/controlfile/%F’;

With a control file autobackup, RMAN can recover the database even if the current control file, recovery catalog, and server parameter file are inaccessible. RMAN Can search for and restore the server parameter file or control file from that autobackup.

Use multiple Streams of data

Rman> configure device type sbt parallelism 3;

Parallelism is the number of streams of data that can be used to read from and write to the device. For example:- if the media manager has two tape drives available, then parallelism 2 would allow both tape drives to be used simultaneously for backup command using that media manager.

Use Clear option to reset to default setting.

Rman> configure retention policy to clear;

configure and allocating channels.

Rman> configure default device type to disk;

Rman> configure default device type to tape;

Configure backup setting for multiple copies of backup set

Rman> configure archivelog backup copies for device type disk to 2;

Rman> configure Datafile backup copies for device type disk to 2;

Rman> backup database plus archivelog ;    -- two copies of backup are made to two different disks.

Note:- This setting only applies to all backups except control file autobackups because the autobackup of a control file always produces one copy.

Note:- rman can make up to 4 backup copies of backup set simultaneously, each an exact duplicate of others. duplex backup set can only apply to backup set and can not apply to image copies.

Configure Backup Optimization

When enable backup optimization , the backup command skips files when identical files have already been backed up.

Rman> configure backup optimization on;

To Override backup optimization

Rman> backup device type disk backupset all force;

Configure Compression Backup

Backup compression can be used skip the backup of unallocated blocks as well as unused blocks.

Available compression algorithm are HIGH, MEDIUM,BASIC and LOW.

Rman> configure compression algorithm ‘HIGH/ MEDIUM/LOW/BASIC’;

Encryption Backup

Transparent Encryption:           With a Wallet (default)

Password Encryption:               With a password. ( no wallet)

Dual Mode Encryption              has both transparent and password encryption modes.

Spool backup

Rman> spool log to ‘/u01/app/oracle/backup/backup_log.txt’;

Recovery Catalog Database Concept and Real - Life Activities with Recovery Catalog Database

Contents:-Register database in to Recovery Catalog Database, Virtual Private Catalog Database, Resynchronization Recovery Catalog, Backup Recovery Catalog, Export and Import Recovery Catalog Database.

Recovery Catalog Database Concept/ Features

A Recovery catalog database preserves the backup information in a separate database which is useful in the event of loss of control file.

A single recovery catalog database is used to store backup information (rman repository information) for multiple target database.

A Recovery catalog can also store rman scripts.

Enables you to create customized reports for all registered target database.

Allows you to list the data files and tablespaces that are or were in the target database at a given time.

Enables you to use KEEP FOREVER Clause of the backup command (means backup will never expires)

Commands and Practicals

Rman> Report Schema   -- is used to lists the tablespace and datafiles in the target database.

Rman > Report Schema at <time/scn/log sequence>   (Can only used at clause in recovery catalog database) . lists the tablespace and datafiles of a particular time.

Creating Recovery Catalog

Steps

Allocate Space for Recovery Catalog  ( Consider number of database supported by recovery catalog, rman script stored, number of archive log file and backups recorded)

Create a Tablespace for Recovery Catalog

Sql> conn sys as sysdba

SQL>create tablespace rec_tablespace Datafile ‘+data(Datafile)’ size 10m autoextend on maxsize unlimited extent management local segment space management auto;

Create the Recovery Catalog Owner

SQL> Create user rcowner identified by rcowner temporary tablespace temp default tablespace rec_tabspace quota unlimited on rec_tabspace;

SQL> Grant recovery_catalog_owner to rcowner;

Create the Recovery Catalog.

$> rman catalog rcowner/welcome@demo

Rman> Create catalog ( it will create the catalog tables in the tablespace of the catalog owner.)

Managing Target Database Records in the Recovery Catalog

Registering a Target Database into Recovery Catalog ( if you use OEM then you have to additionally create catalog and register or unregister database by OEM also even if you registered or unregistered it manually)

It will create a row in the recovery catalog table for the target database.

Synchronize the recovery catalog with the control files.

$ rman target / catalog rcowner/welcome@demo

Rman> register database

Now check files regarding target database orcl into recovery catalog database table.

$ sqlplus rcowner/welcome@demo

Sql> select * from rc_database; 

Un-Registering a Target Database into Recovery Catalog

It will removes the information about target database from recovery catalog.

When you unregister the database, all rman repository information from recovery catalog will lost.

$ rman target / catalog username/pwd@servicename

Rman> unregister Database;

or

$sqlplus rcowner/welcome@demo

Sql> EXECUTE dbms_rcvcat.unregisterdatabase(1,777592246);

Cataloging Additional Backup Files.

Catalog command is used to catalog existing metadata backup files information that are no longer listed in the control file.Catalog command can be use to add the following types of backup files to the recovery catalog.

Control files

Rman> catalog controlfilecopy ‘/u01/app/oracle/control01.ctl’;

Data files

RMAN > catalog datafilecopy ‘/u01/app/oracle/user01.dbf’;

Backup Pieces

RMAN > catalog backuppiece ‘/u01/app/oracle/backup/backup_820.bkp’;

Archived Redo Log Files.

RMAN> catalog archivelog ‘/u01/app/oracle/archivelog/archive_031.log’;

Catalog all Files in the Currently enabled Fast Recovery Area as follows

RMAN> catalog recovery area noprompt;

Catalog with START WITH Option / Recatalogs any available backups

RMAN > catalog start with ‘/tmp/arch_logs/’   ( All types of backup files that 

are found in the specified directory and subdirectories are cataloged)

RMAN > catalog start with ‘/tmp/bset’ ( Catalog only those files in the /tmp directory whose files names start with the string bset)

Notes:- Catalog Command can be used without being connected to recovery catalog.

Recovery Catalog Resynchronization / Re-creating an unrecoverable Recovery Catalog

When RMAN performs resynchronization, it compares the recovery catalog to either the current control file of the target database or a backup/standby control file and updates the recovery catalog with information that is missing or changed.

There are two types of Resynchronization

Partial

For Partial Resynchronization, RMAN Compares the control file to the recovery catalog and updates the recovery catalog with any metadata concerning backups, archived redo logs, data file copies and so on.

Full

For a Full Resynchronization, RMAN first creates the control file snapshot, which is a temporary copy of the control file. It uses the snapshot to make the comparison to the recovery catalog.

It compares and updates all the data that a partial resynchronization does, but it also includes the any database structure changes. For example, Database Schema changes or new tablespaces are included in a full Resynchronization

Note:- IF the only changes to the control file are those records are governed by CONTROL_FILE_RECORD_KEEP_TIME, then a partial resynchronization  is done. Otherwise, a full resynchronization is done. A Full Resynchronization is also done when you issue the RESYNC CATALOG Command.

Manually Resynchronizing the Recovery Catalog

RMAN > resync catalog;

Requirements

If the recovery catalog  was unavailable when you issued RMAN commands that cause a partial resynchronization.

If you perform infrequent backups of your target database because the recovery catalog is not updated automatically when a redo log switch occurs or when a redo log is archived.

After making any changes to the physical structure of the target database.

RMAN Stored Scripts

Local:- Associated with the target database to which RMAN is connected when the script is created.

RMAN > Create script script_name  { <RMAN Command> }

Global :- Can be executed against any registered database in the recovery catalog.

RMAN > Create Global script script_name  { <RMAN Command> }

Creating from a Text File.

RMAN > Create Global script script_name  FROM  file ‘file_name’;

Executing Script / Maintaining RMAN Stored Scripts

Execute Script

RMAN> RUN { Execute Script script_name ;}

RMAN> RUN { Execute Global Script script_name ;}

Displaying a script

RMAN >PRINT [GLOBAL] Script script_name

Sending the Contents of a script to a file.

RMAN >PRINT [GLOBAL] Script script_name to file ‘file_name’;

Displaying names of a defined script

RMAN >LIST [GLOBAL] SCRIPT NAMES;

Updating Script

RMAN > REPLACE [GLOBAL] SCRIPT script_name { <RMAN Command> ;}

RMAN > REPLACE [GLOBAL] Script script_name from FILE ‘file_name’;

Deleting a Script

RMAN> Delete script script_name;

View Detail Script

Sql> select * from rs_stored_script;

Sql> select * from rs_stored_script_line;

Backup the recovery Catalog

Recovery Catalog is the database so take backup of the recovery catalog as the same as normal database. But take backup of rman repository in control file instead of recovery catalog.

Configure control file autobackup so that the control file is backed up every time a backup is made of the recovery catalog.

Steps

Run the recovery catalog in archivelog mode.

Set the retention policy to redundancy value greater than one

Backup the recovery catalog to disk and tape.

To make the backups, use the BACKUP DATABASE PLUS ARCHIVELOG command.

Use the control file (NOCATALOG) , not another recovery catalog, as the RMAN Repository.

Configure Controlfile autobackup to be on

Exporting and Importing the Recovery Catalog

Export/import utilities is used to export and import the recovery catalog database.

Export Recovery Catalog Database Schema and Import it to Another Recovery Catalog Schema into Same Database.

First Create another Recovery Catalog Schema and Grant Privileges.

Sql> create user rcowner identified by welcome temporary tablespace temp default tablespace rec_tablespace1 quota unlimited on rec_tablespace;

Sql> grant recovery_catalog_owner to rcowner;

1^st Way

Export Recovery Catalog Source Schema and import the source recovery catalog schema into another recovery catalog schema.

$> expdp rconwer1/welcome directory=expdir dumpfile=full_catalog.dmp

$> impdp rcowner_dup/welcome directory=expdir dumpfile=full_catalog.dmp  remap_schema=rcowner1:rcowner_dup

2^nd Way

Connect to Target Recovery Catalog Schema and Create Catalog.

$> rman catalog rcowner_dup/welcome@demo

Rman > Create Catalog;

Import from source recovery catalog schema using various option.

$>import catalog rcowner1/welcome@demo db_id=<db_id>;

OR

$>import catalog rcowner1/welcome@demo db_name=orcl;

OR

$>import catalog rcowner1/welcome@demo db_name=orcl unregister – registered database will not unregister from the source recovery catalog schema.

Export Recovery Catalog Database Schema and Import it to Another Recovery Catalog Schema into another database.

First Create Recovery Catalog Schema into target database and Grant Privileges.

Sql> create user rcowner identified by welcome temporary tablespace temp default tablespace rec_tablespace1 quota unlimited on rec_tablespace;

Sql> grant recovery_catalog_owner to rcowner;

Connect target database recovery catalog schema and import Source database recovery catalog.

$> rman catalog rcowner/welcome@rdb

Rman> create catalog;

Rman > import catalog rcowner_dup/welcome@demo db_name=orcl no unregister;

Note:- You should not execute the CREATE CATALOG Command before or after importing the catalog into database. The import operation creates the catalog in the second database.

With import catalog command , you can import the metadata from one recovery catalog schema into a different recovery catalog schema.

DBID/DB_NAME:- you can specify the list of Database IDs or Database Names whose metadata should be imported from the source catalog schema.  When not specified, RMAN merges metadata for all database IDs from the source catalog schema into the destination catalog schema.

NO UNREGISTER :  By default, the imported database IDs are unregistered from the source recovery catalog schema after a successful import. By using NO UNREGISTER option, you can force RMAN to keep the imported database IDs in the source catalog schema.

Upgrading the Recovery Catalog

Requirements:- if you use a version of the recovery catalog that is older than the required  by the RMAN Client, then you must upgrade it by following command.

RMAN> upgrade catalog;

Drop the Recovery Catalog Schema

RMAN > drop catalog;

Dropping the catalog deletes the recovery catalog record of backup for all target database registered in the catalog.

Debug Connection to Catalog Database

$> rman target / catalog rcowner/welcome@rdb debug trace trace.log

Virtual Private Catalog Concept

RMAN Catalog has been enhanced to create virtual private RMAN Catalogs for groups of database and users.

The Catalog owner creates the base catalog and grants the Recovery_catalog_owner privilege to the owner of the virtual catalog.

The catalog owner can either grant access to a registered database or grant the REGISTER privilege to the virtual catalog owner.

The virtual catalog owner can then connect to the catalog for a particular target or register a target database.

After this configuration, the VPC owner uses the virtual private catalog just like a standard base catalog.

As a virtual catalog owner, you can see only the database to which you have been granted access. Note” if a catalog owner has not been granted sysdba or sysoper on the target database, most RMAN operations cannot be performed.

Query to show how many database Registered in Recovery Catalog

SQL> select distinct db_name from DBINC;

Configure Separate Virtual Private Catalog Database

Target Database:- ORCL   Catalog Database:- rdb

   

Target Database:-   demo (we will create demo database as a virtual private catalog database)

Steps

Create tablespace for recovery_catalog information and create new recovery catalog schema and grant privilege  in target database to which we want to make virtual private catalog database.

$> . oraenv

Demo

$> sqlplus sys as sysdba

Sql> create tablespace rec_tablespace Datafile ‘+data(Datafile)’ size 10m autoextend on maxsize unlimited extent management local segment space management auto;

Sql> create user rcowner_dup identified by welcome temporary tablespace temp default tablespace rec_tablespace quota unlimited on rec_tablespace;

Sql> grant recovery_catalog_owner to rcowner_dup;

Now connect to target database, base recovery catalog database and connect to target database to which we want to make virtual private catalog database and create it to virtual private catalog database.

$> . oraenv

Orcl

$> rman target /

Rman> connect catalog rcowner/welcome@rdb

Rman> connect catalog rcowner_dup/welcome@demo

Rman> create virtual catalog;

Configure Virtual Private Catalog Schema on the same Base Catalog Database .

Catalog Database:- rdb   

Steps

Create new virtual private catalog schema on the same base catalog database and grant privileges.

$> sqlplus sys/rdb@rdb as sysdba

Sql> create user rcowner_vpc identified by welcome temporary tablespace temp default tablespace rc_tablespace quota unlimited on rc_tablespace;

Sql> grant recovery_catalog_owner to rcowner_vpc;

Connect to the Base recovery catalog schema and grant either access to registered database or grant register privilege to register a target database to Virtual Private Catalog Schema.

$> rman catalog rcowner/welcome@rdb

Rman > grant catalog for database orcl to rcowner_vpc; -- grant access to registered database.

Or

Rman > grant register database to rcowner_vpc; -- grant Register privilege to register a target database.

Connect to virtual private catalog schema and create the virtual private catalog

$> rman catalog rcowner_vpc/welcome@rdb

Rman> create virtual catalog;

Or if earlier version than 11g the create virtual catalog by following command

Connect to virtual private catalog schema

$>sqlplus rcowner_vpc/welcome@rdb

Sql> EXEC rcowner.DBMS_RCVCAT.CREATE_VIRTUAL_CATALOG;        -- here rcowner is the base catalog owner.

Revoke catalog privilege from Virtual Private Recovery Catalog schema within same catalog database.

Connect to base catalog schema  and revoke privilege from virtual private catalog schema

$>rman catalog rcowner/welcome@rdb

Rman> revoke catalog for database orcl from rcowner_vpc;

Drop virtual private catalog schema owner.

Connect to virtual private catalog schema  and drop virtual private catalog;

$> rman catalog rcowner_vpc/welcome@rdb

Rman> drop catalog;