You cannot rename the data file on the standby site when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO.

When you set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, use of the following SQL statements is not allowed:

• ALTER DATABASE RENAME

• ALTER DATABASE ADD/DROP LOGFILE

• ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER

• ALTER DATABASE CREATE DATAFILE AS

If you attempt to use any of these statements on the standby database, an error is returned. For example:

SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy';

alter database rename file '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy' 
* 
ERROR at line 1: 
ORA-01511: error in renaming log/datafiles 
ORA-01270: RENAME operation is not allowed if STANDBY_FILE_MANAGEMENT is auto

See Adding a Data File or Creating a Tablespace to learn how to add data files to a physical standby database.

If the standby site is not receiving redo data, query the V$ARCHIVE_DEST view and check for error messages.

For example, enter the following query:

SQL> SELECT DEST_ID "ID", -
> STATUS "DB_status", -
> DESTINATION "Archive_dest", -
> ERROR "Error" -
> FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;

ID DB_status Archive_dest                   Error   
-- --------- ------------------------------ ------------------------------------
 1  VALID    /vobs/oracle/work/arc_dest/arc                          
 2  ERROR    standby1                       ORA-16012: Archivelog standby database identifier mismatch  
 3  INACTIVE                            
 4  INACTIVE                    
 5  INACTIVE                                           
5 rows selected.

If the output of the query does not help you, then check the following list of possible issues. If any of the following conditions exist, then redo transport services fail to transmit redo data to the standby database:

• The service name for the standby instance is not configured correctly in the tnsnames.ora file for the primary database.

• The Oracle Net service name specified by the LOG_ARCHIVE_DEST_n parameter for the primary database is incorrect.

• The LOG_ARCHIVE_DEST_STATE_n parameter for the standby database is not set to the value ENABLE.

• The listener.ora file has not been configured correctly for the standby database.

• The listener is not started at the standby site.

• The standby instance is not started.

• You have added a standby archiving destination to the primary SPFILE or text initialization parameter file, but have not yet enabled the change.

• Redo transport authentication has not been configured properly. See section 3.1.2 for redo transport authentication configuration requirements.

• You used an invalid backup as the basis for the standby database (for example, you used a backup from the wrong database, or did not create the standby control file using the correct method).

You cannot mount the standby database if the standby control file was not created with the ALTER DATABASE CREATE [LOGICAL] STANDBY CONTROLFILE ... statement or RMAN command.

You cannot use the following types of control file backups:

• An operating system-created backup

• A backup created using an ALTER DATABASE statement without the PHYSICAL STANDBY or LOGICAL STANDBY option

If the switchover does not complete successfully, you can query the SEQUENCE# column in the V$ARCHIVED_LOG view to see if the last redo data transmitted from the original primary database was applied on the standby database.

If the last redo data was not transmitted to the standby database, you can manually copy the archived redo log file containing the redo data from the original primary database to the old standby database and register it with the SQL ALTER DATABASE REGISTER LOGFILE file_specification statement. If you then start apply services, the archived redo log file is applied automatically. Query the SWITCHOVER_STATUS column in the V$DATABASE view. A switchover to the primary role is now possible if the SWITCHOVER_STATUS column returns TO PRIMARY or SESSIONS ACTIVE.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS 
----------------- 
TO PRIMARY 
1 row selected 

To continue with the switchover, follow the instructions in Performing a Switchover to a Physical Standby Database for physical standby databases or Performing a Switchover to a Logical Standby Database for logical standby databases, and try again to switch the target standby database to the primary role.

This is an explanation of why you may receive an ORA-01102 error during a switchover, and what action to take.

Suppose the standby database and the primary database reside on the same site. After the ALTER DATABASE SWITCHOVER TO target_db_name statement is successfully executed, shut down and restart the physical standby database and the primary database.

However, the startup of the second database fails with ORA-01102 error "cannot mount database in EXCLUSIVE mode."

This could happen during the switchover if you did not set the DB_UNIQUE_NAME parameter in the initialization parameter file that is used by the standby database (the original primary database). If the DB_UNIQUE_NAME parameter of the standby database is not set, the standby and the primary databases both use the same mount lock and cause the ORA-01102 error during the startup of the second database.

Action: Add DB_UNIQUE_NAME=unique_database_name to the initialization parameter file used by the standby database, and shut down and restart the standby and primary databases.

If the archived redo log files are not applied to the new standby database after the switchover, it could be because some environment or initialization parameters were not properly set after the switchover.

For physical standby databases in situations where an error occurred and it is not possible to continue with the switchover, it might still be possible to revert the new physical standby database back to the primary role.

1. Shut down and mount the new standby database (old primary).
2. Start Redo Apply on the new standby database.
3. Verify that the new standby database is ready to be switched back to the primary role. Query the SWITCHOVER_STATUS column of the V$DATABASE view on the new standby database. For example:

   SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;
    
   SWITCHOVER_STATUS 
   ----------------- 
   TO_PRIMARY 
   1 row selected
   

   A value of TO PRIMARY or SESSIONS ACTIVE indicates that the new standby database is ready to be switched to the primary role. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.

4. Issue the following statement to convert the new standby database back to the primary role:

   SQL> ALTER DATABASE SWITCHOVER TO target_db_name [FORCE];
   

   If this statement is successful, then the database runs in the primary database role, and you do not need to perform any more steps.

   If this statement is unsuccessful, then continue with Step 5.

5. When the switchover to change the role from primary to physical standby was initiated, a trace file was written in the log directory. This trace file contains the SQL statements required to re-create the original primary control file. Locate the trace file and extract the SQL statements into a temporary file. Execute the temporary file from SQL*Plus. This reverts the new standby database back to the primary role.
6. Shut down the original physical standby database.
7. Create a new standby control file. This is necessary to resynchronize the primary database and physical standby database. Copy the physical standby control file to the original physical standby system. Create a Control File for the Standby Database describes how to create a physical standby control file.
8. Restart the original physical standby instance.

   If this procedure is successful and archive gap management is enabled, then the FAL processes start and re-archive any missing archived redo log files to the physical standby database. Force a log switch on the primary database and examine the alert logs on both the primary database and physical standby database to ensure the archived redo log file sequence numbers are correct.

   See Manual Gap Resolution for information about archive gap management and Setting Archive Tracing for information about locating the trace files.

9. Try the switchover again.

   At this point, the Oracle Data Guard configuration has been rolled back to its initial state, and you can try the switchover operation again (after correcting any problems that might have led to the initial unsuccessful switchover).

If a failure occurs during the preparation phase of a switchover operation, then cancel the switchover and retry the switchover operation from the very beginning.

Although committing to a switchover involves a single SQL statement, internally a number of operations are performed.

The corrective actions that you need to take depend on the state of the commit to switchover operation when the error was encountered.

Logical standby databases maintain user tables, sequences, and jobs. To maintain other objects, you must reissue the DDL statements seen in the redo data stream.

If SQL Apply fails, an error is recorded in the DBA_LOGSTDBY_EVENTS table. The following sections demonstrate how to recover from two such errors.

Oracle SQL*Loader provides a method of loading data from different sources into the Oracle Database.

This topic analyzes some of the features of the SQL*Loader utility as it pertains to SQL Apply.

Regardless of the method of data load chosen, the SQL*Loader control files contain an instruction on what to do to the current contents of the Oracle table into which the new data is to be loaded, via the keywords of APPEND and REPLACE. The following examples show how to use these keywords on a table named LOAD_STOK:

• When using the APPEND keyword, the new data to be loaded is appended to the contents of the LOAD_STOK table:

  LOAD DATA
  INTO TABLE LOAD_STOK APPEND
  

• When using the REPLACE keyword, the contents of the LOAD_STOK table are deleted prior to loading new data. Oracle SQL*Loader uses the DELETE statement to purge the contents of the table, in a single transaction:

  LOAD DATA
  INTO TABLE LOAD_STOK REPLACE
  

Rather than using the REPLACE keyword in the SQL*Loader script, Oracle recommends that prior to loading the data, you issue the SQL*Plus TRUNCATE TABLE command against the table on the primary database. This has the same effect of purging both the primary and standby databases copy of the table in a manner that is both fast and efficient because the TRUNCATE TABLE command is recorded in the online redo log files and is issued by SQL Apply on the logical standby database.

The SQL*Loader script may continue to contain the REPLACE keyword, but it now attempts to DELETE zero rows from the object on the primary database. Because no rows were deleted from the primary database, there is no redo recorded in the redo log files. Therefore, no DELETE statement is issued against the logical standby database.

Issuing the REPLACE keyword without the SQL statement TRUNCATE TABLE provides the following potential problems for SQL Apply when the transaction needs to be applied to the logical standby database.

• If the table currently contains a significant number of rows, then these rows need to be deleted from the standby database. Because SQL Apply is not able to determine the original syntax of the statement, SQL Apply must issue a DELETE statement for each row purged from the primary database.

  For example, if the table on the primary database originally had 10,000 rows, then Oracle SQL*Loader issues a single DELETE statement to purge the 10,000 rows. On the standby database, SQL Apply does not know that all rows are to be purged, and instead must issue 10,000 individual DELETE statements, with each statement purging a single row.

• If the table on the standby database does not contain an index that can be used by SQL Apply, then the DELETE statement issues a Full Table Scan to purge the information.

  Continuing with the previous example, because SQL Apply has issued 10,000 individual DELETE statements, this could result in 10,000 Full Table Scans being issued against the standby database.

One of the primary causes for long-running transactions in a SQL Apply environment is full table scans.

Additionally, long-running transactions could be the result of SQL statements being replicated to the standby database, such as when creating or rebuilding an index.

Mon Feb 17 14:40:15 2003
WARNING: the following transaction makes no progress
WARNING: in the last 30 seconds for the given message!
WARNING: xid =
0x0016.007.000017b6 cscn = 1550349, message# = 28, slavid = 1
knacrb: no offending session found (not ITL pressure)

SQL> SELECT SAS.SERVER_ID -
>  , SS.OWNER -
>  , SS.OBJECT_NAME -
>  , SS.STATISTIC_NAME -
>  , SS.VALUE -
>  FROM V$SEGMENT_STATISTICS SS -
>  , V$LOCK L -
>  , V$STREAMS_APPLY_SERVER SAS -
>  WHERE SAS.SERVER_ID = &SLAVE_ID -
>  AND L.SID = SAS.SID -
>  AND L.TYPE = 'TM' -
>  AND SS.OBJ# = L.ID1;

SQL> SELECT SUBSTR(SQL_TEXT,1,40) -
>  , DISK_READS -
>  , EXECUTIONS -
>  , DISK_READS/EXECUTIONS -
>  , HASH_VALUE -
>  , ADDRESS -
>  FROM V$SQLAREA -
>  WHERE DISK_READS/GREATEST(EXECUTIONS,1) > 1 -
>  AND ROWNUM < 10 -
>  ORDER BY DISK_READS/GREATEST(EXECUTIONS,1) DESC;

Tue Apr 22 15:50:42 2003
WARNING: the following transaction makes no progress
WARNING: in the last 30 seconds for the given message!
WARNING: xid =
0x0006.005.000029fa cscn = 2152982, message# = 2, slavid = 17

SQL> SELECT SA.SQL_TEXT -
>  FROM V$SQLAREA SA -
  >  , V$SESSION S -
  >  , V$STREAMS_APPLY_SERVER SAS -
  >  WHERE SAS.SERVER_ID = &SLAVEID -
  >  AND S.SID = SAS.SID -
  >  AND SA.ADDRESS = S.SQL_ADDRESS

SQL_TEXT
------------------------------------------------------------
insert into "APP"."LOAD_TAB_1" p("PK","TEXT")values(:1,:2)

SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST -
> FROM V$LOCK -
> WHERE TYPE = 'TX'

SID        TY ID1        ID2        LMODE      REQUEST
---------- -- ---------- ---------- ---------- ----------
         8 TX     327688         48          6          0
        10 TX     327688         48          0          4

SQL> SELECT OWNER, OBJECT_NAME, OBJECT_TYPE -
>  FROM V$SEGMENT_STATISTICS -
>  WHERE STATISTIC_NAME = 'ITL waits' -
>  AND VALUE > 0 -
>  ORDER BY VALUE;

If SQL Apply returns the ORA-1403: No Data Found error, then it may be possible to use Flashback Transaction to reconstruct the missing data.

This strategy relies upon the UNDO_RETENTION initialization parameter specified on the standby database instance.

Under normal circumstances, the ORA-1403 error is not seen in a logical standby database environment. The error occurs when data in a table that is being managed by SQL Apply is modified directly on the standby database and then the same data is modified on the primary database. When the modified data is updated on the primary database and is subsequently received on the logical standby database, SQL Apply verifies the original version of the data is present on the standby database before updating the record. When this verification fails, the ORA-1403: No Data Found error is returned.

LOGSTDBY stmt: UPDATE "SCOTT"."MASTER"
  SET
    "NAME" = 'john'
  WHERE 
    "PK" = 1 and 
    "NAME" = 'andrew' and 
    ROWID = 'AAAAAAAAEAAAAAPAAA'
LOGSTDBY status: ORA-01403: no data found
LOGSTDBY PID 1006, oracle@staco03 (P004)
LOGSTDBY XID 0x0006.00e.00000417, Thread 1, RBA 0x02dd.00002221.10

SELECT VERSIONS_XID
      , VERSIONS_STARTSCN
      , VERSIONS_ENDSCN
      , VERSIONS_OPERATION
      , PK
      , NAME
   FROM SCOTT.MASTER
        VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
  WHERE PK = 1
  ORDER BY NVL(VERSIONS_STARTSCN,0);

VERSIONS_XID     VERSIONS_STARTSCN VERSIONS_ENDSCN V  PK NAME
---------------- ----------------- --------------- - --- -------
03001900EE070000           3492279         3492290 I   1 andrew
02000D00E4070000           3492290                 D   1 andrew

SELECT OPERATION
     , UNDO_SQL
  FROM FLASHBACK_TRANSACTION_QUERY
 WHERE XID = HEXTORAW('02000D00E4070000');

OPERATION  UNDO_SQL
---------- ------------------------------------------------
DELETE     insert into "SCOTT"."MASTER"("PK","NAME") values
           ('1','andrew');
BEGIN

SQL> INSERT INTO "SCOTT"."MASTER"("PK","NAME") VALUES ('1','ANDREW');SQL> COMMIT;

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;