Tuesday, May 10, 2016

Extract Data Guard Commands

Hi,
I found a great code for extracting data guard commands (reverse engineering)
I adjusted it to oracle 12.1 with far sync and it working fine.

displayconfig.sql:
SET SERVEROUTPUT ON
SET LINESIZE 300
SET FEEDBACK OFF

DECLARE
rid INTEGER;
indoc VARCHAR2 ( 4000 );
outdoc VARCHAR2 ( 4000 );
p INTEGER;
z XMLTYPE;
y CLOB;
v_xml XMLTYPE;
tout VARCHAR2 ( 4000 );
db_type VARCHAR2 ( 10 );
BEGIN
indoc := '';
y := NULL;
rid := dbms_drs.do_control ( indoc );

outdoc := NULL;
p := 1;

WHILE ( outdoc IS NULL )
LOOP
outdoc := dbms_drs.get_response ( rid, p );
y := y || TO_CLOB ( outdoc );
END LOOP;

BEGIN
WHILE ( outdoc IS NOT NULL )
LOOP
p := p + 1;

outdoc := dbms_drs.get_response ( rid, p );
y := y || TO_CLOB ( outdoc );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;

z := XMLType ( y );

dbms_drs.delete_request ( rid );

FOR l IN ( SELECT name
, role
, id
, connectdb
FROM XMLTABLE (
'/DRC/SITE/DATABASE'
PASSING z
COLUMNS name PATH '/DATABASE/@name'
, role PATH '/DATABASE/@role'
, id PATH '/DATABASE/@id'
, connectdb PATH '/DATABASE/@connect'
) )
LOOP
CASE l.role
WHEN 'PRIMARY'
THEN
outdoc := 'create configuration dr as primary database is ''' || l.name || ''' connect identifier is ' || l.connectdb || ';';
db_type := 'database';
DBMS_OUTPUT.put_line ( outdoc );
WHEN 'PHYSICAL'
THEN
outdoc := 'add database ''' || l.name || ''' as connect identifier is ' || l.connectdb || ';';
db_type := 'database';
DBMS_OUTPUT.put_line ( outdoc );
WHEN 'FAR_SYNC_INSTANCE'
THEN
outdoc := 'add far_sync ''' || l.name || ''' as connect identifier is ' || l.connectdb || ';';
db_type := 'far_sync';
DBMS_OUTPUT.put_line ( outdoc );
END CASE;

indoc := '';
y := NULL;
rid := dbms_drs.do_control ( indoc );

outdoc := NULL;
p := 1;

WHILE ( outdoc IS NULL )
LOOP
outdoc := dbms_drs.get_response ( rid, p );
y := y || TO_CLOB ( outdoc );
END LOOP;

BEGIN
WHILE ( outdoc IS NOT NULL )
LOOP
p := p + 1;

outdoc := dbms_drs.get_response ( rid, p );
y := y || TO_CLOB ( outdoc );
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;

z := XMLType ( y );

INSERT INTO c ( c )
VALUES ( z );

SELECT XMLQUERY ( '/RESOURCE/PROPERTY_LIST/VALUE' PASSING z RETURNING CONTENT ) INTO v_xml FROM DUAL;


FOR q
IN ( SELECT name, VALUE, property_type AS r
FROM XMLTABLE (
'/VALUE'
PASSING v_xml
COLUMNS name PATH '/VALUE/@name', VALUE PATH '/VALUE/@value', property_type PATH '/VALUE/@property_type'
)
WHERE VALUE IS NOT NULL AND
name IN ( 'AlternateLocation'
, 'ApplyLagThreshold'
, 'ApplyParallel'
, 'ArchiveLagTarget'
, 'Binding'
, 'DbFileNameConvert'
, 'DelayMins'
, 'DGConnectIdentifier'
--, 'FastStartFailoverAutoReinstate'
--, 'FastStartFailoverLagLimit'
--, 'FastStartFailoverPmyShutdown'
, 'FastStartFailoverTarget'
--, 'FastStartFailoverThreshold'
, 'InstanceName'
, 'LogArchiveFormat'
, 'LogArchiveMaxProcesses'
, 'LogArchiveMinSucceedDest'
, 'LogArchiveTrace'
, 'LogFileNameConvert'
, 'LogShipping'
, 'LogXptMode'
, 'LogXptMode'
, 'MaxConnections'
, 'MaxFailure'
, 'NetTimeout'
, 'ObserverConnectIdentifier'
, 'PreferredApplyInstance'
, 'RedoCompression'
, 'RedoRoutes'
, 'ReopenSecs'
, 'StandbyArchiveLocation'
, 'StandbyFileManagement'
, 'StaticConnectIdentifier'
, 'TransportDisconnectedThreshold'
, 'TransportLagThreshold'
, 'ApplyInstanceTimeout' ) )
LOOP
DBMS_OUTPUT.put_line ( 'edit ' || db_type || ' ' || l.name || ' set property ''' || q.name || ''' = ''' || q.VALUE || ''';' );
END LOOP;

dbms_drs.delete_request ( rid );
END LOOP;
END;
/

SET FEEDBACK ON

This is my output:
create configuration dr as primary database is 'pdb11' connect identifier is pdb11;
edit database pdb11 set property 'DGConnectIdentifier' = 'pdb11';
edit database pdb11 set property 'LogXptMode' = 'SYNC';
edit database pdb11 set property 'RedoRoutes' = '(LOCAL : fdb11 SYNC)';
edit database pdb11 set property 'DelayMins' = '0';
edit database pdb11 set property 'Binding' = 'optional';
edit database pdb11 set property 'MaxFailure' = '0';
edit database pdb11 set property 'MaxConnections' = '1';
edit database pdb11 set property 'ReopenSecs' = '15';
edit database pdb11 set property 'NetTimeout' = '30';
edit database pdb11 set property 'RedoCompression' = 'DISABLE';
edit database pdb11 set property 'LogShipping' = 'ON';
edit database pdb11 set property 'ApplyInstanceTimeout' = '0';
edit database pdb11 set property 'ApplyLagThreshold' = '0';
edit database pdb11 set property 'TransportLagThreshold' = '0';
edit database pdb11 set property 'TransportDisconnectedThreshold' = '0';
edit database pdb11 set property 'ApplyParallel' = 'AUTO';
edit database pdb11 set property 'StandbyFileManagement' = 'AUTO';
edit database pdb11 set property 'ArchiveLagTarget' = '0';
edit database pdb11 set property 'LogArchiveMaxProcesses' = '1';
edit database pdb11 set property 'LogArchiveMinSucceedDest' = '1';
edit database pdb11 set property 'DbFileNameConvert' = 'pdb11, sdb11';
edit database pdb11 set property 'LogFileNameConvert' = 'pdb11, sdb11';
edit database pdb11 set property 'FastStartFailoverTarget' = 'sdb11';
edit database pdb11 set property 'InstanceName' = 'pdb11';
edit database pdb11 set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.101.104.20)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb11_DGMGRL)(INSTANCE_NAME=pdb11)(SERVER=DEDICATED)))';
edit database pdb11 set property 'StandbyArchiveLocation' = 'USE_DB_RECOVERY_FILE_DEST';
edit database pdb11 set property 'LogArchiveTrace' = '0';
edit database pdb11 set property 'LogArchiveFormat' = '%t_%s_%r.arc';
add database 'sdb11' as connect identifier is sdb11;
edit database sdb11 set property 'DGConnectIdentifier' = 'sdb11';
edit database sdb11 set property 'LogXptMode' = 'SYNC';
edit database sdb11 set property 'RedoRoutes' = '(LOCAL : fdb11 SYNC)';
edit database sdb11 set property 'DelayMins' = '0';
edit database sdb11 set property 'Binding' = 'optional';
edit database sdb11 set property 'MaxFailure' = '0';
edit database sdb11 set property 'MaxConnections' = '1';
edit database sdb11 set property 'ReopenSecs' = '15';
edit database sdb11 set property 'NetTimeout' = '30';
edit database sdb11 set property 'RedoCompression' = 'DISABLE';
edit database sdb11 set property 'LogShipping' = 'ON';
edit database sdb11 set property 'ApplyInstanceTimeout' = '0';
edit database sdb11 set property 'ApplyLagThreshold' = '0';
edit database sdb11 set property 'TransportLagThreshold' = '0';
edit database sdb11 set property 'TransportDisconnectedThreshold' = '0';
edit database sdb11 set property 'ApplyParallel' = 'AUTO';
edit database sdb11 set property 'StandbyFileManagement' = 'AUTO';
edit database sdb11 set property 'ArchiveLagTarget' = '0';
edit database sdb11 set property 'LogArchiveMaxProcesses' = '1';
edit database sdb11 set property 'LogArchiveMinSucceedDest' = '1';
edit database sdb11 set property 'DbFileNameConvert' = 'pdb11, sdb11';
edit database sdb11 set property 'LogFileNameConvert' = 'pdb11, sdb11';
edit database sdb11 set property 'FastStartFailoverTarget' = 'pdb11';
edit database sdb11 set property 'InstanceName' = 'sdb11';
edit database sdb11 set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=e20ibm3)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sdb11_DGMGRL)(INSTANCE_NAME=sdb11)(SERVER=DEDICATED)))';
edit database sdb11 set property 'StandbyArchiveLocation' = 'USE_DB_RECOVERY_FILE_DEST';
edit database sdb11 set property 'LogArchiveTrace' = '0';
edit database sdb11 set property 'LogArchiveFormat' = '%t_%s_%r.arc';
add far_sync 'fdb11' as connect identifier is fdb11;
edit far_sync fdb11 set property 'DGConnectIdentifier' = 'fdb11';
edit far_sync fdb11 set property 'LogXptMode' = 'ASYNC';
edit far_sync fdb11 set property 'RedoRoutes' = '(pdb11 : sdb11 ASYNC)(sdb11 : pdb11 ASYNC)';
edit far_sync fdb11 set property 'DelayMins' = '0';
edit far_sync fdb11 set property 'Binding' = 'optional';
edit far_sync fdb11 set property 'MaxFailure' = '0';
edit far_sync fdb11 set property 'MaxConnections' = '1';
edit far_sync fdb11 set property 'ReopenSecs' = '15';
edit far_sync fdb11 set property 'NetTimeout' = '30';
edit far_sync fdb11 set property 'RedoCompression' = 'DISABLE';
edit far_sync fdb11 set property 'LogShipping' = 'ON';
edit far_sync fdb11 set property 'ApplyInstanceTimeout' = '0';
edit far_sync fdb11 set property 'ApplyLagThreshold' = '0';
edit far_sync fdb11 set property 'TransportLagThreshold' = '0';
edit far_sync fdb11 set property 'TransportDisconnectedThreshold' = '0';
edit far_sync fdb11 set property 'ApplyParallel' = 'AUTO';
edit far_sync fdb11 set property 'StandbyFileManagement' = 'AUTO';
edit far_sync fdb11 set property 'ArchiveLagTarget' = '0';
edit far_sync fdb11 set property 'LogArchiveMaxProcesses' = '1';
edit far_sync fdb11 set property 'LogArchiveMinSucceedDest' = '1';
edit far_sync fdb11 set property 'DbFileNameConvert' = 'pdb11, fdb11';
edit far_sync fdb11 set property 'LogFileNameConvert' = 'pdb11, fdb11';
edit far_sync fdb11 set property 'InstanceName' = 'fdb11';
edit far_sync fdb11 set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=fdb11_DGMGRL)(INSTANCE_NAME=fdb11)(SERVER=DEDICATED)))';
edit far_sync fdb11 set property 'StandbyArchiveLocation' = 'USE_DB_RECOVERY_FILE_DEST';
edit far_sync fdb11 set property 'LogArchiveTrace' = '0';
edit far_sync fdb11 set property 'LogArchiveFormat' = '%t_%s_%r.arc';
add far_sync 'kdb11' as connect identifier is kdb11;
edit far_sync kdb11 set property 'DGConnectIdentifier' = 'kdb11';
edit far_sync kdb11 set property 'LogXptMode' = 'ASYNC';
edit far_sync kdb11 set property 'RedoRoutes' = '(pdb11 : sdb11 ASYNC)(sdb11 : pdb11 ASYNC)';
edit far_sync kdb11 set property 'DelayMins' = '0';
edit far_sync kdb11 set property 'Binding' = 'optional';
edit far_sync kdb11 set property 'MaxFailure' = '0';
edit far_sync kdb11 set property 'MaxConnections' = '1';
edit far_sync kdb11 set property 'ReopenSecs' = '15';
edit far_sync kdb11 set property 'NetTimeout' = '30';
edit far_sync kdb11 set property 'RedoCompression' = 'DISABLE';
edit far_sync kdb11 set property 'LogShipping' = 'ON';
edit far_sync kdb11 set property 'ApplyInstanceTimeout' = '0';
edit far_sync kdb11 set property 'ApplyLagThreshold' = '0';
edit far_sync kdb11 set property 'TransportLagThreshold' = '0';
edit far_sync kdb11 set property 'TransportDisconnectedThreshold' = '0';
edit far_sync kdb11 set property 'ApplyParallel' = 'AUTO';
edit far_sync kdb11 set property 'StandbyFileManagement' = 'AUTO';
edit far_sync kdb11 set property 'ArchiveLagTarget' = '0';
edit far_sync kdb11 set property 'LogArchiveMaxProcesses' = '4';
edit far_sync kdb11 set property 'LogArchiveMinSucceedDest' = '1';
edit far_sync kdb11 set property 'DbFileNameConvert' = 'pdb11, kdb11';
edit far_sync kdb11 set property 'LogFileNameConvert' = 'pdb11, kdb11';
edit far_sync kdb11 set property 'InstanceName' = 'kdb11';
edit far_sync kdb11 set property 'StaticConnectIdentifier' = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.61)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=kdb11_DGMGRL)(INSTANCE_NAME=kdb11)(SERVER=DEDICATED)))';
edit far_sync kdb11 set property 'StandbyArchiveLocation' = 'USE_DB_RECOVERY_FILE_DEST';
edit far_sync kdb11 set property 'LogArchiveTrace' = '0';
edit far_sync kdb11 set property 'LogArchiveFormat' = '%t_%s_%r.arc';


Enjoy

Monday, November 02, 2015

Testing FAILOVER when primary database is not available

Send redo data from Primary database if MOUNT is possible.
ALTER SYSTEM FLUSH REDO TO target_db_name;
At Standby, if Flashback Database is not already enabled:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
ALTER DATABASE FLASHBACK ON;
Create a restore point BEFORE_FAILOVER, The restore point will be used later to restore the database again to the same state before FAILOVER scenario.
set lines 300
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
CREATE RESTORE POINT before_failover GUARANTEE FLASHBACK DATABASE;
SELECT scn, guarantee_flashback_database, name FROM v$restore_point;

SCN GUARANTEE_FLASHBACK_DATABASE NAME
------- ---------------------------- ----------------
2619073 YES BEFORE_FAILOVER
Testing FAILOVER on physical standby database STBY.
SELECT name, db_unique_name, log_mode, protection_mode, database_role
FROM v$database;

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- -------------
PRIM STBY ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

ALTER DATABASE FAILOVER TO STBY;

SELECT name, db_unique_name, log_mode, protection_mode, database_role
FROM v$database;

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- -------------
PRIM STBY ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY
STBY is now primary database. Following query shows how to check when the database was converted from physical standby into primary database.
SELECT to_char(standby_became_primary_scn) scn
FROM V$DATABASE;

SCN
----------------------------------------
7284214
Because flashback logs are available it’s possible to restore primary database STBY again into physical standby database. Flashback can be executed using two methods SCN number of restore point
Method with SCN number:
FLASHBACK DATABASE TO SCN standby_became_primary_scn;
Method with restore point:
FLASHBACK DATABASE TO RESTORE POINT restore_point_name;
Flashback primary database STBY and conversion into physical standby
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
FLASHBACK DATABASE TO RESTORE POINT before_failover;
ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Start old primary database STBY to have the same state as before FAILOVER scenario
SELECT name, db_unique_name, log_mode, protection_mode, database_role
FROM v$database;

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- ----------------
PRIM STBY ARCHIVELOG MAXIMUM PERFORMANCE PHYSICAL STANDBY

STARTUP;

SELECT name, db_unique_name, log_mode, protection_mode, database_role
FROM v$database;

NAME DB_UNIQUE_NAME LOG_MODE PROTECTION_MODE DATABASE_ROLE
---- -------------- ---------- ------------------- -------------
STBY STBY ARCHIVELOG MAXIMUM PERFORMANCE PRIMARY

Friday, October 23, 2015

Rolling Forward a Physical Standby Database Using the RECOVER FROM SERVICE

Hi
Since Oracle 12c we can recover standby only by accessing the standby database/site, no need to copy nor transfer any file
Short and easy - just copy and paste
enjoy :)

Standby Database Name: STBY
Primary Database Name: PRIM

On Standby
sqlplus / as sysdba
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER DATABASE MOUNT STANDBY DATABASE;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

rman target /
RECOVER DATABASE FROM SERVICE PRIM USING COMPRESSED BACKUPSET NOREDO SECTION SIZE 120M;

sqlplus / as sysdba
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;

rman target /
RESTORE STANDBY CONTROLFILE FROM SERVICE PRIM;

sqlplus / as sysdba
ALTER DATABASE MOUNT STANDBY DATABASE;

rman target /
CATALOG START WITH '+DATA/STBY/DATAFILE/';
SWITCH DATABASE TO COPY;

sqlplus / as sysdba
set pages 0 verify off feed off term off echo off
spool /tmp/clear.sql
select distinct 'ALTER DATABASE CLEAR LOGFILE GROUP '||GROUP#||';' as cmd from v$logfile;
spool off
@/tmp/clear.sql
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

For Oracle 11g there is a longer version, see Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup. (Doc ID 836986.1)

Yossi

Monday, December 22, 2014

Problems in: FETCH FIRST n PERCENT ROWS ONLY

Wanting to count how many rows in emp table

SQL> SELECT COUNT (*) FROM emp;

COUNT(*)
----------
100

Cool... now let me see how much is 5 percent of emp

SQL> SELECT COUNT (*) FROM emp
2 FETCH FIRST 5 PERCENT ROWS ONLY;

COUNT(*)
----------
100

Mmmmm.... it can't be also 100 ... somthing is wierd ..
Let's try other way

SQL> SELECT COUNT (*)
2 FROM ( SELECT empno
3 FROM emp
4 FETCH FIRST 5 PERCENT ROWS ONLY);

COUNT(*)
----------
5

Cool, now it is working :)
Now let's try in PL/SQL

SQL> DECLARE
2 l_Percent_to_fetch PLS_INTEGER;
3 l_cnt PLS_INTEGER;
4 BEGIN
5 SELECT COUNT (*)
6 INTO l_cnt
7 FROM ( SELECT empno
8 FROM emp
9 FETCH FIRST l_Percent_to_fetch PERCENT ROWS ONLY);
10 END;
11 /
DECLARE
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 4480
Session ID: 196 Serial number: 37163

What!!!! can't I use variables ???
let's try hadcoded:

SQL> DECLARE
2 l_cnt PLS_INTEGER;
3 BEGIN
4 SELECT COUNT (*)
5 INTO l_cnt
6 FROM ( SELECT empno
7 FROM emp
8 FETCH FIRST 5 PERCENT ROWS ONLY);
9 END;
10 /

PL/SQL procedure successfully completed
.

Cool.. not so cool - I was forced to bypass a BUG :( :(

Friday, July 26, 2013

Export in Pl/Sql via DBMS_DATAPUMP

CREATE OR REPLACE PACKAGE maintenance
AS
   PROCEDURE export_myschema;
   PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2);
   PROCEDURE stop_all_jobs;
   PROCEDURE import_schema (file_name VARCHAR2);
END maintenance;
CREATE OR REPLACE PACKAGE BODY maintenance
AS
   PROCEDURE export_myschema
   AS
      handle        NUMBER;
      file_name     VARCHAR2 (200);
      log_name      VARCHAR2 (200);
      JOBNAME       VARCHAR2 (200);
      file_prefix   VARCHAR2 (30);
      dir_name      VARCHAR2 (4000);
      l_fexists BOOLEAN;
      l_file_length PLS_INTEGER;
      l_block_size BINARY_INTEGER;
   BEGIN
      dir_name := 'EXPORT_DIR';
      file_prefix := 'myschema_prod_data_';
      file_name :=
         file_prefix || MOD (TO_NUMBER (TO_CHAR (SYSDATE, 'D')), 2) -- toggle 0 or 1                                                                   || '.dmp';
      log_name :=
            file_prefix
         || MOD (TO_NUMBER (TO_CHAR (SYSDATE, 'D')), 2)
         || '_'
         || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS')
         || '.log';
      JOBNAME := 'EXPORT_JOB_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS');
      UTL_FILE.fgetattr (dir_name,
                     file_name,
                     l_fexists,
                     l_file_lenght,
                     l_block_size);
      IF l_fexists
      THEN
       DBMS_OUTPUT.put_line ( 'file to backup:'|| file_name || ' already exists - deleting it...');
       UTL_FILE.fremove (dir_name, file_name);
      ELSE
       DBMS_OUTPUT.put_line ( 'file to backup:'|| file_name || ' does not exists - continuing...');
      END IF;
      handle :=
         DBMS_DATAPUMP.open (operation   => 'EXPORT',
                             job_mode    => 'SCHEMA',
                             job_name    => JOBNAME,
                             version     => 'LATEST');
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => log_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      );
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => file_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      );
      DBMS_DATAPUMP.METADATA_FILTER (handle   => handle,
                                     name     => 'SCHEMA_EXPR',
                                     VALUE    => 'IN (''MYSCHEMA'')');
      DBMS_DATAPUMP.METADATA_FILTER (
         handle        => handle,
         name          => 'NAME_EXPR',
         VALUE         => 'NOT IN (''EMP'',''DEPT'')',
         object_type   => 'TABLE'
      );
      DBMS_DATAPUMP.START_JOB (handle);
      DBMS_DATAPUMP.DETACH (handle);
   END export_myschema; 
   PROCEDURE stop_job (job_name VARCHAR2, schema_name VARCHAR2)
   AS
      handle     NUMBER;
      day_name   VARCHAR2 (3);
   BEGIN
      handle := DBMS_DATAPUMP.ATTACH (job_name, schema_name);
      DBMS_DATAPUMP.STop_JOB (handle, 1, 0);
   END stop_job;
   PROCEDURE stop_all_jobs
   IS
      handle   NUMBER;
   BEGIN
      FOR x IN (SELECT   * FROM DBA_datapump_jobs)
      LOOP
         BEGIN
            BEGIN
               EXECUTE IMMEDIATE   'drop table '
                                || x.owner_name
                                || '.'
                                || x.job_name;
               DBMS_OUTPUT.put_line (
                  'Table Job: ' || x.job_name || ' - Dropped'
               );
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.put_line (
                     'Table Job: ' || x.job_name || ' - does not exists'
                  );
            END;
            handle := DBMS_DATAPUMP.ATTACH (x.job_name, x.owner_name);
            DBMS_DATAPUMP.STop_JOB (handle, 1, 0);
            DBMS_OUTPUT.put_line ('Job: ' || x.job_name || ' - DONE');
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (
                  'Job: ' || x.job_name || ' couldn''t removed'
               );
         END;
      END LOOP;
   END stop_all_jobs; 
   PROCEDURE import_schema (file_name VARCHAR2)
   AS
      handle      NUMBER;
      log_name    VARCHAR2 (200);
      JOBNAME     VARCHAR2 (200);
      dir_name    VARCHAR2 (4000);
   BEGIN
      dir_name := 'IMPORT_DIR';
  
      log_name := 'import_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS') || '.log';
      JOBNAME := 'IMPORT_JOB_' || TO_CHAR (SYSDATE, 'YYMMDDHH24MISS');
      handle :=
         DBMS_DATAPUMP.open (operation   => 'IMPORT',
                             job_mode    => 'SCHEMA',
                             job_name    => JOBNAME,
                             version     => 'LATEST');
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => log_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE
      );
      DBMS_DATAPUMP.ADD_FILE (
         handle      => handle,
         filename    => file_name,
         directory   => dir_name,
         filetype    => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE
      );
      DBMS_DATAPUMP.METADATA_FILTER (handle   => handle,
                                     name     => 'SCHEMA_EXPR',
                                     VALUE    => 'IN (''MYSCHEMA'')');
      DBMS_DATAPUMP.SET_PARAMETER (handle   => handle,
                                   name     => 'TABLE_EXISTS_ACTION',
                                   VALUE    => 'TRUNCATE');
      DBMS_DATAPUMP.SET_PARALLEL (handle => handle, degree => 4);
      DBMS_DATAPUMP.START_JOB (handle);
      DBMS_DATAPUMP.DETACH (handle);
   END import_schema; 
END maintenance;
/

Thursday, January 24, 2013

Undelete Files in Linux

Usually I take care for backups, but the first time I tempted to neglect this habit - I just have met Morphy's law :(
Fortunately there is a solution:

yum install testdisk
OR
apt-get install testdisk
To recover files simply type:
photorec
and follow the instructions

So simple :)

Monday, December 03, 2012

Format scripts in awk, csh, ksh, perl, sh

I am used to relay on tools for formatting and indenting SQL or PL/SQL code.
Looking for the same for linux scripts (especially bash) I found the following code
fmt.script