To simplify ongoing use of RMAN for backup and recovery, the RMAN lets you set a number of persistent configuration settings for each target database. To view these persistent configuration settings use SHOW ALL within RMAN command prompt.
To change these persistent configuration settings use CONFIGURE ... command.
If you want to clear anytime these settings to default mode then use, CONFIGURE... CLEAR command.
Whenever you set any parameter by CONFIGURE ... these settings is used while backup and recovery strategy whenever you do not specify any other format within Run block or at RMAN prompt. However you can override these settings while taking backup. The order of precedence is,
1.Set within Run Block.
2.Set at RMAN prompt.
3.Set with CONFIGURE
Some Examples:
--------------------
This example changes the default backup destination from disk to tape and then again backup to default.
RMAN> show default device type;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
new RMAN configuration parameters are successfully stored
RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
RMAN configuration parameters are successfully reset to default value
RMAN> show default device type;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored
RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
By default, the format of the autobackup file for all configured devices is the substitution variable %F. This variable format translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:
IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF
Wednesday, April 23, 2008
Issues between RMAN Client and Database
Some Issues between RMAN Client and Database:
---------------------------------------------------
•Most RMAN commands take a number of parameters and must end with a semicolon.The exception is some database related command , like STARTUP, SHUTDOWN and CONNECT.
•You can write rman commands in a file and then you can run the file. In both ways from command line and within RMAN command prompt you can check.
$rman TARGET / @filename
RMAN>@filename
After the command file contents have been executed, RMAN displays the following message:
RMAN> **end-of-file**
•With CHECKSYNTAX you can test some RMAN commands for syntactic correctness without executing them.In both ways from command line and within RMAN command prompt you can check.
$rman CHECKSYNTAX
RMAN>run your command.
From Command Line,
rman CHECKSYNTAX @filename
Example:
-------------
1)Make rmancmdtest file with following entry
-bash-3.00$ cat rmancmdtest
list backup;
show all;
2)From RMAN command prompt checking the syntax
RMAN> @/export/home/oracle/rmancmdtest
RMAN> list backup;
The cmdfile has no syntax errors
RMAN> show all;
The cmdfile has no syntax errors
RMAN> **end-of-file**
3)Now from OS Command line
-bash-3.00$ rman checksyntax @/export/home/oracle/rmancmdtest
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 24 10:16:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> list backup;
2> show all;
3>
The cmdfile has no syntax errors
Access database with SQL command from RMAN:
-----------------------------------------------------
You can write sql commands with RMAN to access database. In the following section I have given an example.
1)Here I used System/a but it actually connected to database with SYSDBA privilege automatically, OS Authentication is used.
bash-3.00$ rman target system/a
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 24 09:23:35 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DATA1 (DBID=2547250380)
2)After connecting I created a Table and Insert one row.
RMAN> sql'create table test (a number)';
using target database control file instead of recovery catalog
sql statement: create table test (a number)
RMAN> sql'insert into test values(100)';
using target database control file instead of recovery catalog
sql statement: insert into test values(100)
3)Now connect with system and see the object. Because SYSDBA privilege conenction use SYS schema so in SYSTEM there is no object. Here system/a is nothing but a show. Any user or any password would work if current user is a member of OSDBA group.
-bash-3.00$ sqlplus system/a
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 24 09:23:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> desc test;
ERROR:
ORA-04043: object test does not exist
4)Now Check object in SYS schema
SQL> conn / as sysdba
Connected.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
SQL> select * from test;
A
----------
100
Caution: As to run any SQL command it is needed to connect to target database and to connect target database you must have SYSDBA privilege and SYSDBA privilege use SYS schema so it is not good practice to create object from RMAN client.
Though you can create obejct in other schema from RMAN using,
RMAN> sql'create table system.test(a number)';
using target database control file instead of recovery catalog
sql statement: create table system.test(a number)
Related Documents:
----------------------
DBA Authentication
Interaction with RMAN Client
---------------------------------------------------
•Most RMAN commands take a number of parameters and must end with a semicolon.The exception is some database related command , like STARTUP, SHUTDOWN and CONNECT.
•You can write rman commands in a file and then you can run the file. In both ways from command line and within RMAN command prompt you can check.
$rman TARGET / @filename
RMAN>@filename
After the command file contents have been executed, RMAN displays the following message:
RMAN> **end-of-file**
•With CHECKSYNTAX you can test some RMAN commands for syntactic correctness without executing them.In both ways from command line and within RMAN command prompt you can check.
$rman CHECKSYNTAX
RMAN>run your command.
From Command Line,
rman CHECKSYNTAX @filename
Example:
-------------
1)Make rmancmdtest file with following entry
-bash-3.00$ cat rmancmdtest
list backup;
show all;
2)From RMAN command prompt checking the syntax
RMAN> @/export/home/oracle/rmancmdtest
RMAN> list backup;
The cmdfile has no syntax errors
RMAN> show all;
The cmdfile has no syntax errors
RMAN> **end-of-file**
3)Now from OS Command line
-bash-3.00$ rman checksyntax @/export/home/oracle/rmancmdtest
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 24 10:16:07 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
RMAN> list backup;
2> show all;
3>
The cmdfile has no syntax errors
Access database with SQL command from RMAN:
-----------------------------------------------------
You can write sql commands with RMAN to access database. In the following section I have given an example.
1)Here I used System/a but it actually connected to database with SYSDBA privilege automatically, OS Authentication is used.
bash-3.00$ rman target system/a
Recovery Manager: Release 10.2.0.1.0 - Production on Thu Apr 24 09:23:35 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DATA1 (DBID=2547250380)
2)After connecting I created a Table and Insert one row.
RMAN> sql'create table test (a number)';
using target database control file instead of recovery catalog
sql statement: create table test (a number)
RMAN> sql'insert into test values(100)';
using target database control file instead of recovery catalog
sql statement: insert into test values(100)
3)Now connect with system and see the object. Because SYSDBA privilege conenction use SYS schema so in SYSTEM there is no object. Here system/a is nothing but a show. Any user or any password would work if current user is a member of OSDBA group.
-bash-3.00$ sqlplus system/a
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Apr 24 09:23:10 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> desc test;
ERROR:
ORA-04043: object test does not exist
4)Now Check object in SYS schema
SQL> conn / as sysdba
Connected.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
SQL> select * from test;
A
----------
100
Caution: As to run any SQL command it is needed to connect to target database and to connect target database you must have SYSDBA privilege and SYSDBA privilege use SYS schema so it is not good practice to create object from RMAN client.
Though you can create obejct in other schema from RMAN using,
RMAN> sql'create table system.test(a number)';
using target database control file instead of recovery catalog
sql statement: create table system.test(a number)
Related Documents:
----------------------
DBA Authentication
Interaction with RMAN Client
Interact with RMAN Client.
Types and Authentication of Database that RMAN used to Connect:
-------------------------------------------------
RMAN client can connect to three types of databases
1)The Target Database: The database which need to perform backup and recovery operation that database connection with RMAN client must be the target database.
To connect to the target database you must have the SYSDBA privilege.
2)The Recovery Catalog Database:The recovery catalog database provides an optional backup store for the RMAN repository. To connect to the recovery catalog database you must need the RECOVERY_CATALOG_OWNER role.
3)The Auxiliary Database:RMAN connect to the auxiliary database for performing a specific task such as duplicating a database, transporting tablespaces without taking the making database read-only, or performing tablespace point-in-time recovery.
RMAN does not require that you specify the SYSDBA privilege when connecting to a database. Because all RMAN database connections require SYSDBA privilege, RMAN always implicitly attempts to connect with this privilege.
To connect to RMAN client you can use password file for either local or remote access. But,if you are connecting remotely as SYSDBA with a net service name you must use a password file with a net service name.
Connecting to the target Database with and without Password file:
To use OS Authentication,
rman target /
To use Password file Authentication if OS Authentication not available,
rman target SYS/arju@arju
Related Documents:
----------------------
-------------------------------------------------
RMAN client can connect to three types of databases
1)The Target Database: The database which need to perform backup and recovery operation that database connection with RMAN client must be the target database.
To connect to the target database you must have the SYSDBA privilege.
2)The Recovery Catalog Database:The recovery catalog database provides an optional backup store for the RMAN repository. To connect to the recovery catalog database you must need the RECOVERY_CATALOG_OWNER role.
3)The Auxiliary Database:RMAN connect to the auxiliary database for performing a specific task such as duplicating a database, transporting tablespaces without taking the making database read-only, or performing tablespace point-in-time recovery.
RMAN does not require that you specify the SYSDBA privilege when connecting to a database. Because all RMAN database connections require SYSDBA privilege, RMAN always implicitly attempts to connect with this privilege.
To connect to RMAN client you can use password file for either local or remote access. But,if you are connecting remotely as SYSDBA with a net service name you must use a password file with a net service name.
Connecting to the target Database with and without Password file:
To use OS Authentication,
rman target /
To use Password file Authentication if OS Authentication not available,
rman target SYS/arju@arju
Related Documents:
----------------------
Use RMAN to Validate Before and After taking backup
If you want to verify whether there any physical or logical corruptions in database file before taking backup you can use the VALIDATE option of the BACKUP command.When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.
If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions.
Also with RMAN RESTORE ... VALIDATE before restore database or any datafile you can verify whether database can successfully restored or not.
An example:Before Performing Backup Check Any Physical or Logical Corruption:
SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 23 17:15:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DATA1 (DBID=2547250380)
RMAN> backup validate database;
Starting backup at 23-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
.
.
.
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-APR-08
After Backup Check if they can be Restored Successfully Using Validate BackupSet
RMAN> validate backupset 4;
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/10.2.0/db_1/dbs/05jei8qh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/05jei8qh_1_1 tag=TAG20080423T165857
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
Using RESTORE .. VALIDATE
RMAN> restore database validate;
Starting restore at 23-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/10.2.0/db_1/dbs/0bjeiams_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/0bjeiams_1_1 tag=TAG20080423T173108
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
Finished restore at 23-APR-08
You can also validate single tablespace or datafile or any other like,
RMAN> restore datafile 1 validate;
RMAN> restore tablespace system validate;
RMAN> restore controlfile validate;
RMAN> restore spfile validate;
Related Documents:
Verify Physical Data Structure
If the backup validation discovers corrupt blocks, then RMAN updates the V$DATABASE_BLOCK_CORRUPTION view with rows describing the corruptions.
Also with RMAN RESTORE ... VALIDATE before restore database or any datafile you can verify whether database can successfully restored or not.
An example:Before Performing Backup Check Any Physical or Logical Corruption:
SQL> !rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Wed Apr 23 17:15:24 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: DATA1 (DBID=2547250380)
RMAN> backup validate database;
Starting backup at 23-APR-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
.
.
.
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 23-APR-08
After Backup Check if they can be Restored Successfully Using Validate BackupSet
RMAN> validate backupset 4;
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/10.2.0/db_1/dbs/05jei8qh_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/05jei8qh_1_1 tag=TAG20080423T165857
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
Using RESTORE .. VALIDATE
RMAN> restore database validate;
Starting restore at 23-APR-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile backupset
channel ORA_DISK_1: reading from backup piece /oracle/app/oracle/product/10.2.0/db_1/dbs/0bjeiams_1_1
channel ORA_DISK_1: restored backup piece 1
piece handle=/oracle/app/oracle/product/10.2.0/db_1/dbs/0bjeiams_1_1 tag=TAG20080423T173108
channel ORA_DISK_1: validation complete, elapsed time: 00:00:04
Finished restore at 23-APR-08
You can also validate single tablespace or datafile or any other like,
RMAN> restore datafile 1 validate;
RMAN> restore tablespace system validate;
RMAN> restore controlfile validate;
RMAN> restore spfile validate;
Related Documents:
Verify Physical Data Structure
Tuesday, April 22, 2008
The Oracle Flashback Technology
Oracle Flashback Technology provides a set of features that provide useful alternatives to view past states of data, and winding data back and forth in time, without requiring you to restore large portions of your database from backup or perform point-in-time recovery. The flashback features of Oracle are more efficient and less disruptive than media recovery in most circumstances.
The available Flashback Features:
---------------------------------
A)Oracle Flashback Query: It lets you to view past state data as they would have appeared at that time.
It is based on undo data. Show UNDO_RETENTION parameter.
B)Oracle Flashback Version Query: It lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval.
It is based on undo data. Show UNDO_RETENTION parameter.
C)Oracle Flashback Transaction Query: It lets you view changes made by a single transaction, or by all the transactions during a period of time.
It is based on undo data. Show UNDO_RETENTION parameter.
D)Oracle Flashback Table: It returns a table to its state at a previous point in time.
It is based on undo data. Show UNDO_RETENTION parameter.
E)Oracle Flashback Drop: To get back a dropped table.
It is based on Recyclebin. Use show recyclebin.
F)Oracle Flashback Database: To get back whole database to a previous point in time. It is based on whether flashback feature is enabled or not. Use select flashback_on from V$database; to see it is on or not. By default it is disabled. However you can enable it by ALTER DATBASE FLASHBACK ON in mount stage.
You can have a look at Flashback Database
Flashback query is discussed on Oracle Flashback query
Flashback version query, flashback transaction query is discussed on Flashback Features
Flashback Table, flashback Drop is discussed on Flashback Table And Flashback Drop
The available Flashback Features:
---------------------------------
A)Oracle Flashback Query: It lets you to view past state data as they would have appeared at that time.
It is based on undo data. Show UNDO_RETENTION parameter.
B)Oracle Flashback Version Query: It lets you view all the versions of all the rows that ever existed in one or more tables in a specified time interval.
It is based on undo data. Show UNDO_RETENTION parameter.
C)Oracle Flashback Transaction Query: It lets you view changes made by a single transaction, or by all the transactions during a period of time.
It is based on undo data. Show UNDO_RETENTION parameter.
D)Oracle Flashback Table: It returns a table to its state at a previous point in time.
It is based on undo data. Show UNDO_RETENTION parameter.
E)Oracle Flashback Drop: To get back a dropped table.
It is based on Recyclebin. Use show recyclebin.
F)Oracle Flashback Database: To get back whole database to a previous point in time. It is based on whether flashback feature is enabled or not. Use select flashback_on from V$database; to see it is on or not. By default it is disabled. However you can enable it by ALTER DATBASE FLASHBACK ON in mount stage.
You can have a look at Flashback Database
Flashback query is discussed on Oracle Flashback query
Flashback version query, flashback transaction query is discussed on Flashback Features
Flashback Table, flashback Drop is discussed on Flashback Table And Flashback Drop
New Feature of 10.2g: Eliminate Control File Re-Creation
Before Oracle 10.2g if we need to change the limit of MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES then the possible solutions is either RE-create new controlfile or create a new database.
But from Oracle 10.2g all sections of the control file are now automatically extended when they run out of space. This means that there is no longer a requirement to re-create the control file when changes in the configuration parameter the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are needed.
Two different Sections of Control file:
--------------------------------------------
1)Circularly reusable Sections:
The CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. Example of circularly reusable records are archive log records and various backup records.
2)Not circularly reusable Sections:
Records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.
For the circularly reusable sections the behaviour remain same with previous version.
The new feature in Oracle 10.2g is that for the non-reusable records we now also extend the control file size if we go over the previous limit.
Though the values for MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are still useful since they initialize the control file at a certain size, but they no longer set a hard limit for the number of records in the control file.
Therefore 10.2g onwards, we can get rid of re-creating controlfile whenever we need change parameter like MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES and keep the database alive.
Related Documents:
----------------------
How and When to Re-create Controlfile
But from Oracle 10.2g all sections of the control file are now automatically extended when they run out of space. This means that there is no longer a requirement to re-create the control file when changes in the configuration parameter the MAXLOGFILE, MAXLOGMEMBERS, MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are needed.
Two different Sections of Control file:
--------------------------------------------
1)Circularly reusable Sections:
The CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. Example of circularly reusable records are archive log records and various backup records.
2)Not circularly reusable Sections:
Records such as datafile, tablespace, and redo thread records, which are never reused unless the corresponding object is dropped from the tablespace.
For the circularly reusable sections the behaviour remain same with previous version.
The new feature in Oracle 10.2g is that for the non-reusable records we now also extend the control file size if we go over the previous limit.
Though the values for MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES are still useful since they initialize the control file at a certain size, but they no longer set a hard limit for the number of records in the control file.
Therefore 10.2g onwards, we can get rid of re-creating controlfile whenever we need change parameter like MAXLOGFILE, MAXLOGMEMBERS,MAXLOGHISTORY, MAXDATAFILES, and MAXINSTANCES and keep the database alive.
Related Documents:
----------------------
How and When to Re-create Controlfile
Advantage/Difference of Image Copy over Backup Set
•RMAN supports binary compression of backup sets, where the backup set contents are compressed before being written to disk using a compression algorithm tuned for compression of datafiles and archived log files. But with Image copy no compression is done.
•A backup set never contains empty blocks. As RMAN passes through the datafiles, it simply skips blocks that have never been used. But image copy contains empty blocks. This means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.
•Incremental backups can't be performed with Image copy but incremental backups can be taken over backup sets.
•RMAN can take backup of backup sets directly to tape, if you have installed the RMAN drivers for your tape library.But RMAN can't take backup of image copies directly to tape.
•Both Image copy and backup sets can be restored from RMAN. From Operating System Backup sets can't be generated or restored.
•RMAN will check the contents for corruption for both Image copy and backupsets but native operating-system file copy utilities cannot check corruption while taking backup of Image copy.
Related Documents:
Types of Oracle Database Backup
•A backup set never contains empty blocks. As RMAN passes through the datafiles, it simply skips blocks that have never been used. But image copy contains empty blocks. This means that backups of datafiles as backup sets are generally smaller than image copy backups and take less time to write.
•Incremental backups can't be performed with Image copy but incremental backups can be taken over backup sets.
•RMAN can take backup of backup sets directly to tape, if you have installed the RMAN drivers for your tape library.But RMAN can't take backup of image copies directly to tape.
•Both Image copy and backup sets can be restored from RMAN. From Operating System Backup sets can't be generated or restored.
•RMAN will check the contents for corruption for both Image copy and backupsets but native operating-system file copy utilities cannot check corruption while taking backup of Image copy.
Related Documents:
Types of Oracle Database Backup
Subscribe to:
Posts (Atom)