How To Check Whether Physical Standby is in Sync with the Primary or Not?

The following tips will help you in monitoring sync between primary and standby databases.

1. Check for GAP on standby

2. Check redo received on standby

3. Check redo applied on standby

Solution:

Execute following queries:

A. On Primary

SQL> SELECT THREAD# "Thread",SEQUENCE# "Last Sequence Generated"

FROM V$ARCHIVED_LOG

WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)

ORDER BY 1;

Thread     Last Sequence Generated

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

1          19

2          13

3          11

B. On Physical Standby

SQL> SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"

FROM

(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,

(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL

WHERE

ARCH.THREAD# = APPL.THREAD#

ORDER BY 1;

Thread     Last Sequence Received Last Sequence Applied Difference

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

1          19                     19                    0

2          13                     13                    0

3          11                     11                    0

C. On Physical Standby

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

no rows selected

Now perform following checks:

1. Check for GAP

If query “C” returns any row then this means there are some archive log missing on standby.

Example:

SQL> SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

Thread     Low Sequence High Sequence

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

1          8            9

This example shows sequence 8 and 9 from thread 1 are missing on standby, Hence standby is not in sync with the primary.

If query “C” does not returns any row and output is “no row selected” than this means there is no archive gap on standby.

2. Check for redo received on standby

Compare value of “Last Sequence Generated” in query “A” with “Last Sequence Received” in query “B” for all threads.

If both values are same than this means that standby has received the last sequence generated on primary.

If both values are not same then there are some archives missing on standby, Hence standby is not in sync with the primary.

enjoy:-)

Database Switchover

A database can be in one of two mutually exclusive modes (primary or standby). These roles can be altered at runtime without loss of data or resetting of redo logs. This process is known as a Switchover and can be performed using the following statements..

-- Convert primary database to standby

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

ERROR at line 1:

ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

if the above error occurred, then try to shut the database then startup then try again.

-If still the same error persist,do the following in sequence: -

SQL> SHOW PARAMETER JOB_QUEUE_PROCESSES; (value for this should be 0)

SQL> SELECT SID, PROCESS, PROGRAM FROM V$SESSION;

SQL> alter database commit to switchover to PHYSICAL STANDBY WITH SESSION SHUTDOWN;

-- Shutdown primary database

SHUTDOWN IMMEDIATE;

-- Mount old primary database as standby database

STARTUP NOMOUNT;

ALTER DATABASE MOUNT STANDBY DATABASE;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

On the standby database issue the following commands: -

-- Convert standby database to primary

CONNECT / AS SYSDBA

ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;



SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required


if the above error occurred then execute the below commands:


SQL> alter database recover managed standby database finish;


SQL> alter database commit to switchover to physical primary with session shutdown;

-- Shutdown standby database

SHUTDOWN IMMEDIATE;

-- Open old standby database as primary

STARTUP;

enjoy:-)

Defining the time interval at which redo log wil apply to standby:

Here in this post we will learn how to set time interval of redo log application to standby.

How to create a physical standby in oracle 11g:

You can set a delay between the arrival of the archived redo log and it being applied on the standby server using the following commands.

On standby server: 

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DELAY 30 DISCONNECT FROM SESSION;

You may also changed the scenario as NODELAY means redo log will immediately applied using the below command:

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

-ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY DISCONNECT FROM SESSION;

enjoy:-)

External tables with Datapump

External tables are largely used as a convenient way of moving data into and out of the database.They let you query data in a flat file as though the file were an Oracle table.

Oracle uses SQL*Loader functionality, through the ORACLE_LOADER access driver to move data from the flat file into the database; it uses a Data Pump access driver to move data out of the db into a file in an Oracle-proprietary format, and back into the database from files of that format

Real-Life use of External Tables:

Suppose that you receive a daily .csv file from a reporting department. Instead of writing a SQL*Loader script to import each day's .csv file into your database, you can simply create an external table and write an "insert ... select" SQL query to insert the data directly into your tables. Place the day's CSV file in the location specified in the external table definition, run the query, and you're done.

Lets see how its done:

Choose or create a OS directory where you will put your .CSV(..flat file).

In my case i have taken : 'D:\et\'

My CSV file will look like this having name "emp.CSV"

001,Hutt,Jabba,896743856,jabba@thecompany.com,18

002,Simpson,Homer,382947382,homer@thecompany.com,20

003,Kent,Clark,082736194,superman@thecompany.com,5

004,Kid,Billy,928743627,billythkid@thecompany.com,9

005,Stranger,Perfect,389209831,nobody@thecompany.com,23

The next step is to create this directories in Oracle, and grant read/write access on it to the Oracle user who will be creating the external table

SQL> create directory opump as 'D:\et\';

Directory created.

SQL> grant read,write on directory opump to gg;

Grant succeeded.

Now,create the external table correspond to .CSV file.

SQL> conn gg/gg

Connected.

SQL>  create table xtern_empl_rpt

  2          ( empl_id varchar2(3),

  3            last_name varchar2(50),

  4            first_name varchar2(50),

  5            ssn varchar2(9),

  6           email_addr varchar2(100),

  7            years_of_service number(2,0)

  8          )

  9          organization external

 10         ( default directory opump

 11           access parameters

 12           ( records delimited by newline

 13             fields terminated by ','

 14           )

 15           location ('emp.csv')

 16       );

Table created.

External table is successfully created now.Lets check the data in oracle!

SQL> sho user;

USER is "GG"

SQL> select * from xtern_empl_rpt ;

Note: If u add more records in the csv file i.e. emp.csv then oracle table "xtern_empl_rpt" will automatically updated.

For example i will add a new record in my csv file:

7,Vishu,DBA,123,itsmevishu82@gmail.com,25

Save the file..

Lets see the record if updated in oracle table.

Unloading data into an external file using DATAPUMP.

Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver. This flat file is in an Oracle-proprietary format that can be read by DataPump. The syntax is similar to the CREATE TABLE... ORGANIZATION EXTERNAL above, but simpler -- since you can't specify the data format, you can specify very few access_parameters. The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.

Lets see how its done..

First create a user called "mm" and provide the privileges as below.

SQL> create user mm identified by mm;

User created.

SQL> grant read,write on directory opump to mm;

Grant succeeded.

Now,connect to mm and create the dumpfile using external table.

SQL> conn mm/mm

Connected.

SQL> create table import_empl_info

  2      ( empl_id varchar2(3),

  3        last_name varchar2(50),

  4        first_name varchar2(50),

  5        ssn varchar2(9),

  6        birth_dt date

  7      )

  8      organization external

  9      ( type oracle_datapump

 10       default directory opump

 11       location ('empl_info_rpt.dmp')

 12     ) ;

Table created.

It will create the dumpfile called "empl_info_rpt.dmp" which can be moved and used in any other database or same.

IMPORT THE TABLE BACK IN AGAIN..

We will now again load the data into oracle from the dump being created through external table.

First drop the table "import_empl_info".

SQL> drop table import_empl_info;

Table dropped.

Now load the data again ..

SQL> create table import_empl_info

  2      ( empl_id varchar2(3),

  3        last_name varchar2(50),

  4        first_name varchar2(50),

  5        ssn varchar2(9)

  6        )

  7      organization external

  8      ( type oracle_datapump

  9       default directory opump

 10       location ('empl_info_rpt.dmp')

 11     ) ;

We have seen now how External tables in 9i and 10g provide a convenient, seamless way to move data in and out of the database

Restrictions imposed on External tables:

1.)External are not usable in many ways regular Oracle tables.

2.)You cannot perform any DML operations on external tables other than table creation.

3.)You can't create an index on an external table. 

Enjoy:-)

Remap data in datapump

Remap data lets you export the data and modify the data based on a remapping scheme. The remap_data parameter uses a user defined package/function to alter the data.

DEMO:-

Create user "gg"

SQL> create user gg identified by gg;

User created.

SQL> grant dba to gg;

Grant succeeded.

SQL> conn gg/gg@delhi

Connected.

Create table "dp_test" in "gg"

SQL> create table dp_test (name varchar2(10),age number);

Table created.

SQL> insert into dp_test values('&a',&25);

Enter value for a: a

Enter value for 25: 10

old   1: insert into dp_test values('&a',&25)

new   1: insert into dp_test values('a',10)

1 row created.

SQL> /

Enter value for a: b

Enter value for 25: 15

old   1: insert into dp_test values('&a',&25)

new   1: insert into dp_test values('b',15)

1 row created.

SQL> /

Enter value for a: c

Enter value for 25: 22

old   1: insert into dp_test values('&a',&25)

new   1: insert into dp_test values('c',22)

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dp_test;

NAME              AGE

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

a                  10

b                  15

c                  22

Create package: -

SQL>  create or replace package add_age as

  2   function modify_age (roll_in number) return number;

  3   end;

  4  /

Package created.

SQL>  create or replace package body gg.add_age as

  2   function modify_age (roll_in number) return number

  3   as

  4   roll_out number;

  5   begin

  6   roll_out := roll_in + 10;

  7   return(roll_out);

  8   end;

  9   end;

 10

 11  /

Package body created.

Create another user called "scott1".

SQL> create user scott1 identified by tiger1;

User created.

SQL> grant dba to scott1;

Grant succeeded.

Take export of table "dp_test".

SQL> host expdp gg/gg@delhi dumpfile=dp_test.dmp logfile=dp_test.log tables=gg.dp_test remap_data=gg.dp_test.age:gg.add_age.modify_age

Export: Release 11.2.0.1.0 - Production on Wed Sep 28 16:19:47 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "GG"."SYS_EXPORT_TABLE_01":  gg/********@delhi dumpfile=dp_test.dmp logfile=dp_test.log tables=gg.dp_test remap_data=gg.dp_test.age:gg.add_age.modify_age

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "GG"."DP_TEST"                              5.429 KB       3 rows

Master table "GG"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for GG.SYS_EXPORT_TABLE_01 is:

  D:\APP\BISHWANATH\ADMIN\DELHI\DPDUMP\DP_TEST.DMP

Job "GG"."SYS_EXPORT_TABLE_01" successfully completed at 16:19:58

Now, import table "dp_test" in scott1 schema.

SQL> host impdp scott1/tiger1  dumpfile=dp_test.dmp logfile=dp_testimp.log remap_schema=gg:scott1 

Import: Release 11.2.0.1.0 - Production on Wed Sep 28 16:21:33 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTT1"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTT1"."SYS_IMPORT_FULL_01":  scott1/******** dumpfile=dp_test.dmp logfile=dp_testimp.log remap_schema=gg:scott1 table_exists_action=append

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTT1"."DP_TEST"                          5.429 KB       3 rows

Job "SCOTT1"."SYS_IMPORT_FULL_01" successfully completed at 16:21:37

SQL> conn scott1/tiger1

Connected.

Check the data of "dp_test"

SQL> select * from dp_test;

NAME              AGE

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

a                  20

b                  25

c                  32

As you see values in column "AGE" is incremented by 10.

Enjoy:-)

Advantages and Disadvantages of Datapump

Advantages:

===> Better control on the job running – it provides features like start,  stop, restart

===> Improved performance because of It is server side technology with parallel streams option

===> Using the parallel streams option, data pump can backup large volume of data quickly

===> Data pump is 15-50% faster than the conventional export import.

===> It has the ability to estimate the job times

===> Failed jobs can be restarted

===> Using exclude/include option we can perform fine-grained object selection

===> Backup jobs can be monitored

===> It has the remapping capabilities

===> It supports the export/import operations over the network. The NETWORK_LINK parameter initiate the export using a 

database link

===> Using “Query” parameter DBA can extract the data from tables like SELECT

“Content” parameter gives the flexibility for what to import/export. For example Metadata only, data or both

===> It supports full range of data types

===> It supports cross platform compatibility

===> No need to specify the buffer size like in normal exp/imp

===> It has its own performace tuning features

===> V$session_longops view can be used for the time estimation for the data pump jobs

===> It supports interactive mode that allows the dba to monitor or interact with ongoing jobs

===> Dumps will be in compressed

===> Data can be encrypted

===> XML schemas and XML type is supported by the Data Pump

Disadvantages:

===> Export cannot be taken to tape

===> Import will work only with Oracle 10g or above

===> Cannot use with Unix pipes

Related Views:

DBA_DATAPUMP_JOBS

USER_DATAPUMP_JOBS

DBA_DIRECTORIES

DATABASE_EXPORT_OBJECTS

SCHEMA_EXPORT_OBJECTS

TABLE_EXPORT_OBJECTS

DBA_DATAPUMP_SESSIONS

Enjoy:-)

Difference between Conventional path and Direct Path

Conventional path means that a SQL INSERT statement is used to load data into tables. Direct path deals with data much faster than conventional path. Direct path achieves this performance gain by inserting data directly, bypassing the SQL command processing layer and saves on data copies whenever possible.

This means you don't get any redo created however and once your load is complete you should backup immediately in order to have your data safe.

Lets elaborate them!

--Conventional path Export. 

Conventional path Export uses the SQL SELECT statement to extract data from tables. Data is read from disk into the buffer cache, and rows are transferred to the evaluating buffer. The data, after passing expression evaluation, is transferred to the Export client, which then writes the data into the export file. 

--Direct path Export. 

When using a Direct path Export, the data is read from disk directly into the export session's program global area (PGA): the rows are transferred directly to the Export session's private buffer. This also means that the SQL command-processing layer (evaluation buffer) can be bypassed, because the data is already in the format that Export expects. As a result, unnecessary data conversion is avoided. The data is transferred to the Export client, which then writes the data into the export file. 

Enjoy:-)