Oracle DB and beyond: practical tips, Q&A

Tuesday, 3 December 2013

How to choose the sequence file number to use for restore/duplicate RMAN command?

Connect to the RMAN respositor:

rman target /

List the available backups; choose the latest sequence log number and add one when you run the restore/duplicate DB command.
In the example below, I've used 15628+1

RMAN>list backup;

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
282 302.50K DISK 00:00:00 03-DEC-2013 13:53:14
BP Key: 284 Status: AVAILABLE Compressed: YES Tag: ENV_52_DB_BKP
Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_287.rman

List of Archived Logs in backup set 282
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 15625 10218583388335 03-DEC-2013 13:35:02 10218583388844 03-DEC-2013 13:45:02
1 15626 10218583388844 03-DEC-2013 13:45:02 10218583389173 03-DEC-2013 13:53:13
1 15627 10218583389173 03-DEC-2013 13:53:13 10218583389183 03-DEC-2013 13:53:13

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
283 Full 4.47G DISK 00:14:43 03-DEC-2013 14:08:01
BP Key: 285 Status: AVAILABLE Compressed: YES Tag: ENV_52_DB_BKP
Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_288.rman
List of Datafiles in backup set 283
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- -------------------- ----
1 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/system_TESTDB11_01.dbf
2 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/sysaux_TESTDB11_01.dbf
3 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/undotbs_TESTDB11_01.dbf
4 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/tools_TESTDB11_01.dbf
9 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluapp52_TESTDB11_01.dbf
28 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluref52_TESTDB11_01.dbf
47 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluopr52_TESTDB11_01.dbf
67 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluref52f_TESTDB11_01.dbf
70 Full 10218583389193 03-DEC-2013 13:53:18 /TESTDB11/ora_data00/bmluapp52f_TESTDB11_01.dbf

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
284 Full 1.33M DISK 00:00:01 03-DEC-2013 14:08:04
BP Key: 286 Status: AVAILABLE Compressed: YES Tag: ENV_52_DB_BKP
Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_289.rman
Control File Included: Ckp SCN: 10218583390120 Ckp time: 03-DEC-2013 14:08:03

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ --------------------
285 1.38M DISK 00:00:00 03-DEC-2013 14:08:05
BP Key: 287 Status: AVAILABLE Compressed: YES Tag: ENV_52_DB_BKP
Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/TESTDB11_UAT52_TBS_DB_BKUP_13:52:58-12-03-2013_1_290.rman

List of Archived Logs in backup set 285
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- -------------------- ---------- ---------
1 15628 10218583389183 03-DEC-2013 13:53:13 10218583390129 03-DEC-2013 14:08:05

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ --------------------
286 Full 1.33M DISK 00:00:01 03-DEC-2013 14:08:08
BP Key: 288 Status: AVAILABLE Compressed: YES Tag: TAG20131203T140807
Piece Name: /TESTDB11/ora_data02/RMAN_backup/TESTDB11/ctl/CTLBKP_TESTDB11_13:52:58-12-03-2013.CTL
Control File Included: Ckp SCN: 10218583390148 Ckp time: 03-DEC-2013 14:08:07

The duplicate command looks like:

RUN {
SET NEWNAME FOR DATAFILE 1 TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/sysaux.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/tools01.dbf';
SET NEWNAME FOR DATAFILE 9 TO '/testdb11/ora_data02/RMAN_backup/auxiliary_dest/dbf/bmluapp52.dbf';
set until sequence 15629 thread 1;
DUPLICATE TARGET DATABASE TO AUXU
TABLESPACE test52,TOOLS
LOGFILE
GROUP 1 ('/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo01a.log',
'/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo01b.log') SIZE 200M reuse,
GROUP 2 ('/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo02a.log',
'/testdb11/ora_data02/RMAN_backup/auxiliary_dest/redo/redo02b.log') SIZE 200M reuse;

}

Wednesday, 27 November 2013

Oracle analytical functions to the rescue

Below is a typical example of how using analytical functions speeds up a query, which is having a subquery inside.

Original query:

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
ALT_MODEL_DESC
FROM TEST1, TEST2, TEST3
WHERE TEST1.CUSTOMER_ID = 517091995
AND TEST1.UNIT_ESN = ESN
AND TEST1.ESN_SEQ_NO =
(SELECT MAX (ESN_SEQ_NO)
FROM TEST1 PD1
WHERE PD1.CUSTOMER_ID = TEST1.CUSTOMER_ID
AND PD1.SUBSCRIBER_NO = TEST1.SUBSCRIBER_NO
AND LENGTH(UNIT_ESN) <> 20 )
AND TEST2.TEST3 = TEST3.TEST3
AND ( TEST3.EXPIRATION_DATE IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE )

;

The new query, using analytical functions (much faster and accesing the TEST1 table only once):

SELECT
DISTINCT CUSTOMER_ID, SUBSCRIBER_NO, TEST3.TEST3, MODEL_DESC,
ALT_MODEL_DESC
FROM
(select UNIT_ESN,ESN_SEQ_NO,CUSTOMER_ID, SUBSCRIBER_NO, max(ESN_SEQ_NO) over (partition by CUSTOMER_ID,SUBSCRIBER_NO) as max_ESN_SEQ_NO
from TEST1 where LENGTH(UNIT_ESN) <> 20 ) phd, TEST2, TEST3
WHERE phd.CUSTOMER_ID = 517091995
and ESN_SEQ_NO=phd.max_ESN_SEQ_NO
AND UNIT_ESN = ESN
AND TEST2.TEST3 = TEST3.TEST3
AND ( TEST3.EXPIRATION_DATE IS NULL OR TEST3.EXPIRATION_DATE >= SYSDATE)
;

Tuesday, 26 November 2013

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

I was getting this error while running the RMAN duplicate database in 11g. As I was duplicating only selected tablespaces over, oracle internally was trying to drop the unnecesary tablespaces and the whole duplicate process was failing with as below:

sql statement: drop tablespace "TEST1" including contents cascade constraints
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 11/26/2013 13:06:34
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on clone_default channel at 11/26/2013 13:06:34
RMAN-11003: failure during parse/execution of SQL statement: drop tablespace "TEST1" including contents cascade constraints
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

RMAN> **end-of-file**

I was trying manually to drop the tablespace and I was getting the same error, but no queue tables whatsoever reside in this tablespace:

SQL> drop tablespace "TEST1" including contents cascade constraints;
drop tablespace "TEST1" including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables

SQL> select * from dba_queue_tables where table_name in
(select segment_name from dba_segments where tablespace_name='TEST1';

no rows selected

At this point, my goal was to find out which statement is failing exactly.

Solution:

SQL> ALTER system SET EVENTS='24005 TRACE NAME ERRORSTACK FOREVER, LEVEL 1';

System altered.

I then checked the alert log file for ORA-24005 and then inside the trace file I've found the exact statement failing:

ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
----- Current SQL Statement for this session (sql_id=0000000000000) -----
drop table "florin"."DDD#DEF$_AQERROR" cascade constraints purge

In conclusion, oracle treats a table name with special characters as a queue table.
I was able to drop this table, using DBMS_AQADM.DROP_QUEUE_TABLE, indeed, and then I've dropped the tablespace without any issue.

Sunday, 13 October 2013

How to find out fast the partitioning keys in tables or indexes?

The key in this question is the word "fast"; there are many ways to find the partitioning key for a table or an index: using dbms_metadata.get_ddl, export/import using indexfile, ussing expdp and impdp, but the fastest way is to query the views below:

DBA_PART_KEY_COLUMNS
DBA_SUBPART_KEY_COLUMNS
USER_PART_KEY_COLUMNS
USER_SUBPART_KEY_COLUMNS
ALL_PART_KEY_COLUMNS
ALL_SUBPART_KEY_COLUMNS

They provide info for subpartitions also.

Example:

SQL> select * from DBA_PART_KEY_COLUMNS

2 where owner='CUSTO'

3 and NAME='TRB1_AUDIT_SUB_LOG'

4 order by COLUMN_POSITION;

OWNER NAME OBJEC COLUMN_NAME COLUMN_POSITION

------------------------------ ------------------------------ ----- ------------------------------ ---------------

CUSTO MY_AUDIT_LOG TABLE PERIOD_KEY 1

CUSTO MY_AUDIT_LOG TABLE SOURCE_MEMBER_ID 2

CUSTO MY_AUDIT_LOG TABLE SUB_APPL_ID 3

CUSTO MY_AUDIT_LOG TABLE PROCESS_TYPE_IND 4

Monday, 7 October 2013

Oracle Optimizer and the evergreen need for hints

The oracle optimizer is a software, trying to generate the best execution plan for a query, with the information it has. So in theory, if we provide it the most info we can, it should always be able to generate the best execution plan. But does it? Not always.
Even if we provide the optimizer fresh statistics of all the tables involved, there is still a risk that the optimizer
will take poor decisions, since it does not know how many rows will be returned by a join and it has to estimate the
result. Sometimes, wrongly estimating that a 2 table join will return a very small amount of rows, and this result being
used as the driving table in a Nested Loop join, can seriously hinder performance.

The solution in this particular case is to use the use_hash hint, to avoid the NL altogether.

Saturday, 5 October 2013

GATHER_PLAN_STATISTICS hint and dbms_xplan

In order to get detailed information about how many rows are being returned in each step of the execution plan, the hint GATHER_PLAN_STATISTICS comes in very handy, see the simple example below:

SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.ename = 'FRANK';

You actually have to run the above query, to be able to see that run stats.

SET LINE 200 pages 80

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(format => 'ALLSTATS LAST'));

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 8 |
| 1 | NESTED LOOPS | | 1 | | 1 |00:00:00.01 | 9 |
| 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:00.01 | 8 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 |00:00:00.01 | 6 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 1 | 1 |00:00:00.01 | 1 |
--------------------------------------------------------------------------------------------------

The output above shows the estimated rows returned (E-Rows), the actual rows returned (A-Rows) and the actual time (A-Time) spent during each step of the execution.

Thursday, 12 September 2013

tar: Error exit delayed from previous errors

Another Linux related post: I was trying to tar up the oracle software and I was getting a strange message at the end of the tar output:

>tar -cvf 11.2.0.tar 11.2.0

11.2.0/
11.2.0/timingframework/
11.2.0/timingframework/TimingFramework.jar
11.2.0/timingframework/README
11.2.0/OPatch/
11.2.0/OPatch/opatchprereqs/
11.2.0/OPatch/opatchprereqs/prerequisite.properties
11.2.0/OPatch/opatchprereqs/opatch/
11.2.0/OPatch/opatchprereqs/opatch/runtime_prereq.xml

-----

tar: Error exit delayed from previous errors

The challenge here is to find out the root cause of this general error message.

Solution:

Run the tar command again, while redirecting the error output to a log file:

tar -cvf 11.2.0.tar 11.2.0 2> tar.log

In my case, the content of the log file solved the "mystery":

tar: 11.2.0/bin/nmhs: Cannot open: Permission denied

tar: 11.2.0/bin/nmb: Cannot open: Permission denied

tar: 11.2.0/bin/nmo: Cannot open: Permission denied

tar: Error exit delayed from previous errors