Oracle 10g Data Guard – Quick Switchover with Physical Standby Database

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Once the standby database is setup using Data Guard and works properly, you may want to test switchover, or perform switchover to reduce primary database downtime during OS upgrades or hardware upgrades. (To set up a Physical Standby database, please refer to my OraFAQ blog "Oracle 10g – Manually Create a Physical Standby Database Using Data Guard".)

A switchover allows the primary database to switch roles with its standby database. There is no data loss during a switchover. You can switch back to the original Primary database later by performing another switchover.

In case of primary database failure, you will need to perform failover to transition the standby database to the primary role. After a failover, the original primary database can no longer participate in the Data Guard configuration. So if the original Primary database is still accessible, you should always consider a switchover first.

This document only talks about switchover involving physical standby database. In this example, the original primary data is called PRIM and the original standby database is called STAN.

I. Before Switchover:

1. As I always recommend, test the Switchover first on your testing systems before working on Production.

2. Verify the primary database instance is open and the standby database instance is mounted.

3. Verify there are no active users connected to the databases.

4. Make sure the last redo data transmitted from the Primary database was applied on the standby database. Issue the following commands on Primary database and Standby database to find out:

SQL>select sequence#, applied from v$archvied_log;

Perform SWITCH LOGFILE if necessary.

In order to apply redo data to the standby database as soon as it is received, use Real-time apply.

II. Quick Switchover Steps

1. Initiate the switchover on the primary database PRIM:

SQL>connect /@PRIM as sysdba

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

2. After step 1 finishes, Switch the original physical standby db STAN to primary role;

Open another prompt and connect to SQLPLUS:

SQL>connect /@STAN as sysdba

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

3. Immediately after issuing command in step 2, shut down and restart the former primary instance PRIM:

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP MOUNT;

4. After step 3 completes:

- If you are using Oracle Database 10g release 1, you will have to Shut down and restart the new primary database STAN.

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

- If you are using Oracle Database 10g release 2, you can open the new Primary database STAN:

SQL>ALTER DATABASE OPEN;

STAN is now transitioned to the primary database role.

5. On the new primary database STAN, perform a SWITCH LOGFILE to start sending redo data to the standby database PRIM.

SQL>ALTER SYSTEM SWITCH LOGFILE;

Reference:

1. Oracle Data Guard Concepts and Administration 10g Release 2 (10.2), B14239-04;

2. Switchover and Failover Best Practices: Oracle Data Guard 10g release 2 by Oracle.

Get Oracle Certifications for all Exams
Free Online Exams.com

How To Open The Standby Database When The Primary Is Lost

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Startup Mount

[oracle@rac2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Feb 4 19:51:12 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area  218103808 bytes

Fixed Size                  1260984 bytes

Variable Size             184549960 bytes

Database Buffers           29360128 bytes

Redo Buffers                2932736 bytes

Database mounted.

Check Status

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE

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

MOUNTED    MAXIMUM PERFORMANCE  PHYSICAL STANDBYHow To Open The Standby Database When The Primary Is Lost

3/5  2/4/2009 

Recover if you have logs to apply

In this example the primary is lost and I don't have more archived logs to apply:

SQL> RECOVER STANDBY DATABASE;

ORA-00279: change 794348 generated at 12/29/2008 12:23:02 needed for thread 1

ORA-00289: suggestion :

/u01/app/oracle/oradata/dgfdb/archive/1_49_633452428.dbf

ORA-00280: change 794348 for thread 1 is in sequence #49

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

ORA-00308: cannot open archived log

'/u01/app/oracle/oradata/dgfdb/archive/1_49_633452428.dbf'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

SQL> !ls -l /u01/app/oracle/oradata/dgfdb/archive/

total 31072

-rw-r-----  1 oracle oinstall  1158656 Dec 29 11:31 1_37_633452428.dbf

-rw-r-----  1 oracle oinstall  7385600 Dec 29 11:31 1_38_633452428.dbf

-rw-r-----  1 oracle oinstall  4941824 Dec 29 11:31 1_39_633452428.dbf

-rw-r-----  1 oracle oinstall 13739008 Dec 29 11:31 1_40_633452428.dbf

-rw-r-----  1 oracle oinstall  2272256 Dec 29 11:50 1_41_633452428.dbf

-rw-r-----  1 oracle oinstall     1024 Dec 29 11:51 1_42_633452428.dbf

-rw-r-----  1 oracle oinstall    89088 Dec 29 11:51 1_43_633452428.dbf

-rw-r-----  1 oracle oinstall  1847296 Dec 29 12:18 1_44_633452428.dbf

-rw-r-----  1 oracle oinstall   135680 Dec 29 12:18 1_45_633452428.dbf

-rw-r-----  1 oracle oinstall    67584 Dec 29 12:19 1_46_633452428.

-rw-r-----  1 oracle oinstall    34816 Dec 29 12:22 1_47_633452428.dbf

-rw-r-----  1 oracle oinstall     2048 Dec 29 12:22 1_48_633452428.dbf

Finish the Recovery process

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Activate the Standby Database

SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the new status

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE

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

MOUNTED    MAXIMUM PERFORMANCE  PRIMARY

Open the Database

SQL> ALTER DATABASE OPEN

Database altered.

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE  PROTECTION_MODE      DATABASE_ROLE

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

READ WRITE MAXIMUM PERFORMANCE  PRIMARY

Get Oracle Certifications for all Exams
Free Online Exams.com

SQL Server Snapshots

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

A snapshot is a read-only copy of another database, made at a point in time. Any changes to the original database cause the version of the data when the snapshot was taken to get written to the file used by the snapshot. Therefore, there's a performance hit involved, but it can be very useful for knowing exactly what your database looked like at some point in the past (when you told the snapshot to be created).

It's definitely worth noting that the snapshot contains no data of its own when first created, as it can reference the original database for it, at least until the original database is changed.

When a snapshot is first created, it is an empty shell that delegates all queries (a snapshot is read only) to the original database.

As changes are made to the original database, the pages involved are copied to the snapshot. Queries of the snapshot at this point will be performed on a logical database that is the result of layering the pages in the snapshot over those in the original database.

The effect is that the snapshot appears to be a complete copy of the original database that was made at the same time as the snapshot was created.

One scenario in which this can be useful is in deploying changes. The snapshot can be a very inexpensive form of insurance if something goes wrong. Assuming that only a subset of the pages within the original database were modified during the deployment, only that subset of the pages will need to be copied back from the snapshot to the original database during a restore

Get Oracle Certifications for all Exams
Free Online Exams.com

The “NOT FOR REPLICATION” option in SQL Server Replication:

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

The NOT FOR REPLICATION option allows you to specify that the following database objects are

treated differently when a replication agent performs an operation:

* Foreign key constraints

The foreign key constraint is not enforced when a replication agent performs an insert, update, or

delete operation.

* Check constraints

The check constraint is not enforced when a replication agent performs an insert, update, or delete

operation.

* Identity columns

The identity column value is not incremented when a replication agent performs an insert

operation.

* Triggers

The trigger is not executed when a replication agent performs an insert, update, or delete

Get Oracle Certifications for all Exams
Free Online Exams.com

What is filtered index

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

A filtered index is an optimized nonclustered index, especially suited to cover queries that select

from a well-defined subset of data. It uses a filter predicate to index a portion of rows in the table.

A well-designed filtered index can improve query performance, reduce index maintenance costs,

and reduce index storage costs compared with full-table indexes.

Filtered indexes can provide the following advantages over full-table indexes:

Improved query performance and plan quality

Reduced index maintenance costs

Reduced index storage costs

Get Oracle Certifications for all Exams
Free Online Exams.com

How to set the instance to utilize one thread for searches which owns an estimated execution cost less than 6.

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Change the sp_configure cost threshold for parallelism option.

 

Use the cost threshold for parallelism option to specify the threshold at which Microsoft SQL Server creates and runs parallel plans for queries. SQL Server creates and runs a parallel plan for a query only when the estimated cost to run a serial plan for the same query is higher than the value set in cost threshold for parallelism. The cost refers to an estimated elapsed time in seconds required to run the serial plan on a specific hardware configuration.

 

Only set cost threshold for parallelism on symmetric multiprocessors.

Longer queries usually benefit from parallel plans; the performance advantage negates the additional time required to initialize, synchronize, and terminate the plan. The cost threshold for parallelism option is actively used when a mix of short and longer queries is executed. The short queries execute serial plans while the longer queries use parallel plans. The value of cost threshold for parallelism determines which queries are considered short, thus executing only serial plans.

 

In certain cases, a parallel plan may be chosen even though the query's cost plan is less than the current cost threshold for parallelism value. This is because the decision to use a parallel or serial plan, with respect to cost threshold for parallelism, is based on a cost estimate provided before the full optimization is complete.

 

The cost threshold for parallelism option can be set to any value from 0 through 32767. The default value is 5.

 

If your computer has only one processor, if only a single CPU is available to SQL Server because of the affinity mask configuration value, or if the max degree of parallelism option is set to 1, SQL Server ignores cost threshold for parallelism.

 

cost threshold for parallelism is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change cost threshold for parallelism only when show advanced options is set to 1. The setting takes effect immediately (without a server stop and

restart).

Get Oracle Certifications for all Exams
Free Online Exams.com

How to Create Full SQL Database backup commands

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Execute the BACKUP DATABASE statement to create the full database backup, specifying:

* The name of the database to back up.

* The backup device where the full database backup is written.

The basic Transact-SQL syntax for a full database backup is:

BACKUP DATABASE database

TO backup_device [ ,...n ]

[ WITH with_options [ ,...o ] ] ;

Option Description

database

Is the database that is to be backed up.

backup_device [ ,...n ]

Specifies a list of from 1 to 64 backup devices to use for the backup operation. You can specify a

physical backup device, or you can specify a corresponding logical backup device, if already

defined. To specify a physical backup device, use the DISK or TAPE option:

{ DISK | TAPE } =physical_backup_device_name

For more information, see Backup Devices.

WITH with_options [ ,...o ]

Optionally, specifies one or more additional options, o. For information about some of the basic

with options, see step 2.

2.

Optionally, specify one or more WITH options. A few basic WITH options are described here. For

information about all the WITH options, see BACKUP (Transact-SQL).

* Basic backup set WITH options:

{ COMPRESSION | NO_COMPRESSION }

In SQL Server 2008 Enterprise and later only, specifies whether backup compression is performed

on this backup, overriding the server-level default.

Get Oracle Certifications for all Exams
Free Online Exams.com