Test case: flashback table

scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t(a int);

Table created.

scott@TEST10G> insert into t values(1);

1 row created.

scott@TEST10G> insert into t values(2);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select sysdate from dual;

SYSDATE
-------------------
2008-11-16 13:23:44


scott@TEST10G> insert into t values(3);

1 row created.

scott@TEST10G> insert into t values(4);

1 row created.

scott@TEST10G> commit;

Commit complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2
         3
         4


scott@TEST10G> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

scott@TEST10G> alter table t enable row movement; 

Table altered.

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');

Flashback complete.

scott@TEST10G> select * from t;

         A
----------
         1
         2



Must enable row movment:

scott@TEST10G> alter table t disable row movement;

Table altered.

scott@TEST10G>
scott@TEST10G> select table_name, row_movement from user_tables where table_name='T';

TABLE_NAME                     ROW_MOVE
------------------------------ --------
T                              DISABLED

7 rows selected.

scott@TEST10G> select * from t;

         A
----------
         1
         2

scott@TEST10G> flashback table t to timestamp to_date('2008-11-16 13:23:44');
flashback table t to timestamp to_date('2008-11-16 13:23:44')
                *
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled


scott@TEST10G>

Note: to flashback to 15 min ago, we can use the following syntax:

SQL> flashback table hr.employees, hr.departments
to timestamp systimestamp - interval '15' minute;

Trace an Oracle Scheduler Job

Got a request to trace a scheduler job. I did the following:

1. Find the pl/sql block this job is doing:

select JOB_ACTION from dba_scheduler_jobs where job_name='XXXX_MOBLAL01ANK_CORR_JOB';

JOB_ACTION
----------------------------------------------------------------------------------
BEGIN XXXX_CORRELATION('MOBLAL01ANK', 10000, 60); END;


2. Disable this job after it is stopped. (login as job owner)

begin
dbms_scheduler.disable(user||'.XXXX_MOBLAL01ANK_CORR_JOB');
end;
/


3. Trace a sql session:

alter session set tracefile_identifier ='XXXX';
alter session set timed_statistics=true;
alter session set events '10046 trace name context forever, level 12';

BEGIN
XXXX_CORRELATION('MOBLAL01ANK', 10000, 60);
END;
/
exit;


4. Enable this job after trace is done

begin
dbms_scheduler.enable(user||'.XXXX_MOBLAL01ANK_CORR_JOB');
end;
/

5. Issue the following query to check the job state:
select state from dba_scheduler_jobs where job_name='XXXX_MOBLAL01ANK_CORR_JOB';

STATE
---------------
SCHEDULED

Troubleshooting - ORA-02049: timeout: distributed transaction waiting for lock

The following steps/observations are involved in solving this issue:

1. Application team asked DBA for help to solve the following error:

We are connecting to NCAMASTERCON/xxxxx@PRDDB1

   SQL> update au1011
     2  set rerate_request_cd = '5',dl_service_code = 'FXLSA', sys_update_date = sysdate
     3  where ban in (select ban from TEMP_LSAFF_US1011)
     4  and record_type = '1';
   commit;
   update au1011
   *
   ERROR at line 1:
   ORA-02049: timeout: distributed transaction waiting for lock
   ORA-02063: preceding line from NCAUSAGECON.PRDDB2

2. I found out that au1011 on PRDDB1 is a synonym for an object on a remote database PRDDB2

The synonym definition is as follows:
CREATE OR REPLACE SYNONYM "NCAMASTERCON"."AU1011" FOR "NCAUSAGECON"."AU1011"@"NCAUSAGECON.PRDDB2";


3. Then, I found out on PRDDB2 the "AU1011" is also a synonym, it is defined as:
CREATE OR REPLACE SYNONYM "NCAUSAGECON"."AU1011" FOR "NCAUSAGEOWN"."ACCUMULATED_USAGE_10_B";

4. Checked if there are any locks on the table: ACCUMULATED_USAGE_10_B

   system@PRDDB2> @l2/locked_object_by_name.sql
   Enter value for object_name: ACCUMULATED_USAGE_10_B
   Lock Lock                                                   Time       Object
      SID Type Held   Owner            Object Name                    Held (min) Status
   ------ ---- ------ ---------------- ------------------------------ ---------- ------------
     2278 DML  Row-X  NCAUSAGEOWN      ACCUMULATED_USAGE_10_B               1622 VALID

5. The SQL running by session 2278 is:

   system@PRDDB2> @sql_cur_sid
   Enter value for sid: 2278

   SQL_TEXT                                 SQL_ID           SID USER_NAME

   ---------------------------------------- ------------- ------ ------------
   UPDATE /*+ PARALLEL ("A1",4) */ "NCAUSAG 93vbp0jv2c7w4   2278 NCAUSAGECON
   ECON"."AU1011" "A1" SET "RERATE_REQUEST_
   CD" = '5',"DL_SERVICE_CODE" = 'FXLSA',"S
   YS_UPDATE_DATE" = SYSDATE@! WHERE ("A1".
   "BAN","A1"."SUBSCRIBER_NO")=ANY (SELECT
   DISTINCT "A2"."BAN","A1"."SUBSCRIBER_NO"
    FROM "TEMP_LSAFF_US1011"@! "A2") AND "A
   1"."RECORD_TYPE"='1'

6. Further check revealed that session 2278 on PRDDB2 was started 97784 seconds ago
and the last call it made was 97773 seconds ago


7. The application DBA observed the following:
The session (sid=7265) shows as KILLED on PRDDB1, but the corresponding remote session on PRDDB2(sid=2278) is still active.

8. Confirmed by application team, we killed the session 2278 on PRDDB2

Disable a scheduler job

To disable a schedular job, if it is running, it may be necessary to stop it before it can be disabled.

Below is an exmaple that I did today for a production change request:
( changed the real database name)

BV_DATA@mydbname> select owner, job_name, state from dba_scheduler_jobs where job_name like 'ANYNAME_%';

OWNER                JOB_NAME                       STATE
-------------------- ------------------------------ ---------------
BV_DATA              ANYNAME_TAMQFL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL01ANK_CORR_JOB     RUNNING
BV_DATA              ANYNAME_LPTPPA01ANK_CORR_JOB     DISABLED

4 rows selected.

BV_DATA@mydbname> exec dbms_scheduler.stop_job ('ANYNAME_MOBLAL01ANK_CORR_JOB');

PL/SQL procedure successfully completed.

BV_DATA@mydbname> exec dbms_scheduler.disable(user||'.ANYNAME_LPTPPA01ANK_CORR_JOB',true);

PL/SQL procedure successfully completed.

BV_DATA@mydbname> select owner, job_name, state from dba_scheduler_jobs where job_name like 'ANYNAME_%';

OWNER                JOB_NAME                       STATE
-------------------- ------------------------------ ---------------
BV_DATA              ANYNAME_TAMQFL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL02ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_MOBLAL01ANK_CORR_JOB     DISABLED
BV_DATA              ANYNAME_LPTPPA01ANK_CORR_JOB     DISABLED

AWK script to analyze statspack report

I have written a small awk script to help to analyze statspack report. The content of the script is as follows:

.

The content of the script is as follows:

$ cat printload.awk
{
  if ( $0 ~ /Load Profile/ ) {
     print "===>"  FILENAME
     print $0
     getline
     print $0
     getline
     print $0
     getline
     print $0
     getline
     print $0
   }
}


Here is an example of its usage:

$ awk -f printload.awk *_1114*.lst

===>sp_P1ROCCS_1114_00:00_01:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -530,710.25        -39,825,381.33
              Logical reads:              5,346.96            401,244.58
===>sp_P1ROCCS_1114_01:00_02:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            661,687.44         62,668,765.37
              Logical reads:              5,337.23            505,492.05
===>sp_P1ROCCS_1114_04:00_05:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -261,353.97            -16,558.50
              Logical reads:             34,913.84              2,212.02
===>sp_P1ROCCS_1114_05:00_06:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            679,480.45            635,181.85
              Logical reads:              9,648.79              9,019.74
===>sp_P1ROCCS_1114_06:00_07:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -531,582.88        -11,387,897.48
              Logical reads:              7,055.82            151,154.21
===>sp_P1ROCCS_1114_07:00_08:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            632,586.85         26,531,869.35
              Logical reads:              6,800.79            285,237.83
===>sp_P1ROCCS_1114_08:00_09:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:           -535,177.88        -18,857,150.16
              Logical reads:              6,451.35            227,315.36
===>sp_P1ROCCS_1114_09:00_10:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            657,449.22         15,364,673.58
              Logical reads:              5,490.71            128,318.66
===>sp_P1ROCCS_1114_10:00_11:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            542,321.35            648,839.10
              Logical reads:             14,599.49             17,466.99
===>sp_P1ROCCS_1114_11:00_12:03.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            268,331.93            319,353.99
              Logical reads:             19,272.63             22,937.23
===>sp_P1ROCCS_1114_12:03_13:07.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            316,435.43            342,552.49
              Logical reads:             22,151.92             23,980.23
===>sp_P1ROCCS_1114_14:00_15:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            149,285.73            391,643.98
              Logical reads:              6,620.19             17,367.75
===>sp_P1ROCCS_1114_15:00_16:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            158,509.15            256,437.01
              Logical reads:              7,857.87             12,712.51
===>sp_P1ROCCS_1114_16:00_16:58.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            197,935.84            243,677.31
              Logical reads:             10,099.65             12,433.60
===>sp_P1ROCCS_1114_16:58_17:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            799,700.92            948,216.80
              Logical reads:             37,202.29             44,111.29
===>sp_P1ROCCS_1114_17:00_18:00.lst
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:            541,252.15            441,183.58
              Logical reads:             26,538.76             21,632.18

I know under cygwin I can use grep -A option to achieve same goal.
I can do something as follows:
grep -A5 "Load Profile" *_1114*.lst

Note:
-A NUM, --after-context=NUM
Print NUM lines of trailing context after matching lines. Places a line
containing -- between contiguous groups of matches.

However, the version of grep on our server does not have this option.

startup open restrict

Not familar with this command before. Did a test as follows:

sys@TEST10G> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@TEST10G> startup open restrict
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                  1250428 bytes
Variable Size             163580804 bytes
Database Buffers          440401920 bytes
Redo Buffers                7135232 bytes
Database mounted.
Database opened.

sys@TEST10G> select logins from v$instance;

LOGINS
----------
RESTRICTED

sys@TEST10G> alter system disable restricted session;

System altered.

sys@TEST10G> select logins from v$instance;

LOGINS
----------
ALLOWED

Copy optimizer table statistics between different schema and databases

This test case is to simulate the situation in which we need to copy a table stats from one schema to anohter schema in a different database.

Summary of Steps:
1. Set up the initial tables and collect stats
2. Export table stats using dbms_stats package in the source database and schema
3. Exp the stats table from source database
4. Imp the stats table to the target database and target schema
5. Import table stats using dbms_stats package in target database

Note: Pay attention to the neccessity of updating c5 in the stats tab to replace source schema name with target schema name.


1. set up the inital tables
Note: Table t is in hr, we want to copy its stats to table t in scott

scott@TEST10G> @1_setup
scott@TEST10G> connect hr/hr;
Connected.
hr@TEST10G>
hr@TEST10G> drop table t;

Table dropped.

hr@TEST10G>
hr@TEST10G> create table t
  2  as
  3  select * from all_objects;

Table created.

hr@TEST10G> exec dbms_stats.gather_table_stats(user, 'T');

PL/SQL procedure successfully completed.

hr@TEST10G>
hr@TEST10G> grant select on t to scott;

Grant succeeded.

hr@TEST10G>
hr@TEST10G> connect scott/tiger;
Connected.
scott@TEST10G> drop table t;

Table dropped.

scott@TEST10G> create table t
  2  as
  3  select * from hr.t;

Table created.

2. Export table stats

hr@TEST10G> conn hr/hr
Connected.
hr@TEST10G>
hr@TEST10G> drop table t_stats;

Table dropped.

hr@TEST10G> exec dbms_stats.create_stat_table(user, 'T_STATS');

PL/SQL procedure successfully completed.

hr@TEST10G> exec dbms_stats.export_table_stats(user, 'T', stattab => 'T_STATS');

PL/SQL procedure successfully completed.

3. Exp the stats table: T_STATS

exp hr/hr tables=t_stats file=t_stats.dmp log=3_exp_t_stats.log

$ 3_exp_t_stats.sh

Export: Release 10.2.0.1.0 - Production on Mon Nov 10 18:51:38 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        T_STATS         14 rows exported
Export terminated successfully without warnings.

4. Imp the stats table: T_STATS
imp system/oracle fromuser=hr touser=scott tables=t_stats file=t_stats.dmp log=4_imp_t_stats.log

Import: Release 10.2.0.1.0 - Production on Mon Nov 10 18:52:22 2008

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by HR, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing HR's objects into SCOTT
. . importing table "T_STATS" 14 rows imported
Import terminated successfully without warnings.

5. Import table stats
Note: to update c5 in stats table is necessary !

system@TEST10G> @5_imp_tab_stats.sql
system@TEST10G> connect scott/tiger
Connected.
scott@TEST10G>
scott@TEST10G> -- must update column c5
scott@TEST10G>
scott@TEST10G> Prompt c5 stores schema name
c5 stores schema name
scott@TEST10G> select c5 from t_stats;

C5
------------------------------
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR
HR

14 rows selected.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> --  see what will happen if we don't update c5
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER             TABLE_NAME      NUM_ROWS LAST_ANALYZED
----------------- ------------- ---------- -------------------
HR                T                  40733 2008-11-10 18:59:31
SCOTT             T

scott@TEST10G>
scott@TEST10G>
scott@TEST10G> -- update c5
scott@TEST10G>
scott@TEST10G> update t_stats set c5='SCOTT';

14 rows updated.

scott@TEST10G>
scott@TEST10G> exec dbms_stats.import_table_stats(user, 'T', stattab=> 'T_STATS');

PL/SQL procedure successfully completed.

scott@TEST10G>
scott@TEST10G> select owner,table_name, num_rows, last_analyzed
  2    from dba_tables where table_name='T' and owner in ('HR', 'SCOTT');

OWNER          TABLE_NAME       NUM_ROWS LAST_ANALYZED
-------------- -------------- ---------- -------------------
HR             T                   40733 2008-11-10 18:59:31
SCOTT          T                   40733 2008-11-10 18:59:31

Some Basic RAC Concepts

I found previously I have developed some reading notes about RAC, I am reviewing them today, becuase I will have a technical phone screen tonight which is said about Oracle DBA with RAC experiences. Hopefully review those notes will refresh my mind.

Below are part of the notes about some basic RAC concepts:

13. RAC concepts

13.1 What is RAC ?
RAC architecture consisted of:
(1) Clustered hardware:
- Multiple instances accessing the same database
- One instance per node
- Interconnected nodes act as a single server

(2) Real Application Clusters software:
- Manage coordinated data access and data changes among instances
- Each instance sees a consistent image of the database
- Pass coordination information and data images among each interconnected nodes.

13.2 Why use RAC
- High availability
- Scalability

13.3 RAC specific processes
At RAC instance level
* LMON – GES Monitor
* LMD0 – GES Daemon
* LMSx – GCS processes: manages resources and provides inter-instance resource control.
* LCK0: lock process
* DIAG: Diagnosability process
At cluster level:
* CRSD and RACGIMON: are engines for high-availability operations
* OCSSD: Provides access to node membership and group services
* EVMD: Scan callout directory and invokes callouts in reactions to detected events
* OPRROCD: Is a process monitor for the cluster: (not in Windows and Linux)

13.4 Global Resource Coordination

- Global Resource Directory (GRD):
* Each instance maintains a part of GRD
* Each particular resource has a master (the instance that manages all the information about this resource)
- Global Cache Service (GCS)
* buffer cache
* cache coherency – Cache Fusion algorithm
- Global Enqueue Service (GED)
* dictionary cache locks
* library cache locks
* deadlock

13.5 Object Affinity and Dynamic Remastering

- GCS enables the database to automatically adapt and migrate resources in the GRD
- GCS keeps track of the number of GCS requests on a per-instance and per-object basis.
- Master a buffer cache resource on the instance where it is mostly accesse

13.6 RAC database files
- Must be on local storage: Oracle cluster software (ORA_CRS_HOME)
- Can be on shared but better on local storage: Oracle RDBMS and ASM software
- Must be on shared storage (e.g. ASM disk groups, CFS )

RAW devices:
* OCR files - on raw devices
* Voting files (disks) – on raw devices

ASM Disk group: +DATADG
* Data files
* Temp files
* Control files
* SPFILE
* Undo tablespace files ( each instance has its own dedicated ones)
* Redo log files ( at least two groups for each instance)

ASM DISK group: +FLADG
* Flash recovery area files
* Change tracking file
* Archived log files* Control file (duplicate

KSH expr command integer overflow

Last night we have recived false alerts about a filesystem space issue. It turns out there is a bug in the script related to integer overflow in the "expr" command. I found this overflow error can be see on our HP-UX system, but not on Solaris or Linux machines. Here are the testing results:

----------- test script: test.ksh begins next line -----------------
#!/bin/ksh
## Bad##
FS=2840644712
FS=`expr $FS / 1024` ;
echo $FS
## Good##
FS=2840644712
FS=`echo $FS/1024 bc `
echo $FS
----------- test script ends last line -----------------


HP-UX
~~~~~~~~

$ uname -a
HP-UX dhpnbe5 B.11.23 U ia64 3928363565 unlimited-user license
$ ./test.ksh
-1420236
2774067


Linux
~~~~~~

$ uname -a
Linux wrpe2e06.edc.mycompany.net 2.6.9-55.0.9.ELlargesmp #1 SMP Tue Sep 25 02:25:48 EDT 2007 x86_64 x86_64 x86_64 GNU/Linux
oracle@wrpe2e06.edc.mycompany.net(!) ph1etem3 /tmp
$ ./test.ksh
2774067
2774067


Solaris
~~~~~~~

$ uname -a
SunOS wspebp06 5.10 Generic_127111-11 sun4u sparc SUNW,Sun-Fire
$ ./test.ksh
2774067
2774067