Restore point is nothing but a name given to a specified point of a database status.It can define the status of a database on a specific time or SCN number.So, that when we flashback database , we can mention the name of the restore point in stead of a specific date and time or a SCN number .
Oracle will automatically flashback the database to that specific restore point. It is a mapping of SCN number to a restore point name. So, when we flashback database to a restore point we do, nothing but restoring to a specific SCN.
There are two different type of restore point:
a) Normal restore point or nonguaranteed restore point
b) Guaranteed restore point
a) To create Normal restore point do the following:
sql> create restore point ershad;
[I have given the restore point name as "ershad"]
So, when required , we can flashback the database upto this restore point.But, CONDITIONS APPLY....
whether database can be flashbacked upto the restore point depends on whether you have the flashback logs in the Flash Recovery Area or not.If you have, you can restore upto that point.But , it is not Guaranteed every time.so, you may face the following error while trying to flashback to a normal restore point.
ORA-38729: Not enough flashback database log data to do FLASHBACK
This error means the database does not have the flashback logs needed to go back to the restore point or time or SCN that you’ve specified
***Check db_flashback_retention_target for more on flashback logs retention period.***
b) Guaranteed restore point:
This will guarantee that at any point in time you can flashback the database to the restore point. As , Oracle will make sure the flashback logs will not be deleted until that restore point , whatever the case is.
sql> create restore point askershad guarantee flashback database;
[I have given the restore point name as "askershad"]
Note: As Guaranteed restore point assures flashback logs will not be deleted.So Be Careful about the space consumed in Flash recovery area.And create this only when you require.like , you need to check your database behaviour with a new application and you are going to revert back after the test. And please DROP the restore point after your job is done.Because,for a Guaranteed restore point, the associated flashback logs will never be deleted until you drop the restore point. And not doing so may end up no space in flash recovery area and cause a recovery writer(RVWR) process failure which leads to database instance failure......
TO CHECK RESTORE POINTS:
sql> select * from v$restore_point;
SCN DATABASE_INCARNATION# GUA STORAGE_SIZE TIME NAME
------- -------------------------------- ------ ------------------- ----------- -------
916841 --------11----------------------YES-----3981312----------22-FEB-09-- ASKERSHAD
916831-------- 11---------------------- NO-----------0-----------22-FEB-09---ERSHAD
SCN--> To which SCN the restore point is mapped
DATABASE_INCARNATION#-->This column displays the incarnation of the database
when this restore point was created
GUARANTEE_FLASHBACK_DATABASE-->whether it is guaranteed or normal restore point
STORAGE_SIZE-->What is the flashback log size***
TIME-->To what TIME the restore point is mapped
NAME-->Name of the restored point
***look here , The normal restored point does not have a size mentioned. As it requires the flashback logs from the point of SCN number. If logs are not there,it is not possible to flashback. The guaranteed one will keep the logs so, the size of logs belong to it and assures the flashback.
TO DROP RESTORE POINTS:
SQL> drop restore point ershad;(for both types the command is same)
More Topic:
FLASHBACK DATABASE TO A RESTORE POINT
Thanks,
Have a nice day..........
Sunday, February 22, 2009
Oracle:flashback database to earlier time from SQL prompt
In the last post we have seen how to flashback a database using RMAN utility. And i have discussed about the pros and cons of flashback database also. To review please check:
HOW TO FLASHBACK DATABASE USING RMAN UTILITY
Now , we will see what are the options available to flashback database from SQL*plus utility.
being in the sql prompt also you can do several flashback of database .Available options are:
1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point
In Detail::
1) flashback to a specific date and time:
sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
sql>flashback database to timestamp to_date('14-FEB-2007 11:30:00','DD-MON-YYYY HH24:MI:SS');
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.
sql>alter database open resetlogs;
2) flashback to a specific SCN number:
sql>connect sys/e@lion as sysdba
Check the current SCN number first:
sql> select current_scn from v$database;
CURRENT_SCN
-----------
916022
Then,
sql>shutdown immediate
sql>startup mount
sql> flashback database to scn 915000;
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.
sql>alter database open resetlogs;
3) flashback to a named restore point:
sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"
sql>flashback database to restore point ershad;
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.
sql>alter database open resetlogs;
Topic explained:
WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT
HOW TO FLASHBACK DATABASE USING RMAN UTILITY
Now , we will see what are the options available to flashback database from SQL*plus utility.
being in the sql prompt also you can do several flashback of database .Available options are:
1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point
In Detail::
1) flashback to a specific date and time:
sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
sql>flashback database to timestamp to_date('14-FEB-2007 11:30:00','DD-MON-YYYY HH24:MI:SS');
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.
sql>alter database open resetlogs;
2) flashback to a specific SCN number:
sql>connect sys/e@lion as sysdba
Check the current SCN number first:
sql> select current_scn from v$database;
CURRENT_SCN
-----------
916022
Then,
sql>shutdown immediate
sql>startup mount
sql> flashback database to scn 915000;
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.
sql>alter database open resetlogs;
3) flashback to a named restore point:
sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"
sql>flashback database to restore point ershad;
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.
sql>alter database open resetlogs;
Topic explained:
WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT
Saturday, February 14, 2009
HOW to check the maximum flashback possible of a database
If you want to check how far back you can flashback the database.please do following:
sql> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
sql> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- ---------------- -------------- ----------------------------------------------------------------------------------------------
907191---- 14-FEB-2009 12:37:20----- 1440------ 8192000--------73728
so we can flashback database to any time in betweeen upto 14-feb-2009 till 12:37:20 or upto the scn number 907191. but the condition is , we should have the flashback logs available and the archived logs are also available. Because flashback feature uses archive logs to undo the changes.
columns of V$FLASHBACK_DATABASE_LOG:
OLDEST_FLASHBACK_SCN-->The oldest SCN to which you can flash back the database.
OLDEST_FLASHBACK_TIME-->The earliest time to which you can flash back the database.
RETENTION_TARGET-->The initialization parameter db_flashback_retention_target determines how long the flashback logs are retained,values measurement is in minutes.
FLASHBACK_SIZE-->Current size (in bytes) of the flashback data.
ESTIMATED_FLASHBACK_SIZE--> Estimated size of flashback data needed for the current target retention(will be discussed shortly)
DB_FLASHBACK_RETENTION_TARGET
This parameter value decides how long the flashback logs should be kept. remember undo_retention parameter, it behaves the same way. It will keep the flashback logs for that much time but still conditions apply.....
as it depends on the size of flash recovery area also.DB_RECOVERY_FILE_DEST_SIZE decides the size of flashback recovery area. So, DB_FLASHBACK_RETENTION_TARGET will try to keep the flashback logs until the time , if it has sufficient space. If not , FRA will delete the oldest logs to keep new one.the reverse of this, if space is enough , it can keep logs more than the time mentioned in DB_FLASHBACK_RETENTION_TARGET parameter.
in my query to v$flashback_database_log you can see FLASHBACK_SIZE is 8192000(which is 8 MB almost) and ESTIMATED_FLASH_SIZE is 73728(which is 72 KB).So, as of my retention parameter i require 72 kb of space and compare to that i have 8MB. So it will be well enough . But on the real time active database,the reverse is almost always true.
HOW TO ESTIMATE FLASHBACK LOG SIZE
thanks,
have a nice day
sql> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
Session altered.
sql> select * from v$flashback_database_log;
OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------------- -------------------- ---------------- -------------- ----------------------------------------------------------------------------------------------
907191---- 14-FEB-2009 12:37:20----- 1440------ 8192000--------73728
so we can flashback database to any time in betweeen upto 14-feb-2009 till 12:37:20 or upto the scn number 907191. but the condition is , we should have the flashback logs available and the archived logs are also available. Because flashback feature uses archive logs to undo the changes.
columns of V$FLASHBACK_DATABASE_LOG:
OLDEST_FLASHBACK_SCN-->The oldest SCN to which you can flash back the database.
OLDEST_FLASHBACK_TIME-->The earliest time to which you can flash back the database.
RETENTION_TARGET-->The initialization parameter db_flashback_retention_target determines how long the flashback logs are retained,values measurement is in minutes.
FLASHBACK_SIZE-->Current size (in bytes) of the flashback data.
ESTIMATED_FLASHBACK_SIZE--> Estimated size of flashback data needed for the current target retention(will be discussed shortly)
DB_FLASHBACK_RETENTION_TARGET
This parameter value decides how long the flashback logs should be kept. remember undo_retention parameter, it behaves the same way. It will keep the flashback logs for that much time but still conditions apply.....
as it depends on the size of flash recovery area also.DB_RECOVERY_FILE_DEST_SIZE decides the size of flashback recovery area. So, DB_FLASHBACK_RETENTION_TARGET will try to keep the flashback logs until the time , if it has sufficient space. If not , FRA will delete the oldest logs to keep new one.the reverse of this, if space is enough , it can keep logs more than the time mentioned in DB_FLASHBACK_RETENTION_TARGET parameter.
in my query to v$flashback_database_log you can see FLASHBACK_SIZE is 8192000(which is 8 MB almost) and ESTIMATED_FLASH_SIZE is 73728(which is 72 KB).So, as of my retention parameter i require 72 kb of space and compare to that i have 8MB. So it will be well enough . But on the real time active database,the reverse is almost always true.
HOW TO ESTIMATE FLASHBACK LOG SIZE
thanks,
have a nice day
Oracle:flashback a database to earlier time using RMAN
we can flashback an Oracle database with following features:
1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point
4) flashback to last resetlogs option
first of all, we need to enable flashback and properly size the flash recovery area so that flashback logs are created and we can flashback our database to a previous stage.
to do that please check the following:
HOW TO ENABLE FLASHBACK
HOW TO CONFIGURE FLASH RECOVERY AREA
now, it is configure properly and we can flashback our database as required.
One more information you may need, that is how far back you can flashback the database.
follow the link for this,MAXIMUM FLASHBACK POSSIBLE FOR ORACLE DATABASE
1)Flashback to a specific date and time:(steps)
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
RMAN> flashback database to time 'sysdate-50/60/24'; (doing a flashback to 50 minutes from sysdate)
OUTPUT:
Starting flashback at 14-FEB-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished flashback at 14-FEB-09
at this point, you may need to check whether database has been flashbackED to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> sql 'alter database open read only';
and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;
If answer is no, you can redo the procedure to flashback to a diiferent TIME. that is,shutdown database,startup mount,flashback to the desired TIME,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.
RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........
if you want to mention a specific time then:
RMAN> flashback database to time "to_date('13-FEB-2009 11:00:00','DD-MON-YYYY hh24:mi:ss')";
2) flashback to a specific SCN number
first check the current scn of the database by issuing following statement:
connect as sysdba
sql> select current_scn from v$database;
CURRENT_SCN
-----------
909158
that means we can flashback to a SCN prior to this SCN only.
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
now, think i want to flashback till SCN number--
RMAN> flashback database to scn 909100;
Starting flashback at 22-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 22-FEB-09
at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> sql 'alter database open read only';
Now , check this,
sys@LION> select current_scn from v$database;
CURRENT_SCN
-----------
909100
and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;
If answer is no, you can redo the procedure to flashback to a diiferent SCN. that is,shutdown database,startup mount,flashback to the desired SCN ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.
RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........
3.Flashback to a Restore Point:
we can flashback a database to a restore point also.
to do that,
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"
RMAN> flashback database to restore point ershad;
at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> alter database open read only;
and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;
If answer is no, you can redo the procedure to flashback to a diiferent restore point. that is,shutdown database,startup mount,flashback to the desired Restore point ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.
RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........
WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT
4)flashback to last resetlogs option
At last , During the flashback operation , we may end up with a mistake like:
You may have flashback to a wrong point. As we know it is still recoverable, BY opening the database in read only mode,you can check whether the operation is desired or not . if yes , open the database with resetlog options, if no, you can redo the procedure to your desired point once again.
Problem is , if you have flashback to a point and open the database with resetlogs options. And now feel that it was wrong.
Dont worry. You can still go back to the previous incarnation and recover your old state of database before resetlogs.Because, Oracle knows you expect a lot from him.
Let us check, HOW TO FLASHBACK DATABASE TO PRIOR TO LAST RESETLOGS OPTIONS:
RMAN> connect target /@lion
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> flashback database to before resetlogs;
RMAN> alter database open resetlogs;
Note: When we do a resetlogs, oracle will record the SCN . So the previous operation is nothing but flashing back to a previous SCN. We can check the last resetlogs SCN from the following query:
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
911255
Note: Assume the current SCN is 25444. And I have flashback logs Till 22999. So, we can flashback to any point in between this.and more important , backward and forward both are possible in this range till you reach your expected point. I mean to say , once you flashback to 23888 and found it is not right and you need a bit forward. So, you can then flashback to 24000(which is actually going forward as of your last operation).You can flashback as many times in your range till the flashback logs are available and you have not opened it with resetlogs option.
More:
FLASHBACK A DATABASE FROM SQL PROMPT
MAXIMUM FLASHBACK POSSIBLE
Thanks a lot.....
Have a nice day
1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point
4) flashback to last resetlogs option
first of all, we need to enable flashback and properly size the flash recovery area so that flashback logs are created and we can flashback our database to a previous stage.
to do that please check the following:
HOW TO ENABLE FLASHBACK
HOW TO CONFIGURE FLASH RECOVERY AREA
now, it is configure properly and we can flashback our database as required.
One more information you may need, that is how far back you can flashback the database.
follow the link for this,MAXIMUM FLASHBACK POSSIBLE FOR ORACLE DATABASE
1)Flashback to a specific date and time:(steps)
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
RMAN> flashback database to time 'sysdate-50/60/24'; (doing a flashback to 50 minutes from sysdate)
OUTPUT:
Starting flashback at 14-FEB-09
using channel ORA_DISK_1
starting media recovery
media recovery complete, elapsed time: 00:00:07
Finished flashback at 14-FEB-09
at this point, you may need to check whether database has been flashbackED to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> sql 'alter database open read only';
and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;
If answer is no, you can redo the procedure to flashback to a diiferent TIME. that is,shutdown database,startup mount,flashback to the desired TIME,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.
RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........
if you want to mention a specific time then:
RMAN> flashback database to time "to_date('13-FEB-2009 11:00:00','DD-MON-YYYY hh24:mi:ss')";
2) flashback to a specific SCN number
first check the current scn of the database by issuing following statement:
connect as sysdba
sql> select current_scn from v$database;
CURRENT_SCN
-----------
909158
that means we can flashback to a SCN prior to this SCN only.
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
now, think i want to flashback till SCN number--
RMAN> flashback database to scn 909100;
Starting flashback at 22-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished flashback at 22-FEB-09
at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> sql 'alter database open read only';
Now , check this,
sys@LION> select current_scn from v$database;
CURRENT_SCN
-----------
909100
and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;
If answer is no, you can redo the procedure to flashback to a diiferent SCN. that is,shutdown database,startup mount,flashback to the desired SCN ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.
RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........
3.Flashback to a Restore Point:
we can flashback a database to a restore point also.
to do that,
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"
RMAN> flashback database to restore point ershad;
at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> alter database open read only;
and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;
If answer is no, you can redo the procedure to flashback to a diiferent restore point. that is,shutdown database,startup mount,flashback to the desired Restore point ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.
RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........
WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT
4)flashback to last resetlogs option
At last , During the flashback operation , we may end up with a mistake like:
You may have flashback to a wrong point. As we know it is still recoverable, BY opening the database in read only mode,you can check whether the operation is desired or not . if yes , open the database with resetlog options, if no, you can redo the procedure to your desired point once again.
Problem is , if you have flashback to a point and open the database with resetlogs options. And now feel that it was wrong.
Dont worry. You can still go back to the previous incarnation and recover your old state of database before resetlogs.Because, Oracle knows you expect a lot from him.
Let us check, HOW TO FLASHBACK DATABASE TO PRIOR TO LAST RESETLOGS OPTIONS:
RMAN> connect target /@lion
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> flashback database to before resetlogs;
RMAN> alter database open resetlogs;
Note: When we do a resetlogs, oracle will record the SCN . So the previous operation is nothing but flashing back to a previous SCN. We can check the last resetlogs SCN from the following query:
SQL> select resetlogs_change# from v$database;
RESETLOGS_CHANGE#
-----------------
911255
Note: Assume the current SCN is 25444. And I have flashback logs Till 22999. So, we can flashback to any point in between this.and more important , backward and forward both are possible in this range till you reach your expected point. I mean to say , once you flashback to 23888 and found it is not right and you need a bit forward. So, you can then flashback to 24000(which is actually going forward as of your last operation).You can flashback as many times in your range till the flashback logs are available and you have not opened it with resetlogs option.
More:
FLASHBACK A DATABASE FROM SQL PROMPT
MAXIMUM FLASHBACK POSSIBLE
Thanks a lot.....
Have a nice day
ORA-38759: Database must be mounted by only one instance and not open
i have received an error like the following while trying to enable flashback on my database.
sql> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
Reason is: you cannot enable flashback feature when the database is in open stage.
sql>select status from v$instance;
STATUS
------------
OPEN
solution:
do the following:
sql>shutdown immediate;
sql> startup mount;
sql> alter database flashback on;
sql> alter database open;
But, we can disable flashback feature , when the database is in open state too.
To check whether flashback feature is enable or not:
sql> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Oracle 10G/11G: Flashback features and example
Oracle 10G/11G: Different flashback options
Thanks,
have a nice day..................
sql> alter database flashback on;
alter database flashback on
*
ERROR at line 1:
ORA-38759: Database must be mounted by only one instance and not open.
Reason is: you cannot enable flashback feature when the database is in open stage.
sql>select status from v$instance;
STATUS
------------
OPEN
solution:
do the following:
sql>shutdown immediate;
sql> startup mount;
sql> alter database flashback on;
sql> alter database open;
But, we can disable flashback feature , when the database is in open state too.
To check whether flashback feature is enable or not:
sql> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO
Oracle 10G/11G: Flashback features and example
Oracle 10G/11G: Different flashback options
Thanks,
have a nice day..................
Oracle flashback feature
Oracle flashback feature:
In ORACLE 10G, a new feature introduced named as FLASHBACK.actually in ORACLE 9i, also there was a feature called FLASHBACK QUERIES. Dont get confused about it.... think like the full featured FLASHBACK is from 10G. in 9i, it had only one option available compared to 10G flashback options.In 10G, FLASHBACK refers as one tool which supports different types of data recovery
flashback features:
i) flashback a database to prior to present time
ii) flashback a table to prior to present time
iii) flashback/rollback a drop table statement(means rollback a table which is dropped)
i) FLASHBACK DATABASE:
earlier till 9i, if we need to go to previous time of a database then we need to do incomplete recovery(DBPITR). That is restore a backup previous to the time you want to recover and apply archived logs till the time you want to recover, then open the database with resetlogs option.10G onwards, we can flashback our database to a previous point in time. Flashback feature uses flashback logs(additional logs to be created for this feature) to do the flashback. Flashback logs will maintain the block changes information and this will be refered to , when you want to flashback your database.Oracle will read the flashback logs and undo the changed blocks to roll it backward.
Note: The entire database will be rolled back. you cannot perform flashback on individual tables or tablespaces.
ii) FLASHBACK a table data to prior time:
this was introduced in Oracle9i as flashback query feature. Remember, read consistancy....snapshot too old error .we can have read consistency till the data was not committed by another transaction.If transaction gets rollback the undo will be applied again. If commited you will see the newest data. Till the transaction going on you can see the read consistent data from undo. And so, When the change is committed, the need for the past image is gone, but it’s not discarded till there is a need of undo space for other transactions.So, the concept of flashback a table is why not to use the undo to get a table to a previous stage.yes we can do that.but only till the undo is available.
and we can resize the undo also depending on how much back image of a table i want.........
iii) Rollback/restore a dropped table:
we know DDL statements connot be rollback. Means if you drop a table,that is gone. But this works only till oracle 9i.
in 10G,you can restore a table which was dropped by a ddl commands.
Check whether flashback is enabled or not in oracle 10G:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
If it shows no means flashback is not enabled
How to enable flashback in oracle 10G
step 1: Database should be in archive log mode.(HOW TO ENABLE /DISABLE ARCHIVE LOG)
step 2: FLASH RECOVERY AREA must be configured.(HOW TO CONFIGURE FLASH RECOVERY AREA)
step 3:
sql> conn sys/**@lion as sysdba
sql>startup mount
sql>alter database flashback on;
sql> alter database open;
Note: enabling flashback generates flashbacks logs which tracks the changes to data blocks. Flash recovery area is the only place where it stores flashback logs. So, flash recovery area must to be configured.Secondly, having only flashback logs will not help to flashback database ,it needs the archive logs to be applied to undo the blocks.so,the database must be in archive log mode.Remember one more thing, sizing the flash recovery area is very much important.
if you size it inadequetly it may end up having no space in FRA, so,database may hang,or will not startup and you may receive the following errors :(because it may be the location for your rman backup storage and archived logs also. so, no space here means you are in trouble)
Check the errors you may receive if flashback recovery area lack of space:
1) You see a message in the alert log similar to the following:
Can not open flashback thread because there is no more space in flash recovery area
2)If the database has aborted earlier because of flashback errors and you attempt to
start it, you get the following error:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
we will talk about how we can solve this issues in separate discussion. Let us come back to the discussion here.
Using flashback:
HOW to flashback a DATABASE
Other available options:
What should be the size of FRA(flash recovery area)
How to disable flashback
In ORACLE 10G, a new feature introduced named as FLASHBACK.actually in ORACLE 9i, also there was a feature called FLASHBACK QUERIES. Dont get confused about it.... think like the full featured FLASHBACK is from 10G. in 9i, it had only one option available compared to 10G flashback options.In 10G, FLASHBACK refers as one tool which supports different types of data recovery
flashback features:
i) flashback a database to prior to present time
ii) flashback a table to prior to present time
iii) flashback/rollback a drop table statement(means rollback a table which is dropped)
i) FLASHBACK DATABASE:
earlier till 9i, if we need to go to previous time of a database then we need to do incomplete recovery(DBPITR). That is restore a backup previous to the time you want to recover and apply archived logs till the time you want to recover, then open the database with resetlogs option.10G onwards, we can flashback our database to a previous point in time. Flashback feature uses flashback logs(additional logs to be created for this feature) to do the flashback. Flashback logs will maintain the block changes information and this will be refered to , when you want to flashback your database.Oracle will read the flashback logs and undo the changed blocks to roll it backward.
Note: The entire database will be rolled back. you cannot perform flashback on individual tables or tablespaces.
ii) FLASHBACK a table data to prior time:
this was introduced in Oracle9i as flashback query feature. Remember, read consistancy....snapshot too old error .we can have read consistency till the data was not committed by another transaction.If transaction gets rollback the undo will be applied again. If commited you will see the newest data. Till the transaction going on you can see the read consistent data from undo. And so, When the change is committed, the need for the past image is gone, but it’s not discarded till there is a need of undo space for other transactions.So, the concept of flashback a table is why not to use the undo to get a table to a previous stage.yes we can do that.but only till the undo is available.
and we can resize the undo also depending on how much back image of a table i want.........
iii) Rollback/restore a dropped table:
we know DDL statements connot be rollback. Means if you drop a table,that is gone. But this works only till oracle 9i.
in 10G,you can restore a table which was dropped by a ddl commands.
Check whether flashback is enabled or not in oracle 10G:
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES
If it shows no means flashback is not enabled
How to enable flashback in oracle 10G
step 1: Database should be in archive log mode.(HOW TO ENABLE /DISABLE ARCHIVE LOG)
step 2: FLASH RECOVERY AREA must be configured.(HOW TO CONFIGURE FLASH RECOVERY AREA)
step 3:
sql> conn sys/**@lion as sysdba
sql>startup mount
sql>alter database flashback on;
sql> alter database open;
Note: enabling flashback generates flashbacks logs which tracks the changes to data blocks. Flash recovery area is the only place where it stores flashback logs. So, flash recovery area must to be configured.Secondly, having only flashback logs will not help to flashback database ,it needs the archive logs to be applied to undo the blocks.so,the database must be in archive log mode.Remember one more thing, sizing the flash recovery area is very much important.
if you size it inadequetly it may end up having no space in FRA, so,database may hang,or will not startup and you may receive the following errors :(because it may be the location for your rman backup storage and archived logs also. so, no space here means you are in trouble)
Check the errors you may receive if flashback recovery area lack of space:
1) You see a message in the alert log similar to the following:
Can not open flashback thread because there is no more space in flash recovery area
2)If the database has aborted earlier because of flashback errors and you attempt to
start it, you get the following error:
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-38760: This database instance failed to turn on flashback database
we will talk about how we can solve this issues in separate discussion. Let us come back to the discussion here.
Using flashback:
HOW to flashback a DATABASE
Other available options:
What should be the size of FRA(flash recovery area)
How to disable flashback
HOW TO GET ORACLE DATABASE IDENTIFIER (DBID)
DBID stands for database identifier. Which is a unique identifier for each oracle database running.we can get the DBID from the v$database view.
sql>select DBID from v$database;
DBID
----------
396604920
so, above command will show you the unique database identifier of your database which is stored in your CONTROL files.You may need to know your Database DBID on some recovery scenarios.one example is , you may need to restore your control files from rman backup as you have lost all of your control files.There are lot of conditions apply in recovering a control file depending on the type of backup taken and the location of backup.We will discuss about this
in the control file recovery situations documentation afterward .But, while recovering the control file you may end up here:
RMAN> connect target /@lion
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
OUTPUT asking about DBID:
RMAN-06495: must explicitly specify DBID with SET DBID command
this is where this article stands firm.........
Now , you have to set DBID first then try the above recovery stunts.Now ,it becomes a challenging task as we know DBID is stored in control file which we have already lost........
So, is there a way out???
Yes, Oracle can do almost everything........
Let us check the following way to know what is my database DBID
We can get ORACLE DATABASE DBID from :
a) a rman autobackup file.(backup taken with control file autobackup on parameter)
b) we can configure alert log file to store DBID regularly
c) we can retrieve DBID from a file dump
d) we can get DBID from rman output
In detail:
a)DBID FROM CONTROL FILE AUTOBACKUP:
If you choose to take rman control file autobackup on, then mention the format parameter with %F, so that it will generate the file name as %F, this %F parameter includes the DBID in the filename.
ex: %F maintains the c-IIIIIIIIII-YYYYMMDD-QQ format .
where c indicates that it is a control file backup
IIIIIIIIII indicates DBID
YYYYMMDD indicates the date backup was created
QQ indicates a hex sequence number to make the filename unique on the same day
b)GET DBID FROM ALERT LOG FILE:
We can make sure that my database DBID is written to alert log file on regular basis with the help of a package named DBMS_SYSTEM .
To do that, include the following to your regular backup job or script:
COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));
after running this code, check your alert log now. you will get an entry like:
DBID: 396604920
c)DBID FROM A FILE DUMP:
Other way is, if any of the physical files(datafiles,logfiles,even archived log files) are available, we can extract the DBID to a trace file. More importantly, we dont need the database to be mounted for this:
example:
sql> connect /@lion as sysdba
sql>startup nomount;
sql>alter system dump datafile 'D:\lion\data\SYSTEM.DBF' block min 1 block max 10;
Now search the in the trace file generated in user_dump_dest location with the string 'Db ID'. and you will get an entry like:
Db ID=396604920=0x17a3b5f8, Db Name='LION'
we can use the same syntax to make a dump of redo log files/archived log files as :
SQL> alter system dump logfile '';
d)DBID from RMAN output:
whenever you will connect to your target database rman utility, it will show the DBID as follows on the post connection output. see the following:(for this your database should be at least mounted,when you connect RMAN)
Rman> connect target /@lion
connected to target database: LION (DBID=396604920, not open)
So, you can view your previously rman output log files to get the DBID , as you surely have executed rman scripts earlier.
Rman uses DBID to identify a database uniquely. So, in control file recovery scenario,if your backups are not in default location or you dont have recovery catalog then rman cannot have the information , that which backup piece is appropriate for restore. So, it requieres DBID.
Thanks,
have a nice day..............
sql>select DBID from v$database;
DBID
----------
396604920
so, above command will show you the unique database identifier of your database which is stored in your CONTROL files.You may need to know your Database DBID on some recovery scenarios.one example is , you may need to restore your control files from rman backup as you have lost all of your control files.There are lot of conditions apply in recovering a control file depending on the type of backup taken and the location of backup.We will discuss about this
in the control file recovery situations documentation afterward .But, while recovering the control file you may end up here:
RMAN> connect target /@lion
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
OUTPUT asking about DBID:
RMAN-06495: must explicitly specify DBID with SET DBID command
this is where this article stands firm.........
Now , you have to set DBID first then try the above recovery stunts.Now ,it becomes a challenging task as we know DBID is stored in control file which we have already lost........
So, is there a way out???
Yes, Oracle can do almost everything........
Let us check the following way to know what is my database DBID
We can get ORACLE DATABASE DBID from :
a) a rman autobackup file.(backup taken with control file autobackup on parameter)
b) we can configure alert log file to store DBID regularly
c) we can retrieve DBID from a file dump
d) we can get DBID from rman output
In detail:
a)DBID FROM CONTROL FILE AUTOBACKUP:
If you choose to take rman control file autobackup on, then mention the format parameter with %F, so that it will generate the file name as %F, this %F parameter includes the DBID in the filename.
ex: %F maintains the c-IIIIIIIIII-YYYYMMDD-QQ format .
where c indicates that it is a control file backup
IIIIIIIIII indicates DBID
YYYYMMDD indicates the date backup was created
QQ indicates a hex sequence number to make the filename unique on the same day
b)GET DBID FROM ALERT LOG FILE:
We can make sure that my database DBID is written to alert log file on regular basis with the help of a package named DBMS_SYSTEM .
To do that, include the following to your regular backup job or script:
COL dbid NEW_VALUE hold_dbid
SELECT dbid FROM v$database;
exec dbms_system.ksdwrt(2,'DBID: '||TO_CHAR(&hold_dbid));
after running this code, check your alert log now. you will get an entry like:
DBID: 396604920
c)DBID FROM A FILE DUMP:
Other way is, if any of the physical files(datafiles,logfiles,even archived log files) are available, we can extract the DBID to a trace file. More importantly, we dont need the database to be mounted for this:
example:
sql> connect /@lion as sysdba
sql>startup nomount;
sql>alter system dump datafile 'D:\lion\data\SYSTEM.DBF' block min 1 block max 10;
Now search the in the trace file generated in user_dump_dest location with the string 'Db ID'. and you will get an entry like:
Db ID=396604920=0x17a3b5f8, Db Name='LION'
we can use the same syntax to make a dump of redo log files/archived log files as :
SQL> alter system dump logfile '
d)DBID from RMAN output:
whenever you will connect to your target database rman utility, it will show the DBID as follows on the post connection output. see the following:(for this your database should be at least mounted,when you connect RMAN)
Rman> connect target /@lion
connected to target database: LION (DBID=396604920, not open)
So, you can view your previously rman output log files to get the DBID , as you surely have executed rman scripts earlier.
Rman uses DBID to identify a database uniquely. So, in control file recovery scenario,if your backups are not in default location or you dont have recovery catalog then rman cannot have the information , that which backup piece is appropriate for restore. So, it requieres DBID.
Thanks,
have a nice day..............
Tuesday, February 10, 2009
Oracle: Create database manually (in windows/linux)
Let us check a demonstration of HOW TO CREATE AN ORACLE DATABASE MANUALLY:
Note: Demonstration is on oracle 10G version. but the same will do for other versions also...almost....
step 1: let us create the location for our database:
My database name will be 'DEMO' and the location is 'd:\demo'
under d:\demo folder, i will create folder named......
d:\demo\data (will store all of my datafiles)
d:\demo\pfile (will be my pfile location)
d:\demo\adump (will be my audit_dump_dest location)
d:\demo\bdump (will be my background_dump_dest location)
d:\demo\cdump (will be my core_dump_dest location)
d:\demo\udump (will be my user_dump_dest location)
if you want you can have another folder for flash recovery area (Oracle 10G onwards)
step 2: now , let us copy a sample pfile from the existing running database and place it into "d:\demo\pfile" folder.
CHECK THE SAMPLE PFILE
In the sample file do the required changes mentioned in "BLUE" color text and save the file.
Note: remember the value of undo_tablespace parameter in pfile,as we need to provide the same name for undo_tablespace when we will create the database.
step 3: now we need to create the password file. we will use the orapwd utility for this.
you will find this utility in $ORACLE_HOME\bin directory.
so, lets proceed...
c:\ershad>orapwd file=E:\oracle\product\10.2.0\db_1\database\pwddemo.ora password=tiger entries=5
here we have created a password file named pwddemo.ora under my $oracle_home. The password is tiger and maximum 5 user will be allowed to login as sysdba.
What is a password file
Create password file with ORAPWD utility
step 4: update your listener.ora and tnsnames.ora with only the BLUE colored text here:
(copy & paste)
==>in tnsnames.ora file:
# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
tiger =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.12.242)(PORT = 1521))
)
(CONNECT_DATA =
(server=dedicated)
(SERVICE_NAME = tiger)
)
)
demo=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = YOUR HOSTNAME/IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =demo)
)
)
==>in listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba2)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = tiger)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = tiger)
)
(SID_DESC =
(GLOBAL_DBNAME = demo)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = demo)
)
)
step 5: you need to create the service:
C:\ershad>oradim -new -sid demo -pfile d:\demo\pfile\initdemo.ora -startmode A
step 6: connect to the database:
C:\ershad>set oracle_sid=demo
C:\ershad>sqlplus / as sysdba
sql> select status from v$instance;
STATUS
------------
STARTED
after connecting to database, if it shows "connect to an idle instance" , then you need to manually start up the database using the following:
sql>startup nomount pfile=d:\demo\pfile\initdemo.ora
step 7: run the create database script :
just copy the following into a file and run the file in sql prompt as follows...............
content of create_database_script file:
create database demo
logfile group 1 ('d:\demo\data\redo1.log') size 100M,
group 2 ('d:\demo\data\redo2.log') size 100M,
group 3 ('d:\demo\data\redo3.log') size 100M
datafile 'd:\demo\data\system.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile 'd:\demo\data\sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undodemo datafile 'd:\demo\data\undotbs1.dbf' size 100M
default temporary tablespace temptiger tempfile 'd:\demo\data\temp01.dbf' size 100M;
so now,
sql>@d:\create_database_script
Database created.
Now, You need to run two scripts to create the dynamic views and enable pl/sql packages
put the database in oepn mode and run the following scripts:
location of scripts are '$ORACLE_HOME\RDBMS\ADMIN'
sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql
sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql
Your manually created database is ready to use now.
HOW TO CREATE ORACLE DATABASE MANUALLY IN LINUX
perform all the steps previous except step 5. as in unix/linux ,no need to create the database service.
thanks,
have a nice day...........................
Note: Demonstration is on oracle 10G version. but the same will do for other versions also...almost....
step 1: let us create the location for our database:
My database name will be 'DEMO' and the location is 'd:\demo'
under d:\demo folder, i will create folder named......
d:\demo\data (will store all of my datafiles)
d:\demo\pfile (will be my pfile location)
d:\demo\adump (will be my audit_dump_dest location)
d:\demo\bdump (will be my background_dump_dest location)
d:\demo\cdump (will be my core_dump_dest location)
d:\demo\udump (will be my user_dump_dest location)
if you want you can have another folder for flash recovery area (Oracle 10G onwards)
step 2: now , let us copy a sample pfile from the existing running database and place it into "d:\demo\pfile" folder.
CHECK THE SAMPLE PFILE
In the sample file do the required changes mentioned in "BLUE" color text and save the file.
Note: remember the value of undo_tablespace parameter in pfile,as we need to provide the same name for undo_tablespace when we will create the database.
step 3: now we need to create the password file. we will use the orapwd utility for this.
you will find this utility in $ORACLE_HOME\bin directory.
so, lets proceed...
c:\ershad>orapwd file=E:\oracle\product\10.2.0\db_1\database\pwddemo.ora password=tiger entries=5
here we have created a password file named pwddemo.ora under my $oracle_home. The password is tiger and maximum 5 user will be allowed to login as sysdba.
What is a password file
Create password file with ORAPWD utility
step 4: update your listener.ora and tnsnames.ora with only the BLUE colored text here:
(copy & paste)
==>in tnsnames.ora file:
# tnsnames.ora Network Configuration File: E:\oracle\product\10.2.0\db_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.
tiger =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.4.12.242)(PORT = 1521))
)
(CONNECT_DATA =
(server=dedicated)
(SERVICE_NAME = tiger)
)
)
demo=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = YOUR HOSTNAME/IP)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME =demo)
)
)
==>in listener.ora file:
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = dba2)(PORT = 1521))
)
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = e:\oracle\product\10.2.0\db_1)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = tiger)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = tiger)
)
(SID_DESC =
(GLOBAL_DBNAME = demo)
(ORACLE_HOME =e:\oracle\product\10.2.0\db_1)
(SID_NAME = demo)
)
)
step 5: you need to create the service:
C:\ershad>oradim -new -sid demo -pfile d:\demo\pfile\initdemo.ora -startmode A
step 6: connect to the database:
C:\ershad>set oracle_sid=demo
C:\ershad>sqlplus / as sysdba
sql> select status from v$instance;
STATUS
------------
STARTED
after connecting to database, if it shows "connect to an idle instance" , then you need to manually start up the database using the following:
sql>startup nomount pfile=d:\demo\pfile\initdemo.ora
step 7: run the create database script :
just copy the following into a file and run the file in sql prompt as follows...............
content of create_database_script file:
create database demo
logfile group 1 ('d:\demo\data\redo1.log') size 100M,
group 2 ('d:\demo\data\redo2.log') size 100M,
group 3 ('d:\demo\data\redo3.log') size 100M
datafile 'd:\demo\data\system.dbf' size 100M autoextend on next 10M maxsize unlimited extent management local
sysaux datafile 'd:\demo\data\sysaux.dbf' size 100M autoextend on next 10M maxsize unlimited
undo tablespace undodemo datafile 'd:\demo\data\undotbs1.dbf' size 100M
default temporary tablespace temptiger tempfile 'd:\demo\data\temp01.dbf' size 100M;
so now,
sql>@d:\create_database_script
Database created.
Now, You need to run two scripts to create the dynamic views and enable pl/sql packages
put the database in oepn mode and run the following scripts:
location of scripts are '$ORACLE_HOME\RDBMS\ADMIN'
sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catalog.sql
sql> @E:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catproc.sql
Your manually created database is ready to use now.
HOW TO CREATE ORACLE DATABASE MANUALLY IN LINUX
perform all the steps previous except step 5. as in unix/linux ,no need to create the database service.
thanks,
have a nice day...........................
Oracle: A sample pfile
let us check a sample pfile here, database name is 'demo' :
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=demo
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=d:\demo\bdump
core_dump_dest=d:\demo\cdump
user_dump_dest=d:\demo\udump
###########################################
# File Configuration
###########################################
control_files=("d:\demo\data\control01.ctl", "d:\demo\data\control02.ctl", "d:\demo\data\control03.ctl")
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# SGA Memory
###########################################
sga_target=571473920
###########################################
# Security and Auditing
###########################################
audit_file_dest=d:\demo\adump
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=demoXDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=189792256
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDODEMO
thanks,
have a nice day..............
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################
###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16
###########################################
# Cursors and Library Cache
###########################################
open_cursors=300
###########################################
# Database Identification
###########################################
db_domain=""
db_name=demo
###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=d:\demo\bdump
core_dump_dest=d:\demo\cdump
user_dump_dest=d:\demo\udump
###########################################
# File Configuration
###########################################
control_files=("d:\demo\data\control01.ctl", "d:\demo\data\control02.ctl", "d:\demo\data\control03.ctl")
###########################################
# Job Queues
###########################################
job_queue_processes=10
###########################################
# Miscellaneous
###########################################
compatible=10.2.0.1.0
###########################################
# Processes and Sessions
###########################################
processes=150
###########################################
# SGA Memory
###########################################
sga_target=571473920
###########################################
# Security and Auditing
###########################################
audit_file_dest=d:\demo\adump
remote_login_passwordfile=EXCLUSIVE
###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=demoXDB)"
###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
pga_aggregate_target=189792256
###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDODEMO
thanks,
have a nice day..............
exp/imp vs expdp/impdp or datapump export/import feature or difference between datapump and original export/import
prior to oracle 10G, the logical backup was taken through EXP/IMP utility, which was the basic export/import utility till oracle 9i. Oracle has come with more powerful logical backup tool in oracle 10G. As we know about datapump export/import utility.
let us check the feature which enhances the datapump export/import over the original export/import
expdp/impdp(datapump) vs exp/imp(basic one)
1) Impdp/Expdp uses parallel execution rather than a single stream of execution. this improved the task performance a lot.
2) In Data Pump, expdp full=y and then impdp schemas=HR is same as of expdp schemas=HR and then impdp full=y where in original export/import does not always behave the same.
3) in datapump Expdp/Impdp access files on the server rather than on the client.
4) Data Pump represents metadata information in the dump file set as XML documents rather than as simple DDL commands.
5) Datapump Impdp/Expdp has self-tuning abilities. Tuning parameters that were used in original Export and Import, such as RECORDLENGTH and BUFFER are not supported by
Data Pump Export and Import.Not required is the perfect term to define.
6) Datapump Expdp/Impdp operates on a group of files called a dump file set rather than on
a single sequential dump file as exp/imp
7) Oracle data pump does not supports Sequential media, such as tapes and pipes.
8) The way of transition of data between two different versions of oracle database is not same
in expdp/impdp and exp/imp utility
9) When you are importing rows into an existing table using either APPEND or TRUNCATE
using datapump, if any row violates an active constraint, the load is discontinued and no data is loaded.
Original Import will logs any rows that are in violation and continues the load process.
10) Datapump expdp/impdp generates more undo than expdp/impdp
Hope the information are useful....
thanks,
have a nice day.............
let us check the feature which enhances the datapump export/import over the original export/import
expdp/impdp(datapump) vs exp/imp(basic one)
1) Impdp/Expdp uses parallel execution rather than a single stream of execution. this improved the task performance a lot.
2) In Data Pump, expdp full=y and then impdp schemas=HR is same as of expdp schemas=HR and then impdp full=y where in original export/import does not always behave the same.
3) in datapump Expdp/Impdp access files on the server rather than on the client.
4) Data Pump represents metadata information in the dump file set as XML documents rather than as simple DDL commands.
5) Datapump Impdp/Expdp has self-tuning abilities. Tuning parameters that were used in original Export and Import, such as RECORDLENGTH and BUFFER are not supported by
Data Pump Export and Import.Not required is the perfect term to define.
6) Datapump Expdp/Impdp operates on a group of files called a dump file set rather than on
a single sequential dump file as exp/imp
7) Oracle data pump does not supports Sequential media, such as tapes and pipes.
8) The way of transition of data between two different versions of oracle database is not same
in expdp/impdp and exp/imp utility
9) When you are importing rows into an existing table using either APPEND or TRUNCATE
using datapump, if any row violates an active constraint, the load is discontinued and no data is loaded.
Original Import will logs any rows that are in violation and continues the load process.
10) Datapump expdp/impdp generates more undo than expdp/impdp
Hope the information are useful....
thanks,
have a nice day.............
Sunday, February 8, 2009
Oracle:rman output to a file/V$rman_output/V$rman_status
When using rman utility , you may often need to store the rman activity log in a file. And normally we use various rman backup and recovery scripts on a scheduled time to run . so, we need to refer to log to find everything was ok at the scheduled time as i want.
Although , whenever you run rman commands , it will automatically log the detail output of rman activity in a data dictionary file named as V$rman_output.
sql> select * from v$rman_output;
but as it is a dynamic view , so the contents will be washed out on restart of the database.
so, a log file is safe to store the output of rman.let us check the following demonstration:
RMAN> spool log to 'c:\rman.log';
RMAN> backup database;
RMAN> spool off;
RMAN>
Whatever statements you have issued in between spool and spool off will have there output stored in the destination file you have mentioned.As you see there is no output of backup database command on the screen.
Let us check the log file now:
c:\>notepad 'c:\rman.log'
Spooling started in log file: c:\rman.log
Recovery Manager10.2.0.1.0
RMAN>
Starting backup at 08-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\LION\DATA\SYSTEM.DBF
input datafile fno=00002 name=D:\LION\DATA\UNDOTBS1.DBF
input datafile fno=00003 name=D:\LION\DATA\SYSAUX.DBF
input datafile fno=00004 name=C:\DATA01.DBF
input datafile fno=00005 name=C:\INDEX01.DBF
channel ORA_DISK_1: starting piece 1 at 08-FEB-09
channel ORA_DISK_1: finished piece 1 at 08-FEB-09
piece handle=E:\LION\BACKUPSET\2009_02_08\O1_MF_NNNDF_TAG20090208T150901_4RX89GOZ_.BKP tag=TAG20090208T150901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-FEB-09
Starting Control File and SPFILE Autobackup at 08-FEB-09
piece handle=E:\LION\AUTOBACKUP\2009_02_08\O1_MF_S_678294567_4RX8B8MK_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-FEB-09
RMAN>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "off": expecting one of: "log, msglog, trace"
RMAN-01007: at line 1 column 7 file: standard input
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
RMAN-01007: at line 1 column 10 file: standard input
so, hope it is ok
remember one thing , if you run the same procedure again, it will overwrite the same file.
to avoid this situation try the following:
RMAN> spool log to 'c:\rman.log' append;
RMAN> backup database;
RMAN> spool off;
RMAN>
If you are running a rman script or cmdfile then also in the file you can do the same.
or you have another option like:
rman> rman target /@lion cmdfile c:\rman.txt log c:\rman.log
to avoid overwriting ................
rman> rman target /@lion cmdfile c:\rman.txt log c:\rman.log append
Note: You can check the job status of rman by quering the V$rman_status view also....
Thanks,
Have a nice day............................
Although , whenever you run rman commands , it will automatically log the detail output of rman activity in a data dictionary file named as V$rman_output.
sql> select * from v$rman_output;
but as it is a dynamic view , so the contents will be washed out on restart of the database.
so, a log file is safe to store the output of rman.let us check the following demonstration:
RMAN> spool log to 'c:\rman.log';
RMAN> backup database;
RMAN> spool off;
RMAN>
Whatever statements you have issued in between spool and spool off will have there output stored in the destination file you have mentioned.As you see there is no output of backup database command on the screen.
Let us check the log file now:
c:\>notepad 'c:\rman.log'
Spooling started in log file: c:\rman.log
Recovery Manager10.2.0.1.0
RMAN>
Starting backup at 08-FEB-09
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=D:\LION\DATA\SYSTEM.DBF
input datafile fno=00002 name=D:\LION\DATA\UNDOTBS1.DBF
input datafile fno=00003 name=D:\LION\DATA\SYSAUX.DBF
input datafile fno=00004 name=C:\DATA01.DBF
input datafile fno=00005 name=C:\INDEX01.DBF
channel ORA_DISK_1: starting piece 1 at 08-FEB-09
channel ORA_DISK_1: finished piece 1 at 08-FEB-09
piece handle=E:\LION\BACKUPSET\2009_02_08\O1_MF_NNNDF_TAG20090208T150901_4RX89GOZ_.BKP tag=TAG20090208T150901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
Finished backup at 08-FEB-09
Starting Control File and SPFILE Autobackup at 08-FEB-09
piece handle=E:\LION\AUTOBACKUP\2009_02_08\O1_MF_S_678294567_4RX8B8MK_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 08-FEB-09
RMAN>
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "off": expecting one of: "log, msglog, trace"
RMAN-01007: at line 1 column 7 file: standard input
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found ";": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, convert, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, flashback, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, switch, spool, startup, shutdown, send, show, test, transport, upgrade, unregister, validate"
RMAN-01007: at line 1 column 10 file: standard input
so, hope it is ok
remember one thing , if you run the same procedure again, it will overwrite the same file.
to avoid this situation try the following:
RMAN> spool log to 'c:\rman.log' append;
RMAN> backup database;
RMAN> spool off;
RMAN>
If you are running a rman script or cmdfile then also in the file you can do the same.
or you have another option like:
rman> rman target /@lion cmdfile c:\rman.txt log c:\rman.log
to avoid overwriting ................
rman> rman target /@lion cmdfile c:\rman.txt log c:\rman.log append
Note: You can check the job status of rman by quering the V$rman_status view also....
Thanks,
Have a nice day............................
Oracle:Configure flash recovery area
in Oracle 10G, a major feature is flash recovery area.Flash recovery area contributes as a central repository for rman to store backups,archive logs. Even oracle recommends to keep your manual backups,archive logs,redo files to keep in FRA. We can keep control files also to this location.
Let us check what are the effective way to configure FRA(login as sysdba user):
step 1: To configure Flash recovery area , first you need to think about:
i) what will be the location
ii) what should be the size
step 2:
sql>show parameter log_archive_dest
sql>show parameter log_archive_duplex_dest
or
sql>show parameter log_archive_dest_
(check for the values for 1 to 10)
If this parameters have a value set then, you need to remove this values.
Normally we may have either log_archive_dest+log_archive_duplex_dest or log_archive_dest_1 to 10 set as archive location. Both combination are not allowed together.
any of these parameters , need to be removed to configure flash recovery area
Note: You may have this values configured as your archive log location, we will discuss about this point later
to remove the values:
sql> alter system set log_archive_duplex_dest = ' ' scope=both;
sql> alter system set log_archive_dest = ' ' scope=both;
for log_archive_dest_1 to 10 follow the same to remove the values.
step 3: Issue the following command:
sql>alter system set db_recovery_file_dest_size = 8G scope=both;
sql>alter system set db_recovery_file_dest = 'e:\oracle\flasharea' scope=both;
Note: the directory "e:\oracle\flasharea" should exists .
Now, the note earlier about archive log location.
if you have log_archive_dest as your archive log location , then change it to log_archive_dest_1 to store archive log.
check this ,
earlier location for archive log:
SQL>show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest string e:\archive
now change the value:
sql> alter system set log_archive_dest = ' ' scope=both;
then,
SQL> alter system set log_archive_dest_1 = 'location=e:\archive';
Note that , With flash recovery area configured we can keep the archive log in that location(as of oracle recommendation), though if you want above is the solution for using flash recovery area as well as different archive locations for archiving redo logs.
Thanks,
Have a nice day....................
Let us check what are the effective way to configure FRA(login as sysdba user):
step 1: To configure Flash recovery area , first you need to think about:
i) what will be the location
ii) what should be the size
step 2:
sql>show parameter log_archive_dest
sql>show parameter log_archive_duplex_dest
or
sql>show parameter log_archive_dest_
(check for the values for 1 to 10)
If this parameters have a value set then, you need to remove this values.
Normally we may have either log_archive_dest+log_archive_duplex_dest or log_archive_dest_1 to 10 set as archive location. Both combination are not allowed together.
any of these parameters , need to be removed to configure flash recovery area
Note: You may have this values configured as your archive log location, we will discuss about this point later
to remove the values:
sql> alter system set log_archive_duplex_dest = ' ' scope=both;
sql> alter system set log_archive_dest = ' ' scope=both;
for log_archive_dest_1 to 10 follow the same to remove the values.
step 3: Issue the following command:
sql>alter system set db_recovery_file_dest_size = 8G scope=both;
sql>alter system set db_recovery_file_dest = 'e:\oracle\flasharea' scope=both;
Note: the directory "e:\oracle\flasharea" should exists .
Now, the note earlier about archive log location.
if you have log_archive_dest as your archive log location , then change it to log_archive_dest_1 to store archive log.
check this ,
earlier location for archive log:
SQL>show parameter log_archive_dest
NAME TYPE VALUE
------------------------------------ ----------- ----------------------------
log_archive_dest string e:\archive
now change the value:
sql> alter system set log_archive_dest = ' ' scope=both;
then,
SQL> alter system set log_archive_dest_1 = 'location=e:\archive';
Note that , With flash recovery area configured we can keep the archive log in that location(as of oracle recommendation), though if you want above is the solution for using flash recovery area as well as different archive locations for archiving redo logs.
Thanks,
Have a nice day....................
Oracle: foreign key syntax
let us check the syntax of foreign key with various options:
step 1) first we will create a primary key that will be referred in foreign key constraints:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);
Table created
step 2) We will configure the foreign key now.
a) foreign key with create table option
sys@LION> create table ersh_foreign_tab
(id number(10) not null,
email varchar2(32),
constraint emp_id_fk foreign key (id) references ersh_tab(emp_id)
);
Table created
b) foreign key with alter table option
sys@LION>ALTER TABLE ersh_foreign_tab
add CONSTRAINT emp_id_fk
FOREIGN KEY (id) REFERENCES ersh_tab(emp_id);
Table altered
c) Composite primary key with create table:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id,emp_name)
);
composite foreign key with create table option
sys@LION> create table ersh_foreign_tab
(id number(10) not null,
email varchar2(32),
constraint emp_id_fk foreign key (id,email) references ersh_tab(emp_id,email)
);
Thanks,
Have a nice day.................
step 1) first we will create a primary key that will be referred in foreign key constraints:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);
Table created
step 2) We will configure the foreign key now.
a) foreign key with create table option
sys@LION> create table ersh_foreign_tab
(id number(10) not null,
email varchar2(32),
constraint emp_id_fk foreign key (id) references ersh_tab(emp_id)
);
Table created
b) foreign key with alter table option
sys@LION>ALTER TABLE ersh_foreign_tab
add CONSTRAINT emp_id_fk
FOREIGN KEY (id) REFERENCES ersh_tab(emp_id);
Table altered
c) Composite primary key with create table:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id,emp_name)
);
composite foreign key with create table option
sys@LION> create table ersh_foreign_tab
(id number(10) not null,
email varchar2(32),
constraint emp_id_fk foreign key (id,email) references ersh_tab(emp_id,email)
);
Thanks,
Have a nice day.................
Oracle: Rman show command parameters
to check the rman configuration settings, SHOW is very important . there is a lot of parameter that show supports. let us check one by one:
==>RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFLION.ORA'; # default
show all will show all the rman configuration settings which include both the default settings as well as what you have configured . You can identify the default settings by "# default" at the end of every line.
==>RMAN> show controlfile autobackup;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
shows whether control file will be backup up automatically after any backup operation by rman.default value is OFF.
==>RMAN> show archivelog deletion policy;
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
shows the archive log detention policy.
==>RMAN> show archivelog backup copies;
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
shows the number of archivelog backup copies
==>RMAN> show auxname;
RMAN configuration parameters are:
RMAN configuration has no stored or default parameters
shows the auxiliary database information.
==>RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default
shows whether backup optimization is on or off.
==>RMAN> show controlfile autobackup format;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
shows what will be the format of auto backed up control file .
==>RMAN> show datafile backup copies;
RMAN configuration parameters are:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
shows how many datafile backup copies will be kept by rman
==>RMAN> show default device type;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
shows whether the default device type is disk or tape
==>RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
Shows policy for datafile and control file backups and copies that RMAN marks as obsolete.
==>RMAN> show encryption algorithm;
RMAN configuration parameters are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
shows which encryption algorithm is currently is use.
==>RMAN> show encryption for database;[or for tablespace]
RMAN configuration parameters are:
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
shows the encryption for the database or every tablespace.
More:
show exclude; (will show which tablespaces are excluded from backup)
show maxsetsize; (shows the max size of backup set. the default is unlimited)
show snapshot controlfile name; (shows the snapshot control file name)
show compression algorithm; (shows compression algorithm . default is ZLIB)
show channel for device type disk; [device type sbt](shows the channel for device type)
NOTE: If you have changed the default value of any rman parameters , you can get the parameter name and the values from v$rman_configuration view .
ex:
step 1:
RMAN> show controlfile autobackup;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
so, it is off now.and it is default
step 2:
sys@LION> select * from v$rman_configuration;
no rows selected
step 3:
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
Changing the default value off to on.
step 4:
sys@LION> select * from v$rman_configuration;
CONF# NAME VALUE
---------- ------------------------------ ------------------------------
1 CONTROLFILE AUTOBACKUP ON
So , hope it is clear now.
Thanks,
have a nice day....
==>RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\SNCFLION.ORA'; # default
show all will show all the rman configuration settings which include both the default settings as well as what you have configured . You can identify the default settings by "# default" at the end of every line.
==>RMAN> show controlfile autobackup;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
shows whether control file will be backup up automatically after any backup operation by rman.default value is OFF.
==>RMAN> show archivelog deletion policy;
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
shows the archive log detention policy.
==>RMAN> show archivelog backup copies;
RMAN configuration parameters are:
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
shows the number of archivelog backup copies
==>RMAN> show auxname;
RMAN configuration parameters are:
RMAN configuration has no stored or default parameters
shows the auxiliary database information.
==>RMAN> show backup optimization;
RMAN configuration parameters are:
CONFIGURE BACKUP OPTIMIZATION OFF; # default
shows whether backup optimization is on or off.
==>RMAN> show controlfile autobackup format;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
shows what will be the format of auto backed up control file .
==>RMAN> show datafile backup copies;
RMAN configuration parameters are:
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
shows how many datafile backup copies will be kept by rman
==>RMAN> show default device type;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
shows whether the default device type is disk or tape
==>RMAN> show retention policy;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
Shows policy for datafile and control file backups and copies that RMAN marks as obsolete.
==>RMAN> show encryption algorithm;
RMAN configuration parameters are:
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
shows which encryption algorithm is currently is use.
==>RMAN> show encryption for database;[or for tablespace]
RMAN configuration parameters are:
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
shows the encryption for the database or every tablespace.
More:
show exclude; (will show which tablespaces are excluded from backup)
show maxsetsize; (shows the max size of backup set. the default is unlimited)
show snapshot controlfile name; (shows the snapshot control file name)
show compression algorithm; (shows compression algorithm . default is ZLIB)
show channel for device type disk; [device type sbt](shows the channel for device type)
NOTE: If you have changed the default value of any rman parameters , you can get the parameter name and the values from v$rman_configuration view .
ex:
step 1:
RMAN> show controlfile autobackup;
RMAN configuration parameters are:
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
so, it is off now.and it is default
step 2:
sys@LION> select * from v$rman_configuration;
no rows selected
step 3:
RMAN> configure controlfile autobackup on;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
Changing the default value off to on.
step 4:
sys@LION> select * from v$rman_configuration;
CONF# NAME VALUE
---------- ------------------------------ ------------------------------
1 CONTROLFILE AUTOBACKUP ON
So , hope it is clear now.
Thanks,
have a nice day....
Saturday, February 7, 2009
ORA-02273: this unique/primary key is referenced by some foreign keys
Problem occurs because you are trying to drop a primary constraints which has a dependent foreign key exist.
let us check the demonstration:
i will create a table with primary key:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);
Now let us make a foreign key relationship depending on this table:
sys@LION> create table opt (id number(30),emp_mail varchar2(50),constraint opt_id_fk foreign key(id) references ersh_tab(emp_id));
Now i will try to drop the primary key constraints, which is emp_id_pk:
sys@LION> alter table ersh_tab drop constraint emp_id_pk;
alter table ersh_tab drop constraint emp_id_pk * ERROR at line 1: ORA-02273: this unique/primary key is referenced by some foreign keys
solution is :
first drop the foreign key constraint then the primary one :
sys@LION> alter table opt drop constraint opt_id_fk;
then,
sys@LION> alter table ersh_tab drop constraints emp_id_pk;
or to do it in one shot , you can try the following:
sys@LION> alter table ersh_tab drop constraints emp_id_pk cascade;
it will drop the dependent constraints also automatically.
Note : you can get the dependency information from dba_constraints/user_constraints/all_constraints view .
MORE ABOUT PRIMARY KEY
thanks,
Have a nice day .............
let us check the demonstration:
i will create a table with primary key:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);
Now let us make a foreign key relationship depending on this table:
sys@LION> create table opt (id number(30),emp_mail varchar2(50),constraint opt_id_fk foreign key(id) references ersh_tab(emp_id));
Now i will try to drop the primary key constraints, which is emp_id_pk:
sys@LION> alter table ersh_tab drop constraint emp_id_pk;
alter table ersh_tab drop constraint emp_id_pk * ERROR at line 1: ORA-02273: this unique/primary key is referenced by some foreign keys
solution is :
first drop the foreign key constraint then the primary one :
sys@LION> alter table opt drop constraint opt_id_fk;
then,
sys@LION> alter table ersh_tab drop constraints emp_id_pk;
or to do it in one shot , you can try the following:
sys@LION> alter table ersh_tab drop constraints emp_id_pk cascade;
it will drop the dependent constraints also automatically.
Note : you can get the dependency information from dba_constraints/user_constraints/all_constraints view .
MORE ABOUT PRIMARY KEY
thanks,
Have a nice day .............
He who has never learned to obey
cannot be a good commander.
Aristotle
ORA-02297: cannot disable primary key constraint - dependencies exist
These happens if you want to disable a primary key constraints when the child foreign key exits
let us see the demonstration:
i will create a table with primary key:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);
Now let us make a foreign key relationship depending on this table:
sys@LION> create table opt (id number(30),emp_mail varchar2(50),constraint opt_id_fk foreign key(id) references ersh_tab(emp_id));
Now i will try to disable the primary key constraint , which is ersh_id_pk:
sys@LION> alter table ersh_tab disable constraints emp_id_pk;
alter table ersh_tab disable constraints emp_id_pk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SYS.EMP_ID_PK) - dependencies exist
You cannot disable a parent key constraints if the foreign key exist. If you want to do so , you need to first disable or drop that foreign key constraint first.
sys@LION> alter table opt disable constraint opt_id_fk;
Table altered.
then disable the primary key
sys@LION> alter table ersh_tab disable constraints emp_id_pk;
Table altered.
you can get the dependency information from dba_constraints/user_constraints/all_constraints view .
thanks ,
have a nice day ..............
To find what you seek in the road of life,
the best proverb of all is that which says:
"Leave no stone unturned."
Edward Bulwer Lytton
let us see the demonstration:
i will create a table with primary key:
sys@LION>CREATE TABLE ersh_tab
( emp_id number(10) not null,
emp_name varchar2(50) not null,
address varchar2(50),
CONSTRAINT emp_id_pk PRIMARY KEY (emp_id)
);
Now let us make a foreign key relationship depending on this table:
sys@LION> create table opt (id number(30),emp_mail varchar2(50),constraint opt_id_fk foreign key(id) references ersh_tab(emp_id));
Now i will try to disable the primary key constraint , which is ersh_id_pk:
sys@LION> alter table ersh_tab disable constraints emp_id_pk;
alter table ersh_tab disable constraints emp_id_pk
*
ERROR at line 1:
ORA-02297: cannot disable constraint (SYS.EMP_ID_PK) - dependencies exist
You cannot disable a parent key constraints if the foreign key exist. If you want to do so , you need to first disable or drop that foreign key constraint first.
sys@LION> alter table opt disable constraint opt_id_fk;
Table altered.
then disable the primary key
sys@LION> alter table ersh_tab disable constraints emp_id_pk;
Table altered.
you can get the dependency information from dba_constraints/user_constraints/all_constraints view .
thanks ,
have a nice day ..............
To find what you seek in the road of life,
the best proverb of all is that which says:
"Leave no stone unturned."
Edward Bulwer Lytton
Subscribe to:
Posts (Atom)