RMAN password encryption video
I just added RMAN password configuration, How to configure and Its working.
Archive for
Jun 23
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.
Jun 9
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
Jun 8
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.
Jun 5
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>
DBA 