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

Monday, August 27, 2012

Sending Mail from the database


You should have an outgoing SMTP server IP to configure sending mail from the database.
I already put it in the database server /etc/hosts as mailhost

sqlplus / as sysdba
@?/rdbms/admin/utlmail.sql
@?/rdbms/admin/prvtmail.plb
grant execute on UTL_MAIL to public;
ALTER SYSTEM SET smtp_out_server = 'mailhost' scope=both;

a simple example

BEGIN
UTL_MAIL.send(sender => 'Yossi@NixonIT.com',
recipients => 'you@address.com',
subject => 'Test Mail',
message => 'Hello World',
mime_type => 'text; charset=us-ascii');
END;
/

For further advanced options such as attachments see in this wiki