OracleNotes

Yes, a question indeed, looking for help through the blog! I recently got into a streams issue, have not supported streams in a live environment until recently (when you work on your virtual desktop streams and golden gate and everything works perfect, isn’t!!)…

Ok, here is the question… need to give you some background… To protect the innocent, I have changed the database, host names…

Apparently Streams was set up on the database for SAP Data Services BI application on this database, and was working fine until Aug 21, until the database cycled. This is visible in the alert log, and can see messages such as this until just seconds before the database shutdown. BI also confirmed they were receiving changes from Oracle fine.

Wed Aug 21 01:32:59 2013
LOGMINER: End   mining logfile for session 4 thread 1 sequence 6667, +DEV2/dev2/onlinelog/group_7.538.810543533
LOGMINER: Begin mining logfile for session 4 thread 1 sequence 6668, +DEV2/dev2/onlinelog/group_8.540.810543545
Wed Aug 21 01:33:14 2013

After initiating the normal database shutdown, I can also see messages of a clean streams shutdown as well…

Wed Aug 21 02:34:46 2013
LOGMINER: End   mining logfile for session 4 thread 1 sequence 6670, +DEV2/dev2/onlinelog/group_10.544.81
0543567
LOGMINER: Begin mining logfile for session 4 thread 1 sequence 6671, +DEV2/dev2/onlinelog/group_11.546.81
0543579
Wed Aug 21 02:34:54 2013
Archived Log entry 6690 added for thread 1 sequence 6670 ID 0xffffffffacaf64cf dest 1:
Wed Aug 21 02:39:48 2013
Completed checkpoint up to RBA [0x1a0f.2.10], SCN: 8657881886596
Wed Aug 21 02:42:45 2013
Shutting down instance (immediate)
Stopping background process SMCO
Shutting down instance: further logons disabled
Stopping background process QMNC
Wed Aug 21 02:42:52 2013
Stopping background process CJQ0
Wed Aug 21 02:42:52 2013
Streams Apply Server AS02 for CDC$A_RD_EBS with pid=397 OS id=26667 stopped
Wed Aug 21 02:42:52 2013
Streams Apply Reader AS01 for CDC$A_RD_EBS with pid=396 OS id=26665 stopped
Wed Aug 21 02:42:52 2013
Streams APPLY AP01 for CDC$A_RD_EBS with pid=329, OS id=26661 stopped
Wed Aug 21 02:42:53 2013
LOGMINER: session#=4 (CDC$C_RD_EBS), preparer MS02 pid=460 OS id=28280 sid=1707 stopped
Wed Aug 21 02:42:53 2013
LOGMINER: session#=4 (CDC$C_RD_EBS), builder MS01 pid=459 OS id=28278 sid=1336 stopped
Stopping background process MMNL
Wed Aug 21 02:42:53 2013
LOGMINER: session#=4 (CDC$C_RD_EBS), reader MS00 pid=406 OS id=28276 sid=1693 stopped
Wed Aug 21 02:42:53 2013
Streams CAPTURE CP01 for CDC$C_RD_EBS with pid=404, OS id=26669 stopped
Stopping background process MMON

And during the database startup, I can see streams coming up as well…

Wed Aug 21 03:03:24 2013
Streams CAPTURE CP01 for CDC$C_RD_EBS with pid=33, OS id=25421 is in combined capture and apply mode.
Capture CDC$C_RD_EBS is handling 1 applies.
Starting persistent Logminer Session with sid = 4 for Streams Capture CDC$C_RD_EBS
LOGMINER: Parameters summary for session# = 4
LOGMINER: Number of processes = 3, Transaction Chunk Size = 1
LOGMINER: Memory Size = 30M, Checkpoint interval = 1000M
LOGMINER: SpillScn 0, ResetLogScn 8637982978142
LOGMINER: summary for session# = 4
LOGMINER: StartScn: 8657881991134 (0x07df.d1fb07de)
LOGMINER: EndScn: 0
LOGMINER: HighConsumedScn: 8657881991134 (0x07df.d1fb07de)
LOGMINER: session_flag: 0x0
LOGMINER: Read buffers: 16
LOGMINER: LowCkptScn: 8653693507993 (0x07de.d853d999)
LOGMINER: HighCkptScn: 8653694790356 (0x07de.d8676ad4)
LOGMINER: SkipScn: 8653693507993 (0x07de.d853d999)
Wed Aug 21 03:05:27 2013

But no capture happening. Ran streams healthcheck, and it showed not having the archived log files registered with logminer, so manually registered the log files using command

ALTER DATABASE REGISTER OR REPLACE LOGICAL LOGFILE '+ORAARCH/dev2/archivelog/2013_08_22/thread_1_seq_6728.1179.824120475' FOR 'CDC$C_RD_EBS'

They showed up in dba_registered_archived_log properly. Stopped and started the capture, but nothing happening. Still the message is:

SQL> select capture_name, state from gv$streams_capture;

CAPTURE_NAME
------------------------------
STATE
--------------------------------------------------------------------------------
CDC$C_RD_EBS
WAITING FOR DICTIONARY REDO: FIRST SCN 8653644859909

Increased memory from 30M to 50M, no change. Traced (based on metalink note 313279.1), and found in the trace file:

*** 2013-08-22 17:46:11.522
krvxptl: Priming thread list with scn: 0x07de.d853d999
krvxptl: 6758 archived record(s) searched, 12 online record(s) searched.
krvxpsr: Missing logfile - No logfiles registered within given SCN range

And the log file it is looking for is from July 31st!

++ Minimum Archive Log Necessary to Restart Capture ++
Note: This query is valid for databases where the capture processes exist for the same source database.

Capture will restart from SCN 8653675791039 in the following file:
+ORAARCH/dev2/archivelog/2013_07_31/thread_1_seq_5284.926.822208349
(31-JUL-13)

And these are the SCN numbers found in DBA_CAPTURE (date when they are generated in brackets for clarity).

FIRST_SCN – 8653644859909 (July 30)

APPLIED_SCN – 8657881991134 (Aug 21)

CAPTURED_SCN – 8657881991134 (Aug 21)

REQUIRED_CHECKPOINT_SCN (July 31)

STATUS_CHANGE_TIME is Aug 21

Found that the streams parameter for capture, CHECKPOINT_RETENTION_TIME was the default 60… so I can understand why FIRST_SCN did not move from where it was.

Changed the retention time to 2 days using:

exec dbms_capture_adm.alter_capture(‘CDC$C_RD_EBS’, checkpoint_retention_time => 2);

Stopped and started CDC capture several times, no change in status, still waiting for dictionary redo with required SCN, l an SCN generated on July 31.

So, there is something I do not understand is happening here in streams.

Since the captured_scn and applied_scn are both from Aug 21, why is required_checkpoint_scn from July 31? What changed during the database cycle? Is there any manual checkpoint advance we need to do before database shutdown?

Also, RMAN has not deleted any file from archivelog location yet (after backup) since Aug 21 as it is thinking that the CDC process requires them. We will have to force delete the archivelogs soon, as we might run out of room soon.

Thanks to you in advance for all help!

Wrote a little pl/sql program after several years… glad it worked as expected… thought of sharing the code, might be useful for someone out there…

The requirement is to drop tables that are older than 60 days from a schema named CR_BKUP periodically. Did not want to just drop the tables, so taking a backup of the whole schema using data pump api and then dropping the tables. The table names dropped are also written to a log file. The code is created as a procedure, so that I can schedule it using the database scheduler.

If I am violating any coding standards or a better way to accomplish the result, please let me know.

This code is also a quick demonstration of how to use DBMS_DATAPUMP and UTL_FILE programs.

/* Formatted */
CREATE OR REPLACE PROCEDURE XX_DROP_CR_BKUP_TABLES (
   threshold_days NUMBER DEFAULT 60)
IS
   /******************************************************************************
      NAME:       XX_DROP_CR_BKUP_TABLES
      PURPOSE:    Drop tables under CR_BKUP schema older than 60 days

   ******************************************************************************/

   CURSOR tablist
   IS
      SELECT owner || '.' || object_name table_name
        FROM dba_objects
       WHERE     owner = 'CR_BKUP'
             AND object_type = 'TABLE'
             AND last_ddl_time < (SYSDATE - threshold_days);

   log_file   UTL_FILE.FILE_TYPE;
BEGIN
   -- Export all the tables belonging to CR_BKUP schema to file system
   -- This dump file will be overwritten next time the job runs
   -- OS file backup will copy to the backup media
   DECLARE
      dp_file   NUMBER;
      jstatus   VARCHAR2 (200);
      ltmp      VARCHAR2 (200);
   BEGIN
      EXECUTE IMMEDIATE
         'CREATE OR REPLACE DIRECTORY CR_BKUP_DIR AS ''/u01/app/oracle/expdp''';

      BEGIN
         SELECT table_name
           INTO ltmp
           FROM user_tables
          WHERE table_name = 'CR_BKUP_TABLE_EXP';

         EXECUTE IMMEDIATE 'DROP TABLE CR_BKUP_TABLE_EXP';
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
      END;

      --utl_file.fremove( 'CR_BKUP_DIR', 'cr_bkup_tabs.dmp' );
      --utl_file.fremove( 'CR_BKUP_DIR', 'cr_bkup_tabs.log' );

      dp_file :=
         DBMS_DATAPUMP.open (OPERATION   => 'EXPORT',
                             JOB_MODE    => 'SCHEMA',
                             JOB_NAME    => 'CR_BKUP_TABLE_EXP');

      DBMS_DATAPUMP.add_file (
         HANDLE      => dp_file,
         FILENAME    => 'cr_bkup_tabs.dmp',
         DIRECTORY   => 'CR_BKUP_DIR',
         FILETYPE    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,
         REUSEFILE   => 1);
      DBMS_DATAPUMP.add_file (
         HANDLE      => dp_file,
         FILENAME    => 'cr_bkup_tabs.log',
         DIRECTORY   => 'CR_BKUP_DIR',
         FILETYPE    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
      DBMS_DATAPUMP.metadata_filter (HANDLE   => dp_file,
                                     NAME     => 'SCHEMA_LIST',
                                     VALUE    => '''CR_BKUP''');
      DBMS_DATAPUMP.start_job (HANDLE => dp_file);
      DBMS_DATAPUMP.wait_for_job (HANDLE => dp_file, JOB_STATE => jstatus);
      DBMS_DATAPUMP.detach (HANDLE => dp_file);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_DATAPUMP.detach (HANDLE => dp_file);
         RAISE;
   END;

   -- Export completed, now drop the tables older than the create date threshold
   --
   log_file := UTL_FILE.FOPEN ('CR_BKUP_DIR', 'cr_bkup_table_drop.txt', 'W');
   UTL_FILE.put_line (log_file,
                      'The following tables dropped on ' || SYSTIMESTAMP);

   FOR rtab IN tablist
   LOOP
      EXECUTE IMMEDIATE 'DROP TABLE ' || rtab.table_name;

      UTL_FILE.put_line (
         log_file,
         'Dropped at ' || SYSTIMESTAMP || ' : ' || rtab.table_name);
   END LOOP;

   UTL_FILE.put_line (log_file, 'Completed Drop at ' || SYSTIMESTAMP);
   UTL_FILE.FCLOSE (log_file);
END XX_DROP_CR_BKUP_TABLES;
/

For this procedure to work, the schema owning this procedure should be granted these privileges explicitly.

grant select on dba_segments to SYSTEM;
grant drop any table to SYSTEM;

Posts

Streams/CDC Question? Help…

A PL/SQL Program – After many many years!