Performing Switchover from the primary to standby

Refer :
http://kb.dbatoolz.com/tp/2693.create_oracle_10gr2_standby_database_using_real_time_apply.html

Prerequisites to check before roles transition

1. Check the standby redolog files

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------
3 1 16 NO ACTIVE
4 0 0 YES UNASSIGNED
5 0 0 YES UNASSIGNED

2. Shows duration details on standby

SQL> COLUMN NAME FORMAT A18
SQL> COLUMN VALUE FORMAT A16
SQL> COLUMN TIME_COMPUTED FORMAT A24
SQL> SELECT * FROM V$DATAGUARD_STATS;
NAME VALUE TIME_COMPUTED
------------------ ---------------- ------------------------
apply finish time +00 00:00:02.4 15-MAY-2005 10:32:49
second(1)
interval
apply lag +00 0:00:04 15-MAY-2005 10:32:49
second(0)
interval
transport lag +00 00:00:00 15-MAY-2005 10:32:49
second(0)
interval

Switchovers Involving a Physical Standby Database

A switchover must be initiated on the current primary database and completed on the target standby database. The following steps describe how to perform a switchover.

Step 1 Verify it is possible to perform a switchover.

On the current primary database, query the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the primary database to verify it is possible to perform a switchover. For example:

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO STANDBY
The TO STANDBY value in the SWITCHOVER_STATUS column indicates that it is possible to switch the primary database to the standby role. If the TO STANDBY value is not displayed, then verify the Data Guard configuration is functioning correctly (for example, verify all LOG_ARCHIVE_DEST_n parameter values are specified correctly).
If the value in the SWITCHOVER_STATUS column is SESSIONS ACTIVE, perform the steps described in Section A.4, "Problems Switching Over to a Standby Database" to identify and terminate active user or SQL sessions that might prevent a switchover from being processed. If, after performing these steps, the SWITCHOVER_STATUS column still displays SESSIONS ACTIVE, you can successfully perform a switchover by appending the WITH SESSION SHUTDOWN clause to the ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY statement described in Step 2.

Step 2 Initiate the switchover on the primary database.

To change the current primary database to a physical standby database role, use the following SQL statement on the primary database:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY;
After this statement completes, the primary database is converted into a standby database. The current control file is backed up to the current SQL session trace file before the switchover. This makes it possible to reconstruct a current control file, if necessary.

Step 3 Shut down and restart the former primary instance.

Shut down the former primary instance, and restart and mount the database:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
At this point in the switchover process, both databases are configured as standby databases (see Figure 7-2).

Step 4 Verify the switchover status in the V$DATABASE view.

After you change the primary database to the physical standby role and the switchover notification is received by the standby databases in the configuration, you should verify if the switchover notification was processed by the target standby database by querying the SWITCHOVER_STATUS column of the V$DATABASE fixed view on the target standby database.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY

If the value in the SWITCHOVER_STATUS column is SESSIONS ACTIVE, perform the steps described in Section A.4, "Problems Switching Over to a Standby Database" to identify and terminate active user or SQL sessions that might prevent a switchover from being processed. If, after performing these steps, the SWITCHOVER_STATUS column still displays SESSIONS ACTIVE, you can proceed to Step 5, and append the WITH SESSION SHUTDOWN clause to the switchover statement. See Oracle Database Reference for information about other valid values for the SWITCHOVER_STATUS column of the V$DATABASE view

Step 5 Switch the target physical standby database role to the primary role.

You can switch a physical standby database from the standby role to the primary role when the standby database instance is either mounted in Redo Apply mode or open for read-only access. It must be in one of these modes so that the primary database switchover request can be coordinated. After the standby database is in an appropriate mode, issue the following SQL statement on the physical standby database that you want to change to the primary role:

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Step 6 Finish the transition of the standby database to the primary role.

The task you perform is dependent on if the physical standby database has ever been opened in read-only mode:

If the physical standby database has not been opened in read-only mode since the last time it was started, issue the SQL ALTER DATABASE OPEN statement to open the new primary database:

SQL> ALTER DATABASE OPEN;
Then, go to step 7.
If the physical standby database has been opened in read-only mode since the last time it was started, you must shut down the target standby database and restart it:

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
The target physical standby database has now undergone a transition to the primary database role. See Section 5.4.1, "Specifying Role-Based Destinations with the VALID_FOR Attribute" and Chapter 14, "LOG_ARCHIVE_DEST_n Parameter Attributes" for information about using the LOG_ARCHIVE_DEST_n VALID_FOR attribute to ensure the Data Guard configuration operates properly after a role transition.
Note: There is no need to shut down and restart other standby databases (not involved in the switchover) that are online at the time of the switchover. These standby databases will continue to function normally after the switchover completes.

Step 7 If necessary, restart log apply services on the standby databases.
For the new physical standby database and for each other physical or logical standby database in the Data Guard configuration, if log apply services were not previously configured to continue operating through a switchover, use an appropriate command to restart log apply services. See Chapter 6, "Log Apply Services" for more information about how to configure and start log apply services.

Step 8 Begin sending redo data to the standby databases.
Issue the following statement on the new primary database:

SQL> ALTER SYSTEM SWITCH LOGFILE;
Refer: http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/role_management.htm#i1033702

Flashback Database Technology Article

FLASHBACK RECOVERY

Introduction:

Flashback recovery feature is used to rollback the database to the prior point in time or system change number. Flashback Database is a new approach to point-in-time (PIT) database recovery. This incomplete recovery strategy can be used to recover a database that has been logically corrupted due to human error. Introduced in 10g, it is designed to maximize availability by reducing recovery time.

Incomplete Recovery:

Incomplete recovery is recovery of the database to a prior state. There are two steps to this process: restoring data and forward-recovering transaction activity to a desired time. The major difference between traditional recovery and Flashback Database is that traditional recovery begins by restoring all data files prior to the desired recovery time while Flashback Database works backwards by restoring blocks changed after the corruption. To put this in perspective, let's consider a 10 TB database with 1 MB of corruption. Traditional recovery begins by restoring 10 TB of application data while Flashback Database backs out 1 MB of application data to arrive at a point before the corruption. Restoring all data files from a backup and rolling forward redo logs to the desired point-in-time accomplish recovery. Recovery time is proportional to the size of the database, not the amount of changes that need to be backed out. The mean time to recover (MTTR) actually increases as the database grows in size. But in this feature the time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

Pre-requisites:
You must have either the FLASHBACK ANY TABLE or the more specific FLASHBACK object privilege on the table you want to recover.
Database must be in Archivelog mode to enable Flashback.
The feature is enabled in database MOUNT EXCLUSIVE mode.
The following parameters must be set before enabling the feature

• DB_FLASHBACK_RETENTION_TARGET
• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE.

Configuring Flashback Database:

Flash Recovery Area:
FRA is a disk location for holding recovery-related files. For Flashback Database, a new background process called Recovery Writer (RVWR) periodically writes before images of data blocks from the SGA flashback buffer to disk as Flashback Logs in the FRA. Flashback Logs are automatically managed within the FRA by Oracle. The cost of Flashback Logs is measured in space and performance. Space will be a factor of the write intensity of the database.

Alter system set db_recovery_file_dest=
'C:\oracle\product\10.2.0\flash_recovery_area' scope=both;

Alter system set db_recovery_file_dest_size = 10G scope=both;

Flashback Retention Target:
This initialization parameter, measured in minutes, represents how many days we need to keep the logs. After that period, the files are automatically deleted if more space is required. Its value translates into the amount and duration of Flashback Logs in the FRA. It is important to understand that this retention is not guaranteed. If the FRA is in need of space, Flashback Logs may be automatically deleted before their target retention. With the retention set, Flashback Database can be enabled.

SQL> startup mount;
Database mounted.
SQL> alter system set db_flashback_retention_target = 1440 scope=both;
System altered.
SQL> alter database flashback on;
Database altered.
SQL> alter database open;
Database altered.

Flashback Logs:
Database retains a copy of the modified database blocks in the logs in
Flashback recovery area. Flashback logs have a file extension of .FLB. The logs are automatically deleted when space needed.

Flashback Technology Levels:

• Flashback database enables you to take the entire database to a past point in time (using flashback logs).
• Flashback drop lets you retrieve accidentally dropped tables and indexes (using the recycle bin).
• Flashback table lets you recover a table to a time in the past (using undo data).
• Flashback query lets you query and restore data rows to a point in time (using undo data). (Select * from emp as of timestamp to_timestamp (.))
• Flashback versions query The Flashback Versions Query feature enables you to use the VERSIONS clause to retrieve all the versions of rows that exist between two points in time or between two SCNs.
• Flashback Transaction query The flashback transaction query table shows the DML statements that used against the tables.

Advantages:

• Restoring data files from backup sets and image copies are not needed. Therefore the recovery is faster.
• The database automatically deletes obsolete logs and backup sets when space is required.
• Recovery related files become obsolete when they exceed the RMAN retention policy or have been backed up to tape.
• Can easily re-flash more than once if needed.
• If you determine that you flashed back too far into the past, you can use redo logs to roll forward.
• We can exclude the particular tablespace to be part of these Flashback operations.

Disadvantages:

• Transaction rate on the database will have an affect on performance of creating the logs.
• If a control file has been restored or re-created during the time span you want to flashback over, you can’t use the Flashback Database feature.
• You can’t flashback a database to before a RESETLOGS operation.
• You can’t flashback a data file that was dropped or shrunk during the time span covered by the flashback table operation.

Conclusion:
This feature is the best strategy to reduce recovery time. We've seen this technology is easy to use, faster than traditional recovery and best of all, it's free! I hope that Flashback Database is an essential component of availability architecture.

References:
http://www.skillbuilders.com/download/download-sample.cfm?course=ora10g-nfo&file=Ora10g_New_Features_Sample_V2.1.pdf
http://www.globusz.com/ebooks/Oracle/00000019.htm
http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14194/rcmsynta032.htm
http://www.oracle.com/technology/pub/articles/10gdba/week9_10gdba.html http://www.databasejournal.com/features/oracle/article.php/3446681
http://www.dbazine.com/olc/olc-articles/liu6
http://sysdba.wordpress.com/2006/01/23/flashback-any-error-with-oracle-10g-release-2/

ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors;ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTD

E:\export>expdp "'sys/*******@dbname as sysdba'" parfile=E:\export\expdp.par

Export: Release 10.2.0.4.0 - Production on Tuesday, 30 June, 2009 10:56:10

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"
ORA-06512: at "SYS.KUPV$FT", line 842
ORA-04063: package body "SYS.DBMS_INTERNAL_LOGSTDBY" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_INTERNAL_LOGSTDBY"

Tried to compile that package and got the below error.

PACKAGE BODY SYS.DBMS_INTERNAL_LOGSTDBY On line: 3583 PL/SQL: ORA-00942: table or view does not exist

So tried to find which objects it is using/dependent with the below query.

SELECT owner, object_type, object_name, object_id, status
FROM SYS.dba_objects
where object_name = 'DBMS_INTERNAL_LOGSTDBY';

SELECT B.*
FROM PUBLIC_DEPENDENCY A, SYS.DBA_OBJECTS B
WHERE A.OBJECT_ID = 7331 AND A.REFERENCED_OBJECT_ID = B.OBJECT_ID AND OBJECT_TYPE = 'TABLE'
ORDER BY OWNER, OBJECT_NAME;

OBJECT_NAME

AUDIT_ACTIONS
COL$
DUAL
IND$
INDPART$
JOB$
OBJ$
TAB$
TS$
USER$
LOGMNR_DICTSTATE$
LOGMNR_LOG$
LOGMNR_SESSION$
LOGMNR_UID$
LOGSTDBY$APPLY_MILESTONE
LOGSTDBY$EVENTS
LOGSTDBY$HISTORY
LOGSTDBY$PARAMETERS
LOGSTDBY$SCN
LOGSTDBY$SKIP
LOGSTDBY$SKIP_TRANSACTION

Found that one table is missing by comparing the package with other database which is same version 10.2.0.4.

Table LOGSTDBY$SKIP_SUPPORT is missing in the system schema which the package is refering.

Created the script for that missing table from the database and created in the other database which is giving error.

The problem solved and export completed sucessfully with no errors.


reference:
------------

http://pandazen.wordpress.com/2009/01/03/cannot-drop-jobs-because-sysdbms_internal_logstdby-error/

http://www.dba-village.com/village/dvp_forum.OpenThread?ThreadIdA=32765 - solution