Is it possible to create an unique deferrable constraint enforced by an unique index? No, it's impossible! You can create an unique constraint with 'deferrable' state. You can create an unique constraint enforced by an unique index. But you can't have both.
Below statement will create an unique constraint enforced by an unique index by default.
21:36:13 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:36:36 2 (charge_element_id, ce_det_version_no, display_order) using index;
Table altered.
This will create an unique constraint with 'deferrable' state enforced by an non-unique index by default.
21:39:29 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:39:40 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index;
Table altered.
You need to drop the constraint and the index separately.
You will get an error when trying to add an unique constraint with 'deferrable' state enforced by an existing unique index.
21:40:14 SQL> create unique index p_pced_uk on p_product_ce_determinants
21:40:44 2 (charge_element_id, ce_det_version_no, display_order);
Index created.
Elapsed: 00:00:00.03
21:40:49 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:40:57 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index p_pced_uk;
alter table p_product_ce_determinants add constraint p_pced_uk unique
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.
Elapsed: 00:00:00.06
However, if you add the constraint solely enforced by the unique index, the statement will succeed.
21:41:08 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:41:23 2 (charge_element_id, ce_det_version_no, display_order) using index p_pced_uk;
Table altered.
Elapsed: 00:00:00.01
You still need to drop the constraint and the index separately.
Instead the statement below will succeed and an unique constraint with 'deferrable' state will be created as enforced by an existing non-unique index.
21:42:17 SQL> create index p_pced_uk on p_product_ce_determinants
21:42:28 2 (charge_element_id, ce_det_version_no, display_order);
Index created.
Elapsed: 00:00:00.03
21:42:33 SQL> alter table p_product_ce_determinants add constraint p_pced_uk unique
21:42:37 2 (charge_element_id, ce_det_version_no, display_order) deferrable using index p_pced_uk;
Table altered.
Elapsed: 00:00:00.03
I tested in both 9i and 10g. While they have the same behavior, 10g gives more clear messages than 9i.
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Wednesday, February 28, 2007
Thursday, February 22, 2007
How to catch an user defined error message
Recently I am working on a project. In this project, there is a database package in which I put all the related functions and procedures. I want a function to generate user defined errors while calling it directly in the hosting environment; meanwhile, I want the calling stored procedure to ignore some of the user generated errors if the function is being called by a stored procedure. The point here is to catch some user defined errors but not the oracle predefined errors.
There is a RAISE_APPLICATION_ERROR statement in the function to generate the user defined error. e.g.
function test
return number
is
e_no_rate_found exception;
begin
raise e_no_rate_found;
exception
when e_no_rate_found then
raise_application_error(-20306, 'No rate found!');
end;
If this function is called directly from sqlplus, the user defined error -20306 will be raised. I want to ignore this error in a procedure which calls this function. Actually, it's same to catch an user defined error as to catch an oracle predefined error. Here we go. In the procedure, we could write the code block below:
declare
e_no_rate_found exception;
pragma exception_init(e_no_rate_found, -20306);
begin
call test;
exception
when e_no_rate_found then
NULL;
end;
The user defined error -20306 is associated with the user defined exception e_no_rate_found using exception_init pragma in the calling procedure.
There is a RAISE_APPLICATION_ERROR statement in the function to generate the user defined error. e.g.
function test
return number
is
e_no_rate_found exception;
begin
raise e_no_rate_found;
exception
when e_no_rate_found then
raise_application_error(-20306, 'No rate found!');
end;
If this function is called directly from sqlplus, the user defined error -20306 will be raised. I want to ignore this error in a procedure which calls this function. Actually, it's same to catch an user defined error as to catch an oracle predefined error. Here we go. In the procedure, we could write the code block below:
declare
e_no_rate_found exception;
pragma exception_init(e_no_rate_found, -20306);
begin
call test;
exception
when e_no_rate_found then
NULL;
end;
The user defined error -20306 is associated with the user defined exception e_no_rate_found using exception_init pragma in the calling procedure.
Solaris utility which can be used to connect to MS SQLServer
Sometimes we need to access MS SQLServer from Solaris platform. In many cases, we need to get the data out of MS SQLServer and import it into Oracle. I wonder if there is any utility on Solaris which can do the similar things to what isql(the interactive SQL client comes with MS SQLServer) can do on Windows platform. Here come two resources:
Tuesday, January 30, 2007
Interesting things during housekeeping a database
These days I've been housekeeping a database. Some tablespaces were created inefficiently which all have unnecessary huge extent size 16MB. As a result, even most of segments in those tablespaces have only few rows, they still occupy at least 16MB. Since there are a lot of such segments, the database has grown unnecessarily big. My job is to move these segments out of the tablespaces and shrink their size.
case when bytes/1048576 <>
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'TABLE'
union
select 'alter table '||owner||'.'||segment_name||' move partition '||
partition_name||' tablespace ts_big;'
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'TABLE PARTITION'
order by 1;
case when bytes/1048576 <>
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'INDEX'
union
select 'alter index '||owner||'.'||segment_name||' rebuild partition '||
partition_name||' tablespace ts_big_idx nologging;'
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'INDEX PARTITION'
order by 1;
from dba_segments
where tablespace_name = 'ts_small'
and segment_type = 'TABLE'
and initial_extent != 128*1024 and initial_extent = bytes
order by initial_extent;
Some interesting things show up during deallocating the segments:
- Move small tables to the small-extent 128KB tablespaces. Move big tables and table partitions to the big-extent 16MB tablespaces. Moving tables must be done at first as the rows movement will invalidate all the associated indexes.
case when bytes/1048576 <>
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'TABLE'
union
select 'alter table '||owner||'.'||segment_name||' move partition '||
partition_name||' tablespace ts_big;'
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'TABLE PARTITION'
order by 1;
- Rebuild small indexes into the small-extent 128KB tablespaces. Rebuild big indexes and index partitions into the big-extent 1MB tablespaces.
case when bytes/1048576 <>
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'INDEX'
union
select 'alter index '||owner||'.'||segment_name||' rebuild partition '||
partition_name||' tablespace ts_big_idx nologging;'
from dba_segments
where tablespace_name = 'ts_unnecessary_big' and segment_type = 'INDEX PARTITION'
order by 1;
- Even the tablespaces into which those segments are being moved are created as locally managed and have uniformly small extents, the segments being moved into them will be created with a big initial extent which equals to their original size. After moving the segments, I need to deallocate the unused blocks and shrink their initial extent size. Firstly, I need to find out which segments are candidates to be deallocated. Probably whose initial extents are larger than the tablespace initial extent and the initial extent size is equal to it's segment size.
from dba_segments
where tablespace_name = 'ts_small'
and segment_type = 'TABLE'
and initial_extent != 128*1024 and initial_extent = bytes
order by initial_extent;
Some interesting things show up during deallocating the segments:
- Some segments can be deallocated down to 128k, the tablespace initial extent size; some can NOT even their high watermark is not above 128k.
- Some segments can be deallocated down to the nearest size to the high watermark; some can NOT.
- Some statements will tell you how many unused blocks there are above the segment high watermark while some won't.
- After more aggressively changing 'keep 128k' to 'keep 64k' or down to another level, some segments which are not able to be shrank to 128k mentioned in point 1 have been deallocated to 128k.
- After more aggressively changing 'keep 128k' to 'keep 64k' or down to another level, some segments which are not able to be shrank to their possibly smallest size in point 2 have been deallocated more.
Forward or backward?
Recently I was assigned to a project. Actually, the project itself is pretty simple in terms of business logic. There are only 4 screens which need to be maintained on the front-end web pages. The maintenance is also pretty straightforward. Users just want to insert, update, delete or get a report on the data. What about the data? The data itself is relatively static as well. Till now, everybody would think this project is easy to be done and the data would be easy to be manipulated. I could think it's enough to have up to 4 or 5 tables out there in the database.
Wrong! The data model designed by somebody is extremely complicated. There are tens of tables in the database to support his idea and backup the front-end interface. This guy seems to live in a perfect world. He wants everything to be flexible. He wants users to control which table columns to be displayed on the web interface. He wants everything to be stored as their IDs rather than their real values. To find the real value, I need to come down 3 or more tables to fetch it. I could think even the simplest SQL needs to join 5 or more tables to get what we want. This guy wants to build a RDBMS system instead of an application on top of the Oracle RDBMS!
Yes, he can say the model is very flexible. Users can control almost everything even the data type and some constraints. But the price we pay here is a hard-to-understand data model(sometimes even the designer himself has no idea what he's doing during project meetings, funny!), the inevitable database performance overhead and the unnecessary flexibility. Users may not want those flexibilities we introduce to them at all! Actually, He complicates things completely!
I've been thinking about this. The database technology has been emerging for decades. From the layered database to the networking database to the relational database to the object-oriented database. One of the most important goals is to simplify the data model as much as possible so that developers can focus on the real business logic instead of the complicated data model. We do ours and you do yours! In this project, everything seems inversed! Instead of putting our effort to enforcing the business logic, we have to spend 90% of the project time to understand the data model, manipulate the data to stay consistent with one another. Is it really worth doing all of these? Are we going forward or backward? Time will tell!
Wrong! The data model designed by somebody is extremely complicated. There are tens of tables in the database to support his idea and backup the front-end interface. This guy seems to live in a perfect world. He wants everything to be flexible. He wants users to control which table columns to be displayed on the web interface. He wants everything to be stored as their IDs rather than their real values. To find the real value, I need to come down 3 or more tables to fetch it. I could think even the simplest SQL needs to join 5 or more tables to get what we want. This guy wants to build a RDBMS system instead of an application on top of the Oracle RDBMS!
Yes, he can say the model is very flexible. Users can control almost everything even the data type and some constraints. But the price we pay here is a hard-to-understand data model(sometimes even the designer himself has no idea what he's doing during project meetings, funny!), the inevitable database performance overhead and the unnecessary flexibility. Users may not want those flexibilities we introduce to them at all! Actually, He complicates things completely!
I've been thinking about this. The database technology has been emerging for decades. From the layered database to the networking database to the relational database to the object-oriented database. One of the most important goals is to simplify the data model as much as possible so that developers can focus on the real business logic instead of the complicated data model. We do ours and you do yours! In this project, everything seems inversed! Instead of putting our effort to enforcing the business logic, we have to spend 90% of the project time to understand the data model, manipulate the data to stay consistent with one another. Is it really worth doing all of these? Are we going forward or backward? Time will tell!
Friday, November 11, 2005
Database duplication using RMAN
Scenario 1:
Starting on Nov. 3, 2005
Duplicate ARBORBP without a password file and SQLNET connection, after restoring a full backup taken on Oct. 14, 2005, RMAN errors below appear
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS===============
RMAN-00571:===========================================================
RMAN-03002: failure of Duplicate Db command at 11/04/2005 06:51:49
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01990: error opening password file '/dg04/vol01/app/oracle/product/9.2/dbs/orapw'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Conclusion:
A password file must be created before using RMAN to duplicate a database
Scenario 2:
Starting on Nov. 4, 2005
Creating a password file for the auxiliary instance TEST
Add entry for TEST to listener.ora and tnsnames.ora as well
Duplicate ARBORBP without specifying UNTIL clause, lasting for around 17 hours, after applying the incremental backup taken on Oct. 31, 2005, RMAN errors below appear
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/05/2005 14:33:05
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 44833 scn 572637893394 found to restore
RMAN-06025: no backup of log thread 1 seq 44832 scn 572637892189 found to restore
RMAN-06025: no backup of log thread 1 seq 44831 scn 572637891001 found to restore
RMAN-06025: no backup of log thread 1 seq 44830 scn 572637889406 found to restore
.
.
.
Conclusion:
This is a known problem of DUPLICATE command refers to Oracle Metalik Doc. 274118.1. You must make sure all the archived log required to clone a database have been backed up. UntilNov. 7, 2005, only the archived logs prior to 44635 have been backed up but not all. This caused this try to fail.
Scenario 3:
Starting on Nov. 8, 2005
Specify the UNTIL clause 'sysdate - 2', date back to Nov. 6, 2005
Since there is another incremental backup on Nov. 7, 2005, it's expected all the necessary archived log have already been backed up
On Nov. 4, 2005, there is a new tablespace BCS2004 added into the DB, hence a new datafile. At the first try, there is no entry for the newly added datafile in RMAN command file. The below error appears.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2005 10:31:08
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename /dg02/vol04/oradata/arborbp/bcs2004_01.dbf conflicts with a file used by the target database
At the second try, an entry below added into RMAN command file to reflect this change.
SET NEWNAME FOR DATAFILE 27 TO '/dg02/vol01/oradata/restore/bcs2004_01.dbf';
However, still get the error below
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2005 11:09:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 27 found to restore
Finally, the file entry is removed from the RMAN command file and a SKIP TABLESPACE claus is added to skip the newly added tablespace BCS2004. Moreover, to specify the point in time more accurately, using UNTIL SEQUENCE instead of TIME. A sequence number 44965 of the last archive log which has been backed up to tape on Nov. 7, 2005 has been specified. Without restoring the incremental backup taken on Nov. 7, 2005, the incremental backup taken on Oct. 31, 2005 has been applied. Afterwards, Oracle began to applying the archived log from 44635 which is generated right after finishing the incremental backup on Oct. 31, 2005. After applying around 200 archived logs without any problem, the below erros appear while encountering the log 44835.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2005 05:16:51
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/dg02/vol01/oradata/restore/arch/arch_1
_44835.arc'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 27: '/dg02/vol04/oradata/arborbp/bcs2004_01.dbf'
The log 44835 was produced on Nov. 4, 2005. The newly added datafile was recorded in it.
Conclusion:
You must use a full set of backups to duplicate a database. From the time when the latest inremental 0 backup is taken to a point in time you want the duplicating database recovered to, there should NOT be any structural change like adding datafile.
Scenario 4:
Starting on Nov. 10, 2005
Still no entry for the newly added file and SKIP TABLESPACE BCS2004, this time the archived log 44640 is specified in SET UNTIL clause. This should meet all the requirements to duplicate a database. All the necessary logs have been backed up to tape; from the last incremental level 0 backup taken on Oct. 14, 2005 to the archived log 44640, no datafile has been added. There are 2 incremental level 1 backup taken on Oct. 24, 2005 and Oct. 31, 2005 respectively and a few number of archived logs between 44635 and 44640 to be applied.
Without luck, got errors below again!
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2005 11:32:07
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 44635 scn 572614439208 found to restore
After looking into the case, the root cause of this error is because we are using CONTROLFILE of the target database rather than the RMAN catalog. Due to the limited space in the controlfile, after finishing the incremental level 1 backup taken on Nov. 7, 2005, all the archived log backed up on Oct. 31, 2005 have been flushed out of the controlfile. Since 44635 is the last one, there is no trace of it.
RMAN> list backup of archivelog all completed before '31-oct-2005';
RMAN> exit
However, all the archived logs from 44636 do exist in the control file since they have been backed up on Nov. 7, 2005 in the inremental level 1 backup.
Finally things turn out to be a little simple! According to the Metalink Doc. 274118.1, I manually restored the required archived logs between 44635 and 44640 in the production database ARBORBP by connecting to the RMAN catalog. I then transferred them to the auxiliary box to be applied to the auxiliary instance. The rest of work is pretty straightforward! Using SQL*Plus to recover the auxiliary instance and open it with RESETLOGS option. I didn't bring the instance to 44640 since it's not necessary. I only brought it to 44638, a consistent state!
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.5.0 - Production on Fri Nov 11 12:17:08 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> recover database using backup controlfile;
ORA-00279: change 572614439988 generated at 10/31/2005 17:28:33 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44635.arc
ORA-00280: change 572614439988 for thread 1 is in sequence #44635
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 572614473618 generated at 10/31/2005 20:09:22 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44636.arc
ORA-00280: change 572614473618 for thread 1 is in sequence #44636
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44635.arc' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
cacel
ORA-00308: cannot open archived log 'cacel'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
SQL> recover database using backup controlfile;
ORA-00279: change 572614473618 generated at 10/31/2005 20:09:22 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44636.arc
ORA-00280: change 572614473618 for thread 1 is in sequence #44636
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 572614824171 generated at 11/01/2005 06:06:40 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44637.arc
ORA-00280: change 572614824171 for thread 1 is in sequence #44637
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44636.arc' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 572614824171 generated at 11/01/2005 06:06:40 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44637.arc
ORA-00280: change 572614824171 for thread 1 is in sequence #44637
Specify log: {=suggested | filename | AUTO | CANCEL}
ORA-00279: change 572614825088 generated at 11/01/2005 06:07:50 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44638.arc
ORA-00280: change 572614825088 for thread 1 is in sequence #44638
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44637.arc' no
longer needed for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Starting on Nov. 3, 2005
Duplicate ARBORBP without a password file and SQLNET connection, after restoring a full backup taken on Oct. 14, 2005, RMAN errors below appear
RMAN-00571:===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS===============
RMAN-00571:===========================================================
RMAN-03002: failure of Duplicate Db command at 11/04/2005 06:51:49
RMAN-06136: ORACLE error from auxiliary database: ORA-01503: CREATE CONTROLFILE failed
ORA-01990: error opening password file '/dg04/vol01/app/oracle/product/9.2/dbs/orapw'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Conclusion:
A password file must be created before using RMAN to duplicate a database
Scenario 2:
Starting on Nov. 4, 2005
Creating a password file for the auxiliary instance TEST
Add entry for TEST to listener.ora and tnsnames.ora as well
Duplicate ARBORBP without specifying UNTIL clause, lasting for around 17 hours, after applying the incremental backup taken on Oct. 31, 2005, RMAN errors below appear
Oracle Error:
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/05/2005 14:33:05
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 44833 scn 572637893394 found to restore
RMAN-06025: no backup of log thread 1 seq 44832 scn 572637892189 found to restore
RMAN-06025: no backup of log thread 1 seq 44831 scn 572637891001 found to restore
RMAN-06025: no backup of log thread 1 seq 44830 scn 572637889406 found to restore
.
.
.
Conclusion:
This is a known problem of DUPLICATE command refers to Oracle Metalik Doc. 274118.1. You must make sure all the archived log required to clone a database have been backed up. UntilNov. 7, 2005, only the archived logs prior to 44635 have been backed up but not all. This caused this try to fail.
Scenario 3:
Starting on Nov. 8, 2005
Specify the UNTIL clause 'sysdate - 2', date back to Nov. 6, 2005
Since there is another incremental backup on Nov. 7, 2005, it's expected all the necessary archived log have already been backed up
On Nov. 4, 2005, there is a new tablespace BCS2004 added into the DB, hence a new datafile. At the first try, there is no entry for the newly added datafile in RMAN command file. The below error appears.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2005 10:31:08
RMAN-05501: aborting duplication of target database
RMAN-05001: auxiliary filename /dg02/vol04/oradata/arborbp/bcs2004_01.dbf conflicts with a file used by the target database
At the second try, an entry below added into RMAN command file to reflect this change.
SET NEWNAME FOR DATAFILE 27 TO '/dg02/vol01/oradata/restore/bcs2004_01.dbf';
However, still get the error below
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/08/2005 11:09:38
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 27 found to restore
Finally, the file entry is removed from the RMAN command file and a SKIP TABLESPACE claus is added to skip the newly added tablespace BCS2004. Moreover, to specify the point in time more accurately, using UNTIL SEQUENCE instead of TIME. A sequence number 44965 of the last archive log which has been backed up to tape on Nov. 7, 2005 has been specified. Without restoring the incremental backup taken on Nov. 7, 2005, the incremental backup taken on Oct. 31, 2005 has been applied. Afterwards, Oracle began to applying the archived log from 44635 which is generated right after finishing the incremental backup on Oct. 31, 2005. After applying around 200 archived logs without any problem, the below erros appear while encountering the log 44835.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/10/2005 05:16:51
RMAN-03015: error occurred in stored script Memory Script
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/dg02/vol01/oradata/restore/arch/arch_1
_44835.arc'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 27: '/dg02/vol04/oradata/arborbp/bcs2004_01.dbf'
The log 44835 was produced on Nov. 4, 2005. The newly added datafile was recorded in it.
Conclusion:
You must use a full set of backups to duplicate a database. From the time when the latest inremental 0 backup is taken to a point in time you want the duplicating database recovered to, there should NOT be any structural change like adding datafile.
Scenario 4:
Starting on Nov. 10, 2005
Still no entry for the newly added file and SKIP TABLESPACE BCS2004, this time the archived log 44640 is specified in SET UNTIL clause. This should meet all the requirements to duplicate a database. All the necessary logs have been backed up to tape; from the last incremental level 0 backup taken on Oct. 14, 2005 to the archived log 44640, no datafile has been added. There are 2 incremental level 1 backup taken on Oct. 24, 2005 and Oct. 31, 2005 respectively and a few number of archived logs between 44635 and 44640 to be applied.
Without luck, got errors below again!
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/11/2005 11:32:07
RMAN-03015: error occurred in stored script Memory Script
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 44635 scn 572614439208 found to restore
After looking into the case, the root cause of this error is because we are using CONTROLFILE of the target database rather than the RMAN catalog. Due to the limited space in the controlfile, after finishing the incremental level 1 backup taken on Nov. 7, 2005, all the archived log backed up on Oct. 31, 2005 have been flushed out of the controlfile. Since 44635 is the last one, there is no trace of it.
RMAN> list backup of archivelog all completed before '31-oct-2005';
RMAN> exit
However, all the archived logs from 44636 do exist in the control file since they have been backed up on Nov. 7, 2005 in the inremental level 1 backup.
Finally things turn out to be a little simple! According to the Metalink Doc. 274118.1, I manually restored the required archived logs between 44635 and 44640 in the production database ARBORBP by connecting to the RMAN catalog. I then transferred them to the auxiliary box to be applied to the auxiliary instance. The rest of work is pretty straightforward! Using SQL*Plus to recover the auxiliary instance and open it with RESETLOGS option. I didn't bring the instance to 44640 since it's not necessary. I only brought it to 44638, a consistent state!
$ sqlplus /nolog
SQL*Plus: Release 9.2.0.5.0 - Production on Fri Nov 11 12:17:08 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> connect / as sysdba
Connected.
SQL> recover database using backup controlfile;
ORA-00279: change 572614439988 generated at 10/31/2005 17:28:33 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44635.arc
ORA-00280: change 572614439988 for thread 1 is in sequence #44635
Specify log: {
ORA-00279: change 572614473618 generated at 10/31/2005 20:09:22 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44636.arc
ORA-00280: change 572614473618 for thread 1 is in sequence #44636
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44635.arc' no
longer needed for this recovery
Specify log: {
cacel
ORA-00308: cannot open archived log 'cacel'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
Specify log: {
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
SQL> recover database using backup controlfile;
ORA-00279: change 572614473618 generated at 10/31/2005 20:09:22 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44636.arc
ORA-00280: change 572614473618 for thread 1 is in sequence #44636
Specify log: {
ORA-00279: change 572614824171 generated at 11/01/2005 06:06:40 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44637.arc
ORA-00280: change 572614824171 for thread 1 is in sequence #44637
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44636.arc' no
longer needed for this recovery
Specify log: {
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/dg02/vol01/oradata/restore/system_01.dbf'
SQL> recover database until cancel using backup controlfile;
ORA-00279: change 572614824171 generated at 11/01/2005 06:06:40 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44637.arc
ORA-00280: change 572614824171 for thread 1 is in sequence #44637
Specify log: {
ORA-00279: change 572614825088 generated at 11/01/2005 06:07:50 needed for
thread 1
ORA-00289: suggestion : /dg02/vol01/oradata/restore/arch/arch_1_44638.arc
ORA-00280: change 572614825088 for thread 1 is in sequence #44638
ORA-00278: log file '/dg02/vol01/oradata/restore/arch/arch_1_44637.arc' no
longer needed for this recovery
Specify log: {
CANCEL
Media recovery cancelled.
SQL> alter database open resetlogs;
Database altered.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Friday, October 21, 2005
Learn Something New Every Day: Easy Connect Identifier
Prior to Oracle 10g, after creating a new database and configuring the listener, to connect to the DB from other box, you must first either create a TNSNAMES.ORA file in your local box or set up an entry for the DB in some name services (Oracle NAMES or LDAP). Sometimes it's annoying. Since Oracle 10g Release 1, you don't have to do this any more. Just like using thin JDBC to connect to a DB by providing host, port and SID, you can now easily connect to a DB via SQLNET by providing host, port and sevice name without any extra configuration.
Pretty cool! Below are from Oracle SQL*Plus Documentation
Pretty cool! Below are from Oracle SQL*Plus Documentation
Easy Connection Identifier
The easy or abbreviated connection identifier has the syntax:
[//]host[:port][/[service_name]]
Example 4–4 Start a command-line session to the sales database using the easy connection identifier
sqlplus hr/password@sales-server:1521/sales.us.acme.com
Example 4–5 CONNECT to the sales database using the easy connection identifier
connect hr/password@sales-server:1521/sales.us.acme.com
The easy connection identifier can be used wherever you can use a full connection identifier, or a net service name. The easy syntax is less complex, and no tnsnames.ora entry is required.
However, through some tests, I found out you still have to put the below entry into SQLNET.ORA file. Otherwise, you will keep receiving ORA-12154 error either using 'sqlplus' or 'connect'. It's very frustrating!
NAMES.DIRECTORY_PATH= (EZCONNECT, TNSNAMES)
After adding the EZCONNECT entry, I tried the command line 'sqlplus' many times but without success. Below are some output. Still trying ... :-(
swong@sun:nemo > sqlplus sysman@"mars:1521/flyhorse.domain"
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:27:33 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name: ^C
swong@sun:nemo > sqlplus sysman@'mars:1521/flyhorse.domain'
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:27:46 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name: ^C
swong@sun:nemo > sqlplus sysman@'//mars:1521/flyhorse.domain'
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:27:53 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Usage: SQLPLUS [ [] [] [ ] ]
where ::= -H | -V | [ [-C] [-L] [-M ] [-R ] [-S] ]
::= [/ ][@ ] | / | /NOLOG
::= @ | [. ] [ ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-C" sets SQL*Plus compatibility version
"-L" attempts log on just once
"-M" uses HTML markup options
"-R" uses restricted mode
"-S" uses silent mode
swong@sun:nemo > sqlplus sysman@"//mars:1521/flyhorse.domain"
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:28:00 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Usage: SQLPLUS [ [] [] [ ] ]
where ::= -H | -V | [ [-C] [-L] [-M ] [-R ] [-S] ]
::= [/ ][@ ] | / | /NOLOG
::= @ | [. ] [ ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-C" sets SQL*Plus compatibility version
"-L" attempts log on just once
"-M" uses HTML markup options
"-R" uses restricted mode
"-S" uses silent mode
swong@sun:nemo > sqlplus sysman@//mars:1521/flyhorse.domain
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:28:10 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Usage: SQLPLUS [ [] [] [ ] ]
where ::= -H | -V | [ [-C] [-L] [-M ] [-R ] [-S] ]
::= [/ ][@ ] | / | /NOLOG
::= @ | [. ] [ ...]
"-H" displays the SQL*Plus version banner and usage syntax
"-V" displays the SQL*Plus version banner
"-C" sets SQL*Plus compatibility version
"-L" attempts log on just once
"-M" uses HTML markup options
"-R" uses restricted mode
"-S" uses silent mode
swong@sun:nemo > sqlplus sysman@mars:1521/flyhorse.domain
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:28:14 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor
Enter user-name: ^C
I worked out another way to bypass this problem, that's using 'connect'. You need to enclose the connection identifier with quote marks! Here are some tests including both success and failure. Here we go!
swong@sun:nemo > sqlplus /nolog
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:08:30 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
11:08:31 connect sysman@"//mars:1521/flyhorse.domain"
Enter password:
Connected.
11:08:48 sysman@flyhorse> connect sysman@'//mars:1521/flyhorse.domain'
Enter password:
Connected.
11:09:03 sysman@flyhorse> connect sysman@//mars:1521/flyhorse.domain
SP2-0306: Invalid option.
Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
where::= [/ ][@ ] | /
11:09:16 sysman@flyhorse> connect sysman@mars:1521/flyhorse.domain
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
Warning: You are no longer connected to ORACLE.
11:09:28 sysman@flyhorse> connect sysman@"mars:1521/flyhorse.domain"
Enter password:
Connected.
11:10:33 sysman@flyhorse> connect sysman@'mars:1521/flyhorse.domain'
Enter password:
Connected.
Guess what? You still have to enclose the connection identifier with quote marks while using the command line 'sqlplus' no matter you use the double slashes or not. Slash doesn't matter but the quote marks really matter here! The only difference from 'connect' is you need an extra step, that's to escape the quote marks! It's Oracle to interpret the quote marks rather than Solaris (I am working on Solaris). So you need to pass the connection identifier with the quote marks to Oracle. Below are some samples.
swong@sun:nemo > sqlplus sysman@\"mars:1521/flyhorse.domain\"
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:39:22 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
11:39:27 sysman@flyhorse> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
swong@sun:nemo > sqlplus sysman@\'mars:1521/flyhorse.domain\'
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:39:51 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
11:39:53 sysman@flyhorse> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
swong@sun:nemo > sqlplus sysman@\"//mars:1521/flyhorse.domain\"
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:40:35 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
11:40:37 sysman@flyhorse> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
swong@sun:nemo > sqlplus sysman@\'//mars:1521/flyhorse.domain\'
SQL*Plus: Release 10.1.0.2.0 - Production on Fri Oct 21 11:40:55 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
11:40:58 sysman@flyhorse> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
This connection mechamism can also be used to create a database link.
17:17:11 ops$swong@FLYHORSE> create database link report@user
17:17:41 2 connect to user identified by password
17:17:48 3 using 'mybox.com:1521/testdb.com';
Database link created.
Elapsed: 00:00:00.36
Subscribe to:
Posts (Atom)
