DATAPUMP CONCEPTS & SCENARIO

Important Datapump view "dba+datapump_jobs"
Common parameters IN(Traditional Export vs Expdp)

FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
PARFILE
QUERY
TABLES
TABLESPACES
TRANSPORT_TABLESPACES(exp value is Y/N, expdp value is name of the tablespace)

Common parameters IN(Traditional Import vs Impdp)

FULL
HELP
PARFILE
QUERY
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACES

Comparing (Traditional Export vs  Expdp) parameters

Export	Expdb
FEEDBACK	STATUS
FILE	DUMPFILE
LOG	LOGFILE
OWNER	SCHEMAS
TTS_FULL_CHECK	TRANSPROT_FULL_CHECK

Comparing (Traditional Import vs  Impdp) parameters

Import	Importdp
DATAFILE	TRANSPORT_DATAFILES
DESTROY	REUSE_DATAFILES
FEEDBACK	STATUS
FILE	DUMPFILE
FROMUSER	SCHEMAS, REMAP_SCHEMAS
IGNORE	TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)
LOG	LOGFILE
INDEXFILE, SHOW	SQLFILE
TOUSER	REMAP_SCHEMA

Parameters in expdp Utility

ATTACH Attach the client session to existing data pump jobs
ATTACH [=[schema_name.]job_name]

COMPRESSION  Specifies which data to compress before writing to the dump file set.
COMPRESSION={ALL | DATA_ONLY | METADATA_ONLY | NONE}

CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)
CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

DATA_OPTIONS   Default: There is no default. If this parameter is not used, then the special data handling options it provides simply do not take effect.

DIRECTORY   Default: DATA_PUMP_DIR   Location to write the dump file and log file.

DUMPFILE     Default: we have to give  exp.dmp

ENCRYPTION  The default value depends upon the combination of encryption-related parameters that are used
ENCRYPTION = {ALL | DATA_ONLY | ENCRYPTED_COLUMNS_ONLY | METADATA_ONLY | NONE}

ENCRYPTION_ALGORITHM   Specifies which cryptographic algorithm should be used to perform the encryption
ENCRYPTION_ALGORITHM = { AES128 | AES192 | AES256 }

ENCRYPTION_MODE  The default mode depends on which other encryption-related parameters are used. Specifies the type of security to use when encryption and decryption are performed.
ENCRYPTION_MODE = { DUAL | PASSWORD | TRANSPARENT }

ENCRYPTION_PASSWORD   Default: There is no default; the value is user-provided. Specifies a password for encrypting encrypted column data, metadata, or table data in the export dumpfile. This prevents unauthorized access to an encrypted dump file set.

ESTIMATE Show how much disk space each table in the export job consumes.
ESTIMATE={BLOCKS | STATISTICS}

ESTIMATE_ONLY It estimate the space, but does not perform export
ESTIMATE_ONLY={y | n}

EXCLUDE List of objects to be excluded

FILESIZE  Default: 0 (equivalent to the maximum size of 16 terabytes).Specifies the maximum size of each dump file. If the size is reached for any member of the dump file set, then that file is closed and an attempt is made to create a new file, if the file specification contains a substitution variable or if additional dump files have been added to the job.
FILESIZE=integer[B | KB | MB | GB | TB]

INCLUDE List of jobs to be included

JOB_NAME Name of the export job

KEEP_MASTER Specify Y not to drop the master table after export

NETWORK_LINK Specify database dblink to export from remote database

NOLOGFILE Specify Y if you do not want to create log file
NOLOGFILE={y | n}

PARALLEL  Specify the maximum number of threads for the export job. also help to split the dumpfile into number dump-file set

QUERY  Default: There is no default. Allows you to specify a query clause that is used to filter the data that gets exported.
QUERY = [schema.][table_name:] query_clause

VERSION objects that are incompatible with the specified version will not be exported.

SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.


Parameters in impdp Utility

CONTENT={ALL | DATA_ONLY | METADATA_ONLY}

EXCLUDE List of objects to be excluded

ENCRYPTION_PASSWORD It is required on an import operation if an encryption password was specified on the export operation.
 

FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.

FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.

INCLUDE List of jobs to be included

NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.

REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.

REMAP_SCHEMA Loads objects to a different target schema name.

REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.

TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.

TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.
For instance, TRANSFORM=storage:N:table

Data Pump differnt Export Levels in command line:

o   Full Export Level

o   Schema Level

o   Table Level

o   Tablespace  Level

o   Transportable Tablespace Level

EXAMPLE 1 :          

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY
USERS01 tablespace need to be export  from cust_schema in source_db And  import to sample table in USERS02 tablespace from cust_schema in Target_db	SCHEMAS	REMAP_TABLESPACE TABLE_EXISTS_ACTION	REMAP_TABLESPACE: we importing the data to different tablespace. TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data
Exclude few objects during the import		EXCLUDE	And exclude few objects and import  into the table named "sample" in USERS02 tablespace

Expdp parfile information	Import parfile information
userid=system/password@alias_name dumpfile=schemaexpdb.dmp logfile=schemaexpdb.log directory=dump_dir schemas=cust_schema	userid=system/password@alias_name dumpfile=schemaexpdb.dmp logfile=schemaimpdb.log directory=dumplocation table_exists_action=replace remap_tablespace=res:users exclude=sequence,view,package,cluster,table:"in('sample')"

EXAMPLE 2.

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	USERID DUMPFILE, LOGFILE DIRECTORY	USERID DUMPFILE, LOGFILE DIRECTORY
Here going to export the sample table FROM cust_schema from the instance called source . And import into sample table FROM cust_schema from the instance called target	SCHEMAS	TABLE_EXISTS_ACTION	TABLE_EXISTS_ACTION: the table already present in the source location. so replacing with new data

Expdp parfile information	Import parfile information
userid=system/password@alias_name dumpfile=table_expdb.dmp logfile=table_expdb.log directory=dump_location tables= cust_schema.sample	userid=system/password@alias_name dumpfile=table_expdb.dmp logfile=tabimpdb.log directory=dump_location table_exists_action=REPLACE

EXAMPLE 3:                                     

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY
Export the sample tables from cust_schema. this sample has partitions now we are going to export the contents in partition1 and partition2	TABLES	TABLE_EXISTS_ACTION	TABLE_EXISTS_ACTION: APPEND   the content of  sample table in the target database (NOTE: partition information not required if target has same table name with same partition
		sql> delete sample where deptno=1; sql> delete sample where deptno=2;	NOTE: Before overwrite the partition in target database we need to manually delete partition and import the dump file set to the target database. EX: If we want to overwrite the exported data in target database, then we need to delete value in the partition has sample table information of  deptno in(1,2).

Expdp parfile information	Import parfile information
userid=system/password@alias_name dumpfile=partition_tab_expdb.dmp logfile=partition_tab_expdb.log directory=dump_location tables=cust_schema.sample:partition1, cust_schema.sample:partition2	userid=system/password@alias_name dumpfile=partition_expdb.dmp logfile=tab_impdb.log directory=dump_location table_exists_action=append

EXAMPLE 4:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY
To export only table in cust_schema at source instance and import into the target instace.	CONTENT=table	TABLE_EXISTS_ACTION	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

Expdp parfile information	Import parfile information
userid=system/password@source dumpfile=schema_tab_expdb.dmp logfile= schema_tab_expdb.log directory=dump_location include=table schemas=cust_schema	userid=system/password@target dumpfile=schema_tab_expdb.dmp logfile=schema_tab_impdb.log directory=dump_location table_exists_action=replace

EXAMPLE 5:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY
Export the rows belongs to the sampdept,  sample need to export the value of dept 10,20. Need to export only the content is  data of the table.	INCLUDE, QUERY, CONTENT, SCHEMAS	TABLE_EXISTS_ACTION, QUERY SCHEMAS,	TABLE_EXISTS_ACTION: APPEND   the content of  sample table in the target database QUERY: we have the content of samdept 10,20 but we need to import only the content of samdept 20. (note:always check the expdp logfile what content are exported)

Expdp parfile information	Import parfile information
userid=system/password@source dumpfile=data_filter_expdb.dmp logfile=data_filter_expdb.log directory=dumplocation schemas=cust_schema query="where deptno in(10,20)" content=data_only include=table:"in('SAMPLE','SAMDEPT')"	userid=system/password@target dumpfile=data_filter_expdb.dmp logfile=data_filter_impdb.log directory=dumplocation schemas=cust_schema query="where deptno = 20" table_exists_action=APPEND

EXAMPLE 6:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY	In using impdp multiple files can be detected automatically detected by passing the value  "%U" to dumpfile
Export the cust_schema schema from source database and split the dump file sets into 70MB in size. Import the dump file into target database. so this expdp will create the backupsets in 70mb in size. by using "%U" in dumpfile it will automatically create the sequence dumpset EX: schema_exp_split_01.dmp, schema_exp_split_02.dmp till create the of total size of dump set.	INCLUDE, SCHEMAS, FILESIZE	TABLE_EXISTS_ACTION, REMAP_TABLESPACE	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data REMAP_TABLESPACE=tbs1:tbs01 Import the value to new tablespace

Expdp parfile information	Import parfile information
userid=system/password@alias_name dumpfile=schema_exp_split_%U.dmp logfile=schema_exp_split.log directory=dump_location schemas=cust_schema include=table filesize=70M	userid=system/password@alias_name dumpfile=schema_exp_split_%U.dmp logfile=schema_imp_split.log directory=dump_location table_exists_action=replace remap_tablespace=tbs1:tbs01

EXAMPLE 7:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY	In using impdp multiple files can be detected automatically detected by passing the value  "%U" to dumpfile
Export the cust_schema schema from source database and split the dump file sets into 6 parts of total size of export data. Import the dump file into target database. so this expdp will create the backupsets in total size of six parts. by using "%U" in dumpfile it will automatically create the sequence dumpset EX: schema_exp_split_01.dmp, schema_exp_split_02.dmp till create the dumpset upto schema_exp_split_06.dmp	INCLUDE, SCHEMAS, PARALLEL	TABLE_EXISTS_ACTION, REMAP_TABLESPACE	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data REMAP_TABLESPACE=tbs1:tbs01 Import the value to new tablespace

Expdp parfile information	Import parfile information
userid=system/password@source dumpfile=schema_exp_split_%U.dmp logfile=schema_exp_split.log directory=dump_location schemas=cust_schema include=table parallel=6	userid=system/password@target dumpfile=schema_exp_split_%U.dmp logfile=schema_imp_split.log directory=dump_location table_exists_action=replace remap_tablespace=tbs1:tbs01 exclude=grant

EXAMPLE 8

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY	In using impdp multiple files can be detected automatically detected by passing the value  "%U" to dumpfile
Export the cust_schema schema from source database and split the dump file sets into 6 parts of total size of export data in different loaction. Import the dump file into target database. So this expdp will create the backupsets in total size of six parts. by using "%U" in dumpfile it will automatically create the sequence dumpset EX: schema_exp_split_01.dmp, schema_exp_split_02.dmp till create the dumpset upto schema_exp_split_06.dmp	INCLUDE, SCHEMAS, FILESIZE	TABLE_EXISTS_ACTION,	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

Expdp parfile information	Import parfile information
userid=system/password@alias_name logfile=schemaexp_split.log dumpfile=dumplocation1:schemaexp_%U.dmp, dumplocation2:schemaexp_%U.dmp, dumplocation3:schemaexp_%U.dmp directory=dump_location schemas=user_schema include=table filesize=100M	Impdp parfile content: userid=system/password@alias_name logfile=schemaimp_split.log dumpfile=dump1:schemaexp_%U.dmp,dump2:schemaexp_%U.dmp,dump3:schemaexp_%U.dmp directory=dumplocation table_exists_action=replace

EXAMPLE :9.a
Creating the database link (for destination system schema)
Before performing exporting the data over a network link it is required  to create a database link in the DESTINATION database first. The link can be created by either destination locations's user schema or in the SYSTEM schema. If the link is created in SYSTEM schema the user will be created automatically with the same grants during the import.

Creating the link as SYSTEM schema:

create database link source_link connect to system identified by manager using 'source';
(Note: The database connection string for the database “source” is defined in the local TNSNAMES.ORA

Creating the link as USER schema Creating the database link (in destination user schema):

For creating the database link in the destination user schema you need to create the user first. If done create the link with:

create database link source_link connect to test identified by user using 'source';

Warning raise when mixing privileged user and non-privileged users
There is one warning when mixing privileged (e.g. DBA users) with non privileged users data pump will complain about that:

ORA-31631: privileges are required
ORA-39149: cannot link privileged user to non-privileged user
You need to look out for database links created in the SYS or SYSTEM schema pointing to a non-DBA-user in the remote database or vice versa.

For example the following link created the in SYSTEM schema wont work because SYSTEM is a dba user and test is not:

create database link source_link connect to test identified by test using 'source';

EXAMPLE:9 (b)

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	DUMPFILE, LOGFILE DIRECTORY	In using impdp multiple files can be detected automatically detected by passing the value  "%U" to dumpfile
Currently working in source database. but taking the export from destination location and storing the dump file set in source location using network_link this method of export is possible. But users of the source and destination must have identical privileges. if users privileges fail to match it throws an error. Using tns entry we can achieve this job	INCLUDE, SCHEMAS, NETWORK_LINK	TABLE_EXISTS_ACTION,	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

If user privilage fails to match we get this error:

ORA-31631: privileges are required

ORA-39149: cannot link privileged user to non-privileged user

You need to look out for database links created in the SYS or SYSTEM schema pointing to a non-DBA-user in the remote database or vice versa.

Expdp parfile information	Import parfile information
userid=user/user@source logfile=netwrok_exp.log directory=dump_location dumpfile=network_exp.dmp schemas=user include=table network_link=target	userid=system/password@source logfile=network_imp.log directory=dump_location dumpfile=network_exp.dmp table_exists_action=replace

EXAMPLE 10:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition		LOGFILE, DIRECTORY
Currently working in target database. So without using the export the dump file set here directly taking import the data  from target location to source location. using  network_link  this  method is possible.		TABLE_EXISTS_ACTION,	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

(NOTE: From the target server contact the source server the extract and import the data into source database. this methos is very useful when we have no space to create dump file set. then we can implement this operation over network.)

Import parfile information

userid=user/user@target network_link=source logfile=network_imp.log directory=dumplocation table_exists_action=replace

EXAMPLE 11:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	LOGFILE, DIRECTORY
Currently working in target database. But performing  the export in target database itself. Importing the dump file set from target to source. without moving the dumpfile in the source location.  EX: imagine we have no space on source database but target have enough space so to utilize the target space without consuming the source space we can achieve this method	INCLUDE, SCHEMAS,	TABLE_EXISTS_ACTION, NETWORK_LINK	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

Expdp parfile information	Import parfile information
userid=user/user@target logfile=netwrok_exp.log dumpfile=network_exp.dmp directory=dump_location schemas=cust_schema include=table	userid=system/password@target logfile=network_imp.log dumpfile=network_exp.dmp directory=dump_location table_exists_action=replace remap_schema=user:user01

EXAMPLE 12:

EXPDP explanation	SOURCE DB-EXPDP	TARGET DB-IMPDP	IMPDP explanation
Common for most of the condition	DUMPFILE, LOGFILE DIRECTORY	LOGFILE, DIRECTORY
SAMPLE parameter allows you to export subsets of data by specifying the percentage of data to be sampled and exported. The sample_percent indicates the probability that a block of rows will be selected as part of the sample. if table name is not specified then entire job 40% percentage value will exported	TABLE, SAMPLE,	TABLE_EXISTS_ACTION,	TABLE_EXISTS_ACTION:REPLACE the table already present in the source location. so replacing with new data

Expdp parfile information	Import parfile information
userid=system/password@source dumpfile=sample_expdb.dmp logfile= sample_expdb.log directory=dump_location tables=user.sample SAMPLE=40	userid=system/password@target dumpfile=sample_expdb.dmp logfile= sample_impdb.log directory=dump_location table_exists_action=replace

 

Prerequisite of active dataguard setup

Step 1:  ON PRIMARY DATABASE (SURIYA)
Check the status  latest sequence generated in the primary database.
SQL> select status,instance_name,database_role from v$instance,v$database;
STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN         suriya         PRIMARY

SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
 246

Step 2:  ON PHYSICAL STANDBY (SURIYADR)
Check the status of the  latest sequence applied on the physcial standby database.

SQL> select status,instance_name,database_role from v$instance,v$database;
STATUS       INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
MOUNTED      suriyadr           PHYSICAL STANDBY


SQL> select max(sequence#) from v$archived_log;

MAX(SEQUENCE#)
--------------
           246

Step 3: ON PRIMARY DATABASE (SURIYA)
Check if the Managed Recovery Process (MRP) is active on the physical standby database.

 SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CONNECTED             0
ARCH      CLOSING             245
ARCH      CLOSING             244
ARCH      CONNECTED             0
LNS       WRITING             247

Step 4: ON PHYSICAL STANDBY (SURIYADR)
Cancel the MRP on the physical standby database and open the standby database so standby database  opened in the READ-ONLY Mode.

SQL> alter database recover managed standby database cancel;

Database altered.

SQL> alter database open;

Database altered.

SQL> select status,instance_name,database_role,open_mode from v$database,v$instance;

STATUS       INSTANCE_NAME    DATABASE_ROLE    OPEN_MODE
------------ ---------------- ---------------- --------------------
OPEN                   suriyadr                 PHYSICAL STANDBY      READ ONLY

Step 6: ON PHYSICAL STANDBY (SURIYADR)
Now start the Media Recovery Preocessor (MRP) on the physical standby database.

SQL> alter database recover managed standby database disconnect from session;

Database altered.

SQL> select process,status,sequence# from v$managed_standby;

PROCESS   STATUS        SEQUENCE#
--------- ------------ ----------
ARCH      CLOSING             244
ARCH      CONNECTED             0
ARCH      CONNECTED             0
ARCH      CLOSING             246
RFS       IDLE                  0
RFS       IDLE                  0
RFS       IDLE                247
MRP0      WAIT_FOR_LOG        247


Finally MRP is active and is waiting for the log sequence 247 on physical standby location and
also the physical standby database is opened in READ-ONLY mode. Now report from physical standby database can be fetched by the users.

Performing the switchover in 11gr2 datagurad steps

1) There are few steps we need to check before performing the switchover in dataguard environment 
a)If we are using the OEM Enable a blackout for primary database in enterprise manager.
b)check the crontab jobs for Primary database need to disabled.
c)Check for executing backups/exports and cancel.
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database

2)Verify that it is possible to perform a switchover operation On the primary database

On the primary database the switchover_status column of v$database check which is possible to perform a switchover operation

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

TO STANDBY

-In order to perform a switchover all sessions to the database need to be disconnected. This process has been automated with the “with session shutdown” clause that has been added to the alter database commit to switchover command.

SWITCHOVER_STATUS 

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

 SESSIONS ACTIVE 

 -Then you should either disconnect all sessions manually (or) when performing step 3 you should append the “with session shutdown” clause.

For example:

 SQL> alter database commit to switchover to standby with session shutdown;

(Note: that the clause "with session shutdown" also works with the switchover to primary command 

Ex: alter database commit to switchover to primary with session shutdown;)

SWITCHOVER_STATUS 

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

NOT ALLOWED         

-Either this is a standby database and the primary database has not been switched first, or this is a primary database and there are no standby databases. 

SWITCHOVER_STATUS 

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

SESSIONS ACTIVE                 - 

-Indicates that there are active SQL sessions attached to the primary or                                               standby database that need to be disconnected before the switchover operation is permitted.

SWITCHOVER_STATUS 

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

SWITCHOVER PENDING - 

-This is a standby database and the primary database switchover request has been received but not processed. 

SWITCHOVER_STATUS 

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

SWITCHOVER LATENT - 

-The switchover was in pending mode, but did not complete and went back to the primary database.

SWITCHOVER_STATUS 

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

TO PRIMARY                  

 - This is a standby database, with no active sessions, that is allowed to switch over to a primary database.

SWITCHOVER_STATUS 

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

TO STANDBY 

- This is a primary database, with no active sessions, that is allowed to switch over to a standby database.

SWITCHOVER_STATUS 

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

RECOVERY NEEDED 

- This is a standby database that has not received the switchover request.

During normal operations it is acceptable to see the following values for

SWITCHOVER_STATUS 

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

 SESSIONS ACTIVE (or) TO STANDBY. ( on the primary database)

 During normal operations  it is acceptable to see these values

SWITCHOVER_STATUS 

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

NOT ALLOWED (or) SESSIONS ACTIVE.  (on the standby database)

-During normal operations  it is acceptable to see these values 

3. Convert the primary database to the new standby:

SQL> alter database commit to switchover to physical standby ;

Database altered.

4. Shutdown the former primary and mount as a standby database:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2217792 bytes
Variable Size             528484544 bytes
Database Buffers          285212672 bytes
Redo Buffers                2486272 bytes

SQL> alter database mount standby database;

Database altered.

5. Defer the remote archive destination on the old primary:

SQL> alter system set log_archive_dest_state_3=defer;

6. Verify that the physical standby can be converted to the new primary:

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS

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

SWITCHOVER PENDING

(Note that if the status returns SESSIONS ACTIVE then you should append the with session shutdown clause to the command in step 7.

7. Convert the physical standby to the new primary:

SQL> alter database commit to switchover to primary;

Database altered.

8. Shutdown and startup the new primary:

SQL> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup

ORACLE instance started.
Total System Global Area  818401280 bytes
Fixed Size                  2217792 bytes
Variable Size             482347200 bytes
Database Buffers          331350016 bytes
Redo Buffers                2486272 bytes
Database mounted.
Database opened.

9. Enable remote archiving on the new primary to the new standby:

SQL> alter system set log_archive_dest_state_2=enable;

10. Start managed recover on the new standby database:

SQL> recover managed standby database disconnect;

Media recovery complete.

11) There are few steps we need to check after performing the switchover in dataguard environment 
a)If we are using the OEM check the status of  primary database in enterprise manager.
b)check the crontab jobs for Primary database need to enabled
c)Check for executing backups/exports and start as per schedule
d)Identify the log gap if gap present resolve the gap
e)From Primary and Standby database check the list of last log should be applied on both database