Schema Refresh (Using Export /Import)

1. In PROD Database (Source Database name: Prod1)

1.1 CHECK THE NUMBER OF OBJECTS IN THE SOURCE DATABASE:

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='PROD_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

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

PROD_USER123                   TABLE                               45

PROD_USER123                   INDEX                              120

PROD_USER123                   VIEW                                 1

PROD_USER123                   TRIGGER                              5

PROD_USER123                   SEQUENCE                            10

PROD_USER123                   LOB                                 19

6 rows selected.

1.2 CHECK THE INVALID OBJECTS IN THE SOURCE DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER='PROD_USER123';

no rows selected

1.3 TAKE EXPORT BACKUP OF PROD_USER123 SCHEMA FROM THE SOURCE DATABASE:

exp userid=system/xxxxxxx file=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp log=/p02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.log owner=PROD_USER123 rows=y indexes=y grants=y constraints=y

1.4 TAKE THE REQUIRED INFORMATION FROM THE SOURCE DATABASE:

SQL> set line 1000

SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='PROD_USER123';

USERNAME        PASSWORD   DEFAULT_TABLESPACE   TEMPORARY_TABLESPACE   PROFILE

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

PROD_USER123               USERDATA             TEMP                   DEFAULT

SQL> select * from dba_role_privs where grantee in('PROD_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in('PROD_USER123');

GRANTEE                        PRIVILEGE                                ADM

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

PROD_USER123                   CREATE TRIGGER                           NO

PROD_USER123                   CREATE PROCEDURE                         NO

PROD_USER123                   CREATE SEQUENCE                          NO

PROD_USER123                   CREATE SESSION                           NO

PROD_USER123                   GLOBAL QUERY REWRITE                     NO

PROD_USER123                   CREATE MATERIALIZED VIEW                 NO

PROD_USER123                   CREATE DATABASE LINK                     NO

PROD_USER123                   ALTER  SESSION                           NO

PROD_USER123                   CREATE INDEXTYPE                         NO

PROD_USER123                   CREATE OPERATOR                          NO

PROD_USER123                   CREATE SYNONYM                           NO

PROD_USER123                   CREATE TABLE                             NO

PROD_USER123                   CREATE TYPE                              NO

PROD_USER123                   CREATE VIEW                              NO

PROD_USER123                   CREATE CLUSTER                           NO

15 rows selected.
   
SQL> select * from dba_ts_quotas where username='PROD_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

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

USERDATA         PROD_USER123    12582912   -1         1536       -1         NO

USERINDX         PROD_USER123    4980736    -1         608        -1         NO


1.4 SEND THE FULL EXPORT BACKUP TO THE TARGET DATABASE SERVER:

[oracle@proddb1 export]$ scp prod1.exp_PROD_USER123_11DEC.dmp oracle@devdb1:/d02/app/oracle/export

 ***********************************Target Database-Start******************************************************** 

2. In wmdevdb1 DEV Database server:(dev1)

2.1 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :

SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                          OBJECT_TYPE         COUNT(OBJECT_TYPE)

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

DEV_USER123                    TRIGGER                              5

DEV_USER123                    SEQUENCE                             3

DEV_USER123                    LOB                                 12

DEV_USER123                    INDEX                               65

DEV_USER123                    VIEW                                 1

DEV_USER123                    TABLE                               26

6 rows selected.

2.2 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE:

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123';

no rows selected

2.3 TAKE EXPORT BACKUP OF DEV_USER123 SCHEMA FROM THE TARGET DATABASE :

[oracle@devdb1 export]$ exp userid=system/xxxxxx file=/d02/app/oracle/export/exp_DEV_USER123_11DEC.dmp log=/d02/app/oracle/export/exp_DEV_USER123_11DEC.log owner=DEV_USER123 rows=y indexes=y grants=y constraints=y

2.4 TAKE THE REQUIRED INFORMATION FROM THE TARGET DATABASE:
SQL> set line 1000    
SQL> select username,password,default_tablespace,temporary_tablespace,profile from dba_users where username ='DEV_USER123';

USERNAME    PASSWORD   DEFAULT_TABLESPACE TEMP_TABLESPACE PROFILE

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

DEV_USER123            USERDATA           TEMP            DEV_USER123_DEFAULT

SQL> select * from dba_role_privs where grantee in ('DEV_USER123');

no rows selected

SQL> select * from dba_sys_privs where grantee in ('DEV_USER123');

GRANTEE                        PRIVILEGE                                ADM

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

DEV_USER123                    CREATE OPERATOR                          NO

DEV_USER123                    CREATE MATERIALIZED VIEW                 NO

DEV_USER123                    CREATE TYPE                              NO

DEV_USER123                    CREATE SEQUENCE                          NO

DEV_USER123                    CREATE TABLE                             NO

DEV_USER123                    GLOBAL QUERY REWRITE                     NO

DEV_USER123                    CREATE INDEXTYPE                         NO

DEV_USER123                    ALTER  SESSION                           NO

DEV_USER123                    CREATE SESSION                           NO

DEV_USER123                    CREATE DATABASE LINK                     NO

DEV_USER123                    CREATE SYNONYM                           NO

DEV_USER123                    CREATE PROCEDURE                         NO

DEV_USER123                    CREATE VIEW                              NO

DEV_USER123                    CREATE CLUSTER                           NO

DEV_USER123                    CREATE TRIGGER                           NO

15 rows selected.

SQL> select * from dba_ts_quotas where username='DEV_USER123';

TABLESPACE_NAME  USERNAME        BYTES      MAX_BYTES  BLOCKS     MAX_BLOCKS DRO

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

USERDATA         DEV_USER123     38928384   -1         4752       -1         NO

USERINDX         DEV_USER123     8847360    -1         1080       -1         NO

2.5 DROP THE DEV_USER123 USERS FROM THE TARGET DATABASE:
SQL> drop user DEV_USER123 cascade;

2.6 CREATE THE DEV_USER123 USER AS PER THE SOURCE DATABASE :
SQL> create user DEV_USER123 identified by DEV_USER123

default tablespace USERDATA

temporary tablespace TEMP

profile DEV_USER123_DEFAULT

quota unlimited on USERDATA

quota unlimited on USERINDX;

2.7 GRANT THE PRIVILAGES TO THE PRODAPP USER AS PER SOURCE DATABASE :
SQL> grant connect,resource to DEV_USER123;
SQL> grant CREATE TABLE,CREATE SESSION,CREATE TRIGGER,CREATE SEQUENCE,UNLIMITED TABLESPACE,SELECT ANY DICTIONARY,CREATE VIEW to DEV_USER123;

2.8 IMPORT THE DATA TO THE TARGET DATABASE FROM THE SOURCE DATABASE :

[oracle@devdb1 export]$ imp userid=system/xxxxxxx file=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC.dmp 

log=/d02/app/oracle/export/prod1.exp_PROD_USER123_11DEC_imp.log 

fromuser=PROD_USER123 touser=DEV_USER123 ignore=y

2.9 CHECK THE NUMBER OF OBJECTS IN THE TARGET DATABASE :
SQL> select OWNER,OBJECT_TYPE,COUNT(OBJECT_TYPE) FROM DBA_OBJECTS WHERE OWNER ='DEV_USER123' GROUP BY OWNER,OBJECT_TYPE;

OWNER                         OBJECT_TYPE         COUNT(OBJECT_TYPE)

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

DEV_USER123                   TABLE                               45

DEV_USER123                   INDEX                              120

DEV_USER123                   VIEW                                 1

DEV_USER123                   TRIGGER                              5

DEV_USER123                   SEQUENCE                            10

DEV_USER123                   LOB                                 19

6 rows selected.
Note: Output of above command should be same as production.

2.10 CHECK THE INVALID OBJECTS IN THE TARGET DATABASE :

SQL> select owner,object_name,object_type,status from dba_objects where status <>'VALID' and OWNER ='DEV_USER123'; 

2.11 IF ANY INVALID OBJECTS THEN RUN UTLRP.SQL
    
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

**********************************END**********************************

Some usefull unix command for Oracle DBA

How to kill all similar processes with single command (in this case opmn)
ps -ef | grep opmn |grep -v grep | awk ‘{print $2}’ |xargs -i kill -9 {}

Locating Files under a particular directory
find . -print |grep -i test.sql
 Using AWK in UNIX

To remove a specific column of output from a UNIX command – for example to determine the UNIX process Ids for all Oracle processes on server (second column)
ps -ef |grep -i oracle |awk '{ print $2 }'

Changing the standard prompt for Oracle Users
PS1="`hostname`*$ORACLE_SID:$PWD>"

Display top 10 CPU consumers using the ps command\
/usr/ucb/ps auxgw | head -11

Show number of active Oracle dedicated connection users for a particular ORACLE_SID
ps -ef | grep $ORACLE_SID|grep -v grep|grep -v ora_|wc -l

Display the number of CPU’s in Solaris
psrinfo -v | grep "Status of processor"|wc -l

Display the number of CPU’s in AIX
lsdev -C | grep Process|wc -l

Display RAM Memory size on Solaris
prtconf |grep -i mem

Display RAM memory size on AIX

First determine name of memory device
lsdev -C |grep mem

then assuming the name of the memory device is ‘mem0’
lsattr -El mem0

Swap space allocation and usage
Solaris : swap -s or swap -l
Aix : lsps -a

Total number of semaphores held by all instances on server
ipcs -as | awk '{sum += $9} END {print sum}'

View allocated RAM memory segments
ipcs -pmb

Manually deallocate shared memeory segments
ipcrm -m '<ID>'

Show mount points for a disk in AIX
lspv -l hdisk13

Display amount of occupied space (in KB) for a file or collection of files in a directory or sub-directory
du -ks * | sort -n| tail

Display total file space in a directory
du -ks .

Cleanup any unwanted trace files more than seven days old
find . *.trc -mtime +7 -exec rm {} \;

Locate Oracle files that contain certain strings
find . -print | xargs grep rollback

Locate recently created UNIX files (in the past one day)
find . -mtime -1 -print

Finding large files on the server (more than 100MB in size)
find . -size +102400 -print

Crontab :

To submit a task every Tuesday (day 2) at 2:45PM
45 14 2 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every 15 minutes on weekdays (days 1-5)
15,30,45 * 1-5 * * /opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

To submit a task to run every hour at 15 minutes past the hour on weekends (days 6 and 0)
15 * 0,6 * * opt/oracle/scripts/tr_listener.sh > /dev/null 2>&1

Creating standby database in oracle 11g without rman backups

Quick steps to set up a 11g Standby database with Active Data Guard using the Active Duplication feature available in 11g where we can create a standby database without having to take a backup on the primary database. Datafiles are copied over the network.

Primary machine – PROD01
Standby machine – STAND01

Database Name – PROD

TNS alias for Primary – prod_ps
TNS alias for standby – prod_ss

Enable force logging on the Primary database

SQL> alter database force logging;

Database altered.

Create the Standby log files on the Primary database

Copy the password file from the $ORACLE_HOME/dbs directory on primary server to $ORACLE_HOME/dbs on the standby server

Update listener.ora on Standby machine
(SID_DESC=
(GLOBAL_DBNAME=prod_ss)
(ORACLE_HOME=/p01/oracle/product/11.1.0/db_1)
(SID_NAME=prod)
)

Stop and Restart the listener on the standby site

Update tnsnames.ora on Standby as well as Primary site with the alias ‘prod_ss’ and ‘prod_ps’
prod_ss =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stand01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod_ss )
)
)

prod_ps =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = PROD01)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = prod01.domain.com)
)
)

Create an init.ora on the Standby machine with just a single line which is the db_name parameter
[oracle@stand01 dbs]$ cat initprod.ora
db_name=prod

Startup the Standby instance in nomount state
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 217157632 bytes
Fixed Size 2152328 bytes
Variable Size 159385720 bytes
Database Buffers 50331648 bytes
Redo Buffers 5287936 bytes

On the Primary launch RMAN and establish an auxiliary connection to the standby instance
[oracle@prod01 ~]$ rman target / auxiliary sys/xxx@prod_ss

Recovery Manager: Release 11.1.0.7.0 – Production on Fri JAN 24 10:28:51 2013

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

connected to target database: PROD (DBID=2860177231)
connected to auxiliary database: PROD (not mounted)

Run the command to create the Standby Database
DUPLICATE TARGET DATABASE
FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK
DORECOVER
SPFILE
SET DB_UNIQUE_NAME=”prod_ss”
SET LOG_ARCHIVE_DEST_2=”service=prod_ps LGWR SYNC REGISTER VALID_FOR=(online_logfile,primary_role)”
SET FAL_SERVER=”prod_ps”
SET FAL_CLIENT=”prod_ss”
SET CONTROL_FILES=’/p02/oradata/prod/control01.ctl’,'/p03/oradata/prod/control02.ctl’;

Change the init.ora parameters related to redo transport and redo apply
On standby and primary

SQL> alter system set standby_file_management=AUTO scope=both;

System altered.

On Primary

SQL> alter system set fal_server=prod_ss scope=both;

System altered.

SQL> alter system set fal_client=prod_ps scope=both;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2=’SERVICE=prod_ss LGWR SYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod_ss’ scope=both; 

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_1=’LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod’;

System altered.

Shutdown the Standby and enable managed recovery (active standby mode)
SQL> shutdown immediate;
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1043886080 bytes
Fixed Size 2160352 bytes
Variable Size 775948576 bytes
Database Buffers 260046848 bytes
Redo Buffers 5730304 bytes
Database mounted.
Database opened.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
SQL>

Check if the MRP process is running
SQL> !ps -ef |grep mrp
oracle 446526 1 0 10:59:01 – 0:00 ora_mrp0_prod

TEST
--------------------
On Primary
SQL> conn system/xxx
Connected.
SQL> create table test123(mydate date);

Table created.

SQL> insert into test123 values(sysdate);
1 row created.

SQL> commit;
Commit complete.

SQL> alter system switch logfile;

System altered.

On Standby
SQL> conn system/xxx
Connected.
SQL> select to_char(mydate,’DD-MON-YY HH24:MI:SS’) from test123;

TO_CHAR(MYDATE,’DD-MON-YYHH
—————————
24-JAN-13 11:15:49

Standby Database Concepts

What Is a Standby Database?

A standby database is a database replica created from a backup of a primary database. By applying archived redo logs from the primary database to the standby database, you can keep the two databases synchronized.

A standby database has the following main purposes:

• Disaster protection
• Protection against data corruption
• Supplemental reporting

If the primary database is destroyed or its data becomes corrupted, you can perform a failover to the standby database, in which case the standby database becomes the new primary database. You can also open a standby database with the read-only option, thereby allowing it to function as an independent reporting database.

This section contains the following topics:

• Configuration Options
• Advantages and Disadvantages
• Compatibility and Operational Requirements
• Concepts and Terminology

Configuration Options:

You can set up a standby database in several different ways, depending on the method for:

Transferring archived redo logs to the standby site Applying archived redo logs to the standby database

For example, Oracle's managed standby environment allows the primary database to automatically archive redo logs to the standby database site so long as the standby instance is started. If you implement a non-managed standby environment, you must transfer the logs manually.

If the standby database is in managed recovery mode, the standby database automatically applies logs received from the primary database. You can also apply logs manually to the standby database by placing it in manual recovery mode. At any time you can open the standby database in read-only mode for reporting purposes.

Most database administrators (DBAs) choose a managed recovery environment. You may prefer a non-managed environment if:

• You do not want to maintain a Net8 connection between the primary and standby sites, which is required for managed recovery.
• You want to create a time lag between the archiving of a log at the primary database and the application of the log to the standby database. A time lag protects against the transfer of corrupted or erroneous data from the primary database to the standby database.

Advantages and Disadvantages

A standby database can be a powerful tool for both disaster prevention and supplementary reporting. For example, you can:

• Maintain a standby database in a location that is geographically remote from the primary database, or maintain several standby databases in geographically diverse locations.
• Maintain the primary and standby databases on different disk drives of the same machine, so that if the primary database's drive fails, you can activate the standby database and resume normal operations.
• Implement a managed standby configuration, whereby a standby database automatically applies archived redo logs that are automatically shipped to the standby site by a primary database. In this way, changes to a primary database are regularly propagated to a standby database.
• Make a standby database the new primary database with minimal loss of time and data if the primary database is completely destroyed.
• Provide possible protection against erroneous batch jobs, user errors (for example, truncating the wrong table), or application corruptions on the primary database by not applying archived logs containing corrupt data to the standby database. You can then activate the uncorrupted standby database, making it the primary database.

While a standby database can be a tremendous benefit in your backup and recovery strategy, it involves costs as well. For example, a standby database requires:

• An additional computer if you want to maximize disaster prevention by maintaining a standby database on a separate host
• Implementation and maintenance of a Net8 connection if you use the managed standby environment
• Additional system resources and extra storage space no matter which implementation you choose
• Administration of the standby database to mirror some structural operations (for example, adding a tablespace or datafile) performed on the primary database

Concepts and Terminology

Familiarize yourself with the following terms, which are used throughout the subsequent chapters:

failover

The operation of turning a standby database into a normally functioning primary database. This operation is also called standby database activation. Note that after a failover, you cannot switch the standby database back so that it becomes a standby database again.

gap sequence

A sequence of archived redo logs that must be manually applied to a standby database before it can be placed in managed recovery mode.

managed recovery mode

A standby database mode initiated by entering the following SQL*Plus statement:

RECOVER MANAGED STANDBY DATABASE;

When a standby database runs in managed recovery mode, it automatically applies redo logs received from the primary database.

managed standby environment

A configuration in which a primary database automatically archives redo logs to a standby site. If the standby database is in managed recovery mode, it automatically applies the logs received from the primary database to the standby database. Note that in a managed standby environment, a standby site continues to receive archived logs even if the standby database is not in managed recovery mode.

manual recovery mode

A standby database mode initiated by issuing the following SQL*Plus statement:

RECOVER STANDBY DATABASE;

This mode allows you to recover a standby database manually.

non-managed standby environment

Any environment in which the primary database does not automatically archive redo logs to the standby site. In this environment, you must manually transfer archived logs to the standby site and manually apply them.

primary database

A database used to create a standby database. Every standby database is associated with one and only one primary database. A single primary database can, however, support multiple standby databases.

primary site

The location of the primary database. Note that the primary and standby sites can be on separate hosts or on the same host.

read-only mode

A standby database mode initiated by issuing the following SQL statement:

ALTER DATABASE OPEN READ ONLY;

This mode allows you to query the standby database, but not to make changes to it.

standby database

A database replica created using a backup of your primary database. A standby database has its own initialization parameter file, control file, and datafiles.

standby database environment

The physical configuration of the primary and standby databases. The environment depends on many factors, including:

• The number of standby databases associated with a primary database
• The number of machines used by the databases
• The directory structures of the machines used by the databases
• The network configuration

standby site

The location of the standby database. The standby site can be on the same host as the primary database or on a separate host.

Standby Database Modes

You can perform any of the following mutually exclusive operations on a standby database:

• Maintain it in manual recovery mode
• Maintain it in managed recovery mode
• Open it in read-only mode for queries

Although you cannot run the standby database in more than one mode at the same time, you can switch back and forth between the modes at will. For example, you can run in managed recovery mode, then open read-only, then switch to manual recovery, then return to managed recovery, as shown in Figure 1-1.

Figure 1-1 Switching Between Modes

Manual Recovery Mode

You have the option of placing the database in manual recovery mode, in which case you must continually and manually transfer and apply archived redo logs to the standby database to keep it synchronized with the primary database.

To perform manual recovery, connect to the standby database using SQL*Plus and issue the RECOVER STANDBY DATABASE statement. Figure 1-2 shows an example of a database in manual recovery mode.

Figure 1-2 Standby Database in Manual Recovery Mode

Manual recovery mode is useful in environments in which you do not want to connect the primary and standby databases through Net8. Also, if for some reason the primary database is unable to automatically transfer archived redo logs to a standby database in a managed recovery environment, you may need to perform manual recovery to update the standby database.

Managed Recovery Mode

You can place the standby database in managed recovery mode, in which case the standby database automatically applies archived redo logs as it receives them from the primary database. To initiate managed recovery, connect to the standby database using SQL*Plus and issue the RECOVER MANAGED STANDBY DATABASE statement.

The principal advantage of running a database in managed recovery mode is that you do not have to transfer or apply archived redo logs manually: Oracle automates the procedure. For example, Figure 1-3 illustrates a case in which a primary database in San Francisco transmits archived redo logs to a standby site in Boston, where the standby database automatically applies them.

Figure 1-3 Automatic Updating of a Standby Database

Read-Only Mode

You can also open your standby database in read-only mode after terminating manual or managed recovery. You can then query the database and even store data in temporary tablespaces (so long as they already exist in the standby database) without affecting the datafiles or redo logs. You can return the standby database to manual or managed recovery mode at any time, without having to shut it down. Figure 1-4 shows a standby database in read-only mode.

Figure 1-4 Standby Database in Read-Only Mode

In a managed standby environment, the standby site continues to receive redo logs archived by the primary database and the control file continues to be updated with their records. Consequently, archiving continues to the standby site even though the standby database does not perform recovery while in read-only mode.

A read-only standby database is useful when you want to decrease the number of queries to the primary database. For example, if specific tablespaces in a primary database change infrequently but are accessed frequently, you can direct those queries to the standby database so the primary database does not become overloaded with read requests.

Failover to a Standby Database

Performing a failover to a standby database, also known as activation, occurs when you issue the following SQL statement:

ALTER DATABASE ACTIVATE STANDBY DATABASE;

You can issue this statement only when the standby database is mounted.

Figure 1-5 depicts a failover operation from a primary database in San Francisco to a standby database in managed recovery mode in Boston.

Figure 1-5 Failover to a Standby Database\

After you activate the standby database, it ceases to be a standby database and becomes a fully functional primary database. At this point, you can open the database in read/write or read-only mode and make changes or issue queries as usual.

CAUTION: Activating a standby database is a permanent operation. You cannot undo the activation and return the database to its former role as a standby database. 

Consequences of Failover

Failover permanently transforms a standby database into a primary database. Because standby activation is a unidirectional operation, you cannot return the new primary database to any of the standby modes. In other words, you cannot perform a failover and then undo it.

CAUTION:Activating a standby database resets the online logs of the standby database. After activation, the archived logs from the standby database and the primary database are incompatible. 

Another consequence of failover is that any other standby databases that were supporting the original primary database are now invalid as standby databases to the new, activated primary database. For example, assume primary database A supports standby databases B and C, as illustrated in Figure 1-6.

Figure 1-6 Primary Database with Multiple Standby Databases

If you perform a failover from A to B, then C does not function as standby database to the newly activated B. Because B's redo logs are reset, you cannot apply archived redo logs from B to C.

In some situations, maintaining multiple standby databases can lessen the repercussions of a failover. For example, assume the preceding scenario, with standby databases B and C supporting primary database A. The following events occur:

1. A's machine suffers a media failure.
2. You activate standby database B. Users now access B as the primary database.
3. You quickly fix the media problem on A's machine.
4. You shut down B, then restart A.
5. Users now access A as the primary database again. C continues to function as a standby database for A, while B is invalidated.

One consequence of this scenario is that any changes made to B while it briefly served as the primary database cannot be applied through archived redo logs. Note that you can generate a report of these changes by using the LogMiner utility and then reenter the changes manually into database A.

Testing the Standby Database Without Performing Failover

Because failover to a standby database destroys its standby functionality, perform this operation only when absolutely necessary. If you want to test the standby database, do not activate it--open it in read-only mode instead. By opening in read-only mode, you can query the standby database to ensure that it is correctly updating the datafiles with the redo logs received from the primary database.

Re-Creating the Original Primary Database After Failover

If you activate a standby database and then solve the problem at the original primary site that necessitated the failover operation, you have the option of re-creating the primary database on the original primary site. Perform the following steps, assuming the original primary site was on node A and the activated standby site is on node B:

1. Make a consistent backup of the activated standby database on node B.
2. Restore the backup created on node B to node A.
3. Shut down the activated standby database on node B.
4. Open the restored database on A. It is now the primary database.
5. Make a backup of the database on node A.
6. Use the backup of A to re-create the standby database on node B.

Manual Recovery Using Logs in the Gap Sequence

A gap sequence is created whenever you are unable to apply the next archived redo log generated by the primary database to the standby database. For example, the primary database archives log 100 to the standby site, but the standby control file has no knowledge of any logs after log sequence 89, because the standby control file was created when the most recent log archived by the primary database was 89. The gap sequence in this case spans logs 90 to 99.

To be able to begin managed recovery, you must first manually apply logs in the gap sequence to the standby database. After you have performed this manual recovery, you can issue the RECOVER MANAGED STANDBY DATABASE statement, at which point Oracle applies subsequent logs to the standby database automatically.

Typical Causes of Gap Sequences

Most commonly, gap sequences occur in the following situations:

• Creation of the standby database
• Shutdown of the standby database when the primary database is open
• A network failure that prevents archiving to the standby site

In the first two situations, the primary database can archive redo logs to the standby site, but the standby database control file is unaware of logs archived while it was not mounted. Whenever the primary database archives to the primary site but the standby control file does not contain records of logs that are necessary for recovery of the database, a gap sequence is created.

In the third situation, the primary database continues to archive locally, but is prevented from archiving to the standby site by a network failure. Archived logs accumulate at the primary site, but the standby control file does not know about them. Consequently, you must transfer the accumulated logs manually and then apply them in a manual recovery operation before managed recovery can begin.

The Gap Sequence Cycle

Because a gap sequence can occur whenever the primary database is archiving logs that the standby control file is not informed about, you can occasionally go through a gap sequence cycle. This cycle occurs whenever you must exit either managed recovery mode or read-only mode to perform manual recovery using logs in a gap sequence. After you have completed manual recovery of all logs in the sequence, you can return to managed recovery or read-only mode.

Managed Recovery and Read-Only Access Cycle

In most scenarios, you run the database primarily in managed recovery mode or primarily in read-only mode. During managed recovery, the standby site receives logs from the primary database and the standby recovery process applies them automatically. In read-only mode, the standby site receives logs from the primary database, but the standby recovery process does not apply them.

You can easily switch between managed recovery mode and read-only mode. Because the standby control file continues to be updated about incoming logs when it is in read-only mode, you do not have to perform manual recovery before returning to managed recovery mode.

Failover to the Standby Database

You can activate the standby database at any time, so long as the database is mounted. Typically, you perform a failover operation while the standby database is running in managed recovery mode. It can occur, however, that the primary database fails while you are performing manual recovery using logs in the gap sequence. In this case, you can activate the standby database even though it does not contain the latest changes made to the primary database.

After failover, you can back up the new primary database and start the standby life cycle over again by creating a new standby database.