Skip to content
Advertisements

Archive for

RMAN password encryption video

I just added RMAN password configuration, How to configure and Its working.

Advertisements

Crash Recovery Overview

An instance(crash) failure occurs when your database isn’t able to shutdown normally.When this happens, your datafiles could be in an inconsistent state meaning they may not contain all committed changes and may contain uncommitted changes. Instance failures occur when the isntance terminates abnormally. A sudden power failure or a shutdown abort are two common causes of isntance failure.

Oracle uses crash recovery to return the database to a consistent committed state after an instance failure. Crash recovery guarntees that when your database is opened, it will contain only transactions that were committted before the instance failure occured. Oracle system monitor will automatically detect whether crash recovery is required.
Crash recovery has two pahses : rollforward and rollback

The system monitor will first roll forward and apply to the datafiles any transactions in the online redo files that occured after the most recent checkpoint. Crash Recovery uses redo information found in the online redo log files onlu . After rolling forward, Oracle will rollback any of those transactions that were never committed. Oracle uses information stored in the undo segments to rollback (undo) any uncommitted transactions.

When you start your database, Oracle uses the SCN information in the control files and datafiles headers to determin which one of the following will occur.

Starting up normally.

Performing crash Recovery

Determining that media reocvery is required.

On start up , Oracle check the instance thread status to determine whether crash recover is required . When the database is open for normal operations ,the thread status is OPEN. When Oracle us shut down normally , a checkpoint takes place and the instance thread status is set to CLOSED.

when your instance abnormally terminates the thread status remains OPEN because Oracle didn’t get a chance to upodate teh status to CLOSED.

On startup , When Oracle detects that an instance thread was abnormally left open, the system monitor process will automatically perform crash recovery.

The below query , it will usefull to find out whether crash recovery is required.

select a.thread#,b.open_mode,a.status,
CASE
WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery req.’
WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recovery Req.’
WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance already open’
ELSE ‘huh?’
END STATUS
FROM v$thread a,
v$database b,
v$instance c
where a.thread#=c.thread#;

Just a Example :

1.

SQL> startup
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             306184656 bytes
Database Buffers          222298112 bytes
Redo Buffers                5804032 bytes
Database mounted.
Database opened.

SQL> ED
Wrote file afiedt.buf

  1  select
  2  a.thread#,b.open_mode,a.status,
  3  CASE
  4  WHEN ((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery r
eq.’
  5  WHEN ((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recov
ery Req.’
  6  WHEN ((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance alre
ady open’
  7  ELSE ‘huh?’
  8  END STATUS
  9  FROM v$thread a,
 10  v$database b,
 11  v$instance c
 12* where a.thread#=c.thread#
SQL> /

   THREAD# OPEN_MODE            STATUS STATUS
———- ——————– —— ———————-
         1 READ WRITE           OPEN   Instance already open

2.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             306184656 bytes
Database Buffers          222298112 bytes
Redo Buffers                5804032 bytes
Database mounted.

SQL> select a.thread#,b.open_mode,a.status,
  2  CASE
  3  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery re
q.’
  4  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recove
ry Req.’
  5  WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance alrea
dy open’
  6  ELSE ‘huh?’
  7  END STATUS
  8  FROM v$thread a,
  9  v$database b,
 10  v$instance c
 11  where a.thread#=c.thread#;

   THREAD# OPEN_MODE            STATUS STATUS
———- ——————– —— ———————-
         1 MOUNTED              CLOSED No Crash Recovery Req.

SQL>

3.

SQL> SHUTDOWN ABORT
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  535662592 bytes
Fixed Size                  1375792 bytes
Variable Size             306184656 bytes
Database Buffers          222298112 bytes
Redo Buffers                5804032 bytes
Database mounted.

SQL> ed
Wrote file afiedt.buf

  1  select a.thread#,b.open_mode,a.status,
  2  CASE
  3  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’OPEN’)) THEN ‘Crash Recovery re
q.’
  4  WHEN((b.open_mode=’MOUNTED’) AND (a.status=’CLOSED’)) THEN ‘No Crash Recove
ry Req.’
  5  WHEN((b.open_mode=’READ WRITE’) AND (a.status=’OPEN’)) THEN ‘Instance alrea
dy open’
  6  ELSE ‘huh?’
  7  END STATUS
  8  FROM v$thread a,
  9  v$database b,
 10  v$instance c
 11* where a.thread#=c.thread#
SQL> /

   THREAD# OPEN_MODE            STATUS STATUS
———- ——————– —— ———————-
         1 MOUNTED              OPEN   Crash Recovery req.

RMAN-06004: ORACLE error from recovery catalog database

Using a recovery catalog makes it startightforward to restore the controlfile.When you issue the restore controlfile command, RMAN will retieve from teh recovery catalog the location and name of the file that contains the control file backup and restores the control file approproately.

Because the recovery catalog knows the location of the RMAN backups piece, It doesn’t matter whether the backup piece is in a flash recovery area or in a configured channel location.

When you’re connected to the recovery catalog, you can view backup information about your control files even while your target database is in nomount mode. To list backups of your controlfiles ,use the lsit command as shown here.

RMAN>connect target /
RMAN>connect catalog backup/backup@recover
RMAN>startup nomount
RMAN>list backup of ocntrolfile;

If you have registered two database in the recovery datalog with the same name, then you might receive an error such as this when you attempt to list backups or restore the control file

RMAN-06004: ORACLE error from recovery catalog database
RMAN-20005: target database name is ambiguous

In this situation, you will need to first set your database identifier(dbid) before you can restore your control file.

How to determine your DBID

You can verify that you have multiple databases with the same name in your recovery catalog by querying the recovery catalog RC_DATABASE view

sql>connect backup/backup@recover;
sql> select db_key,dbid,name from rc_database;
DB_KEY       DBID            NAME
——-           —–                 ——-
1                1124743449      TEST
4241        1140772490      TEST

Active/passive vs active /active Cluster

Active/Passive

An active/passive cluster generally contain two identical nodes.At any time, one of the nodes is active and the other is passive.Oracle singel instance database software is installed on both nodes, but the database is located on shared storage.During normal operation, the Oracle database instance runs only on the active node.In the event of a failure of the currently active primary system,clustering software will transfer control of the disk subsystem to the secondary system, a process known as failover.As part of the failover process, the Oracle instance on the secondary nose is started, thereby recovering and resuming the service of the Oracle database. All connections to the instance on the primary system at the time of failure are terminated and must be reestablished withe the new instance on the secondary system.On resolution of the issue, the Oracle instance is cleanly shut down on the secondary system , control of the disk subsystem is transfered back to the primary system and oracle instance is started to resume normal operations.

a number of third party products are avilable to implement active /passive clustering on linux.

Active/Active

An active /active cluster has an identical hardware infrastruture to an active /passive one. However, Oracle instances run concurrenlty on both servers and access the same database. which is located on shared storage. The instances must communicated with each other to negotiate access to shared data in the database. In the event of a server failure, the remaining server can continue processing the workload and optionally failed sessions can be reconnected . the benefit of an active / active cluster over an active/passive cluster is that,during normal processing, the workload can be shared across both servers in the cluster

Oracle RAC is desighned to run in an active / active enviornment.

RAC first day class

Today I went to attend the my first day class about Oracle Real Application Cluster. The class taken by Mr.Syed Jaffar Hussian.I thought Why i want to explain about himself.Because almost all of them(oracle Community) know about him,He is one of the famous person and outstanding DBA in this world.However I briefly explain about my trainer.He is an Oracle Certified Master (First person in Saudi Arabia) and He also Oracle ACE member and he is one of the author of upcoming book of Oracle R1/R2 RAC handbook and He is reviewer of Oracle books and he given seminar for some Organization about RMAN and RAC .I hope I will become a RAC expert after finish this course.

Today He given overviwed about Course Agenda what he will take class in future..

Cluster Introduction with its advantages.

What is Oracle RAC?

Oracle RAC architecture

Advantage of Oracle RAC

Single instance and RAC

Installation Prequistes like Hardware, Network , Storage, OS groups and users and SSH/RCP preconfiguration

Cluster utility (cluvfy) overview and usage

RAC Installation – Clustware ,ASM and RDBMS

RAC Database Creation

Common Initilization Parameters

OCR (oracle cluster registry) & Voting Disk

Enabling and Disabling automatic cluster stack start and stop

Clustware administration

RAC workload management and services -Hig avilbilty, load balance

RAC internals

Troublshooting – Node eviction Issues

Performance Tuning

Node addition and Deletion

Single instance database conversion to RAC

New features of 11g R2.

RMAN Password encryption

 
You want to encrypt the backups made with RMAN in order to meet your organization’s security guidelines.By default,all RMAN backups are unencrypted but you can encrypt any RMAN backup in the form of a backup set.You can encrypt sets in two ways transparent encryption and password encryption.Here I’m going to show How to configure Password encryption.

Password Encryption :

RMAN perform encrypted backups by using the set encryption command. This method is called password encryption of backups since the DBA must provide a password both for creating an encrypted backup and for restoring an encrypted backup.

RMAN> set encryption on identified by azar only;

executing command: SET encryption

Here I choosed one tablespace for encryption.

RMAN> configure encryption for tablespace users on;

tablespace USERS will be encrypted in future backup sets
new RMAN configuration parameters are successfully stored

RMAN> backup tablespace users;

Starting backup at 05-JUN-10
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 05-JUN-10
channel ORA_DISK_1: finished piece 1 at 05-JUN-10
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYDB/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T154958_60nkzpxn_.bkp tag=TAG20100605T154958 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 05-JUN-10

RMAN> sql ‘alter tablespace users offline’;

sql statement: alter tablespace users offline

RMAN> restore tablespace users;

Starting restore at 05-JUN-10
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=148 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYDB/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T154958_60nkzpxn_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/05/2010 15:55:29
ORA-19870: error reading backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYDB/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T154958_60nkzpxn_.bkp
ORA-19913: unable to decrypt backup

So we need to set decryption.

RMAN> set decryption  identified by azar;

executing command: SET decryption

RMAN> restore tablespace users;

Starting restore at 05-JUN-10
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/oradata/mydb/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYDB/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T154958_60nkzpxn_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area/MYDB/backupset/2010_06_05/o1_mf_nnndf_TAG20100605T154958_60nkzpxn_.bkp tag=TAG20100605T154958
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 05-JUN-10

RMAN> recover tablespace users;

Starting recover at 05-JUN-10
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 05-JUN-10

RMAN> sql ‘alter tablespace users online’;

sql statement: alter tablespace users online

RMAN>