ORA-16224: Database Guard is enabled

SQL> INSERT INTO TRANSACTION_INFO(BRANCH_CODE, HOUR, MINUTES)
VALUES(122,12,12);  2
INSERT INTO TRANSACTION_INFO(BRANCH_CODE, HOUR, MINUTES)
            *
ERROR at line 1:
ORA-16224: Database Guard is enabled


SQL> SELECT GUARD_STATUS FROM GV$DATABASE;

GUARD_S
-------
ALL
ALL

SQL> ALTER DATABASE GUARD NONE;

Database altered.

SQL> SELECT GUARD_STATUS FROM GV$DATABASE;

GUARD_S
-------
NONE
NONE

SQL> INSERT INTO TRANSACTION_INFO(BRANCH_CODE, HOUR, MINUTES)
VALUES(122,12,12);  2

1 row created.

SQL> commit;

Commit complete.

SQL>

How to Perform a Switchover to a Logical Standby Database.

1. Verify it is possible to perform a switchover on the primary database. A value of TO STANDBY or SESSIONS ACTIVE in the SWITCHOVER_STATUS column, if it is not; check the parameter of LOG_ARCHIVE_DEST_n is valid or not.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
-------------------------
SESSIONS ACTIVE

2. Prepare the current primary database for the switchover.

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

3. Check the status of current primary database.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

4. Prepare the target logical standby database for the switchover.

SQL> ALTER DATABASE PREPARE TO SWITCHOVER TO PRIMARY;

Database altered.

5. Check the status of current logical standby database.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
PREPARING SWITCHOVER

6. Check the status of current primary database.

SQL> SELECT SWITCHOVER_STATUS FROM V$DATABASE;

SWITCHOVER_STATUS
--------------------
TO LOGICAL STANDBY

7.Switch the primary database to the logical standby database role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;

Database altered.

8.Switch the logical standby database to the primary database role.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

Database altered.

9 Start SQL Apply on the new logical standby database (Old primary database).

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

ORA-01118: cannot add any more database files: limit of 125 exceeded

Cause : MAXDATAFILES parameter value of control file exceeded.

Solution: Create control file to trace and change the value of MAXDATAFILES parameter into new control file and start database using new control file.

ORA-00059: maximum number of DB_FILES exceeded

SQL> ALTER TABLESPACE DATA ADD DATAFILE '/oradata/erpdcdb/DATA12.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

ALTER TABLESPACE DATA ADD DATAFILE '/oradata/erpdcdb/DATA12.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
*
ERROR at line 1:
ORA-01118: cannot add any more database files: limit of 125 exceeded


SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS '/oradata/erpdcdb/CONTROL_FILE.SQL';

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area 6.8564E+10 bytes
Fixed Size                  2382016 bytes
Variable Size            2.4478E+10 bytes
Database Buffers         4.4074E+10 bytes
Redo Buffers               10338304 bytes
SQL>

Now open the file and generate the scripts for control file and execute like below

SQL> CREATE CONTROLFILE REUSE DATABASE "erpdcdb" RESETLOGS FORCE LOGGING ARCHIVELOG
    MAXLOGFILES 16
  2    3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 500
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 1168
  7  LOGFILE
  8    GROUP 1 (
  9      '/fra/erpdcdb/redo01a.log',
 10      '/oradata/erpdcdb/redo01b.log'
 11    ) SIZE 1024M BLOCKSIZE 512,
 12    GROUP 2 (
 13      '/fra/erpdcdb/redo02a.log',
 14      '/oradata/erpdcdb/redo02b.log'
 15    ) SIZE 1024M BLOCKSIZE 512,
 16    GROUP 3 (
 17      '/fra/erpdcdb/redo03a.log',
 18      '/oradata/erpdcdb/redo03b.log'
 19    ) SIZE 1024M BLOCKSIZE 512,
 20    GROUP 4 (
 21      '/oradata/erpdcdb/redo04b.log',
        '/fra/erpdcdb/redo04a.log'
 22   23    ) SIZE 1024M BLOCKSIZE 512,
 24    GROUP 5 (
 25      '/fra/erpdcdb/redo05a.log',
 26      '/oradata/erpdcdb/redo05b.log'
 27    ) SIZE 1024M BLOCKSIZE 512,
 28    GROUP 6 (
 29      '/fra/erpdcdb/redo06a.log',
 30      '/oradata/erpdcdb/redo06b.log'
 31    ) SIZE 1024M BLOCKSIZE 512
 32  DATAFILE
 33    '/oradata/erpdcdb/system01.dbf',
 34    '/oradata/erpdcdb/sysaux01.dbf',
 35    '/oradata/erpdcdb/undotbs01.dbf',
 36    '/oradata/erpdcdb/users01.dbf'
37  CHARACTER SET WE8MSWIN1252;

Control file created.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata/erpdcdb/temp01.dbf'
     SIZE 32767M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M; 

Tablespace altered.

SQL>
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 6.8564E+10 bytes
Fixed Size                  2382016 bytes
Variable Size            2.4478E+10 bytes
Database Buffers         4.4074E+10 bytes
Redo Buffers               10338304 bytes
Database mounted.
Database opened.
SQL> ALTER TABLESPACE DATA ADD DATAFILE '/oradata/erpdcdb/DATA12.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Tablespace altered.

SQL>

How to Convert Physical Standby to Logical Standby Database

Before converting physical standby to logical convert resolve the following prerequisite conditions for creating a Logical Standby Database.

1. Determine Support for Data Types and Storage Attributes for Tables. If the primary database contains unsupported tables, log apply services automatically exclude the tables when applying redo logs to the logical standby database.

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_UNSUPPORTED;
OWNER                          TABLE_NAME
------------------------------ ------------------------------
ERPUSER                        EMPLOYEE_HIST

2. Ensure Table Rows in the Primary Database Can Be Uniquely Identified. If your application ensures the rows in a table are unique, you can create a disabled primary key RELY constraint on the table else create the primary key.

SQL> SELECT OWNER, TABLE_NAME FROM DBA_LOGSTDBY_NOT_UNIQUE;
OWNER                          TABLE_NAME
------------------------------ ------------------------------
ERPUSER                          EMPLOYEE_SALARY

SQL> ALTER TABLE EMPLOYEE_SALARY ADD PRIMARY KEY (EMPLOYEE_ID) RELY DISABLE;

Now Convert Physical Standby to Logical Standby Database 

1. Cancel Recovery Manager in Physical Standby Database.

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

2. Apply the parameters change in primary database.

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPPRIM' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPSTAND LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=ENABLE MAX_FAILURE=0 MAX_CONNECTIONS=1 REOPEN=180 NET_TIMEOUT=30 DB_UNIQUE_NAME="ERPPSTAND", VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ERPPRIM'  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE  SCOPE=BOTH SID='*';

3. Build a Dictionary in the Redo Data on Primary Database

EXECUTE DBMS_LOGSTDBY.BUILD;

4. Convert to a Logical Standby Database in Standby Database.

ALTER DATABASE RECOVER TO LOGICAL STANDBY ERPPSTAND;

5. Shutdown and Startup Logical Standby Database in Mount Stage

SHUTDOWN; 

STARTUP MOUNT;

6. Adjust Initialization Parameter on Logical Standby Database

ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ONLINE_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ERPPSTAND' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=ERPPRIM LGWR ASYNC NOAFFIRM DELAY=0 OPTIONAL COMPRESSION=ENABLE MAX_FAILURE=0 MAX_CONNECTIONS=1 REOPEN=180 NET_TIMEOUT=30 DB_UNIQUE_NAME="ERPPRIM", VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)' SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_3='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) DB_UNIQUE_NAME=ERPPSTAND'  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_1=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE  SCOPE=BOTH SID='*';

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_3=ENABLE  SCOPE=BOTH SID='*';

7. Open the Logical Standby Database

SQL> ALTER DATABASE OPEN RESETLOGS;

8. Start Logical Apply on Standby

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

ORA-26787: The row with key ... does not exist in table

Cause: The row to update or delete does not exist in the table of standby database.

Solution: Use DBMS_LOGSTDBY.INSTANTIATE_TABLE  procedure to bring a table back into sync with the version on the primary database.

1. Create public DB link in standby database with primary database.
2. Stop SQL Apply on the logical standby.
3. From DBA user Execute the DBMS_LOGSTDBY.INSTANTIATE_TABLE procedure to bring a table back into sync with the version on the primary database.
4. Start SQL Apply on the logical standby.

LOGSTDBY Apply process AS01 server id=1 pid=50 OS id=17029 stopped
Errors in file /u01/app/oracle/diag/rdbms/erpdrdb/erpdrdb/trace/erpdrdb_as01_17029.trc:
ORA-26787: The row with key ("BRANCHCODE", "HOUR", "MINUTES") = (423423, 4, 34) does not exist in table ERP_USER.TRAN_END_TIME
Errors in file /u01/app/oracle/diag/rdbms/erpdrdb/erpdrdb/trace/erpdrdb_lsp0_16993.trc:
ORA-26808: Apply process AS01 died unexpectedly.
ORA-26787: The row with key ("BRANCHCODE", "HOUR", "MINUTES") = (423423, 4, 34) does not exist in table ERP_USER.TRAN_END_TIME
LOGSTDBY Analyzer process AS00 server id=0 pid=49 OS id=17025 stopped
LOGSTDBY Apply process AS05 server id=5 pid=54 OS id=17037 stopped
LOGSTDBY Apply process AS04 server id=4 pid=53 OS id=17035 stopped
LOGSTDBY Apply process AS02 server id=2 pid=51 OS id=17031 stopped
LOGSTDBY Apply process AS03 server id=3 pid=52 OS id=17033 stopped
LOGMINER: session#=1 (Logical_Standby$), builder MS01 pid=46 OS id=17021 sid=139 stopped
LOGMINER: session#=1 (Logical_Standby$), preparer MS02 pid=47 OS id=17023 sid=203 stopped
LOGMINER: session#=1 (Logical_Standby$), reader MS00 pid=45 OS id=17019 sid=63 stopped

Solution: Perform the following in Standby Database.


SQL> alter database stop logical standby apply;

Database altered.

SQL> exec dbms_logstdby.instantiate_table('ERP_USER','TRAN_END_TIME','PRIMDB');

PL/SQL procedure successfully completed.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.


SQL>

oraagent.bin High Memory Usage

Cause : Grid Infrastructure oraagent.bin process using huge amount of memory and  forking huge number of threads due to missing "ora.LISTENER.lsnr" listener.

Solution: Perform the following steps if you found in oraagent alert 

1. Check te log file of oraagent service and if you really missing to register listener perform the following.

cat $GRID_HOME/log/hostname/agent/ohasd/oraagent_grid/oraagent_grid.log

2017-01-15 19:03:22.679: [ USRTHRD][3433169]{0:237:2} CrsCmd::ClscrsCmdData::stat entity 1 statflag 33 useFilter 0
2017-01-15 19:03:22.745: [ USRTHRD][3433169]{0:237:2} checkCrsStat 2 CLSCRS_STAT ret: 200
2017-01-15 19:03:22.745: [ USRTHRD][3433169]{0:237:2} checkCrsStat 2 clscrs_res_get_op_status CLSCRS_STAT status 210 err_msg CRS-0210: Could not find resource 'ora.LISTENER.lsnr'.
2017-01-15 19:03:22.745: [ USRTHRD][3433169]{0:237:2} AsmCommonAgent::setLocalListener cls::Exception CRS-0210: Could not find resource 'ora.LISTENER.lsnr'.
2017-01-15 19:03:22.745: [ USRTHRD][3433169]{0:237:2} ASM Dedicated Thread }
2017-01-15 19:03:22.745: [ USRTHRD][3433169]{0:237:2} Thread:ASM DedicatedThreadisRunning is reset to false here
2017-01-15 19:03:23.584: [ora.asm][3599]{0:237:2} [check] AsmProxy StartDependeeRes = ora.LISTENER.lsnr
2017-01-15 19:03:23.584: [ USRTHRD][3599]{0:237:2} Thread:ASM DedicatedThreadstart {
2017-01-15 19:03:23.584: [ USRTHRD][3599]{0:237:2} Thread:ASM DedicatedThreadstart }
2017-01-15 19:03:23.585: [ USRTHRD][3433428]{0:237:2} ASM Dedicated Thread {
2017-01-15 19:03:23.594: [ USRTHRD][3433428]{0:237:2} CrsCmd::ClscrsCmdData::stat entity 1 statflag 32 useFilter 0
bash-4.3$

2. Check the cluster resource 

bash-4.3$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DG_BACKUP.dg
               ONLINE  ONLINE       erpdb01
ora.DG_DATA01.dg
               ONLINE  ONLINE       erpdb01
ora.DG_FRA.dg
               ONLINE  ONLINE       erpdb01
ora.DG_OCR_VOT.dg
               ONLINE  ONLINE       erpdb01
ora.DG_REDO.dg
               ONLINE  ONLINE       erpdb01
ora.asm
               ONLINE  ONLINE       erpdb01                Started
ora.ons
               OFFLINE OFFLINE      erpdb01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       erpdb01
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       erpdb01
bash-4.3$

3. Check the status of listener 

bash-4.3$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 15-JAN-2017 19:08:21

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
Start Date                02-DEC-2016 22:16:23
Uptime                    43 days 20 hr. 51 min. 58 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      ON
Listener Parameter File   /u01/app/11.2.0/grid/network/admin/listener.ora
Listener Log File         /u01/app/grid/diag/tnslsnr/erpdb01/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=erpdb01.sonalibank.com.bd)(PORT=1521)))
Services Summary...
Service "sbldcdbXDB" has 1 instance(s).
  Instance "erpdrdb", status READY, has 1 handler(s) for this service...
Service "erpdrdb" has 1 instance(s).
  Instance "erpdrdb", status READY, has 1 handler(s) for this service...
Service "slbdrdb" has 1 instance(s).
  Instance "erpdrdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

4. Add the listener

bash-4.3$ srvctl add listener -l LISTENER -p 1522
bash-4.3$
bash-4.3$ crsctl status resource -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DG_BACKUP.dg
               ONLINE  ONLINE       erpdb01
ora.DG_DATA01.dg
               ONLINE  ONLINE       erpdb01
ora.DG_FRA.dg
               ONLINE  ONLINE       erpdb01
ora.DG_OCR_VOT.dg
               ONLINE  ONLINE       erpdb01
ora.DG_REDO.dg
               ONLINE  ONLINE       erpdb01
ora.LISTENER.lsnr
               OFFLINE OFFLINE      erpdb01
ora.asm
               ONLINE  ONLINE       erpdb01                Started
ora.ons
               OFFLINE OFFLINE      erpdb01
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        ONLINE  ONLINE       erpdb01
ora.diskmon
      1        OFFLINE OFFLINE
ora.evmd
      1        ONLINE  ONLINE       erpdb01
bash-4.3$

5. Stop the listener

bash-4.3$ lsnrctl stop

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 15-JAN-2017 19:26:45

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
The command completed successfully
bash-4.3$

6. Start the listener

bash-4.3$ srvctl start listener
bash-4.3$
bash-4.3$ srvctl status listener
Listener LISTENER is enabled
Listener LISTENER is running on node(s): erpdb01

ORA-01111: name for data file 7 is unknown - rename to correct file

Cause: This error will encounter when standby_file_management is set to manual and when set to auto and file path which is added to the primary database does not exist on standby site or db_file_name_convert is not define.

Solution:

SQL> startup;
ORACLE instance started.

Total System Global Area 3958439936 bytes
Fixed Size                  2259320 bytes
Variable Size            2197816968 bytes
Database Buffers         1744830464 bytes
Redo Buffers               13533184 bytes
Database mounted.
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01111: name for data file 7 is unknown - rename to correct file
ORA-01110: data file 7: '/u01/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00007'

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/erpdb/datafile/system.256.910894817
+DATA/erpdb/datafile/sysaux.257.910894817
+DATA/erpdb/datafile/undotbs1.258.910894817
+DATA/erpdb/datafile/users.259.910894817
+DATA/erpdb/datafile/example.264.910894901
+DATA/erpdb/datafile/undotbs2.265.910894995
+DATA/erpdb/datafile/tbs_gg.299.930621725

7 rows selected.

SQL>

In standby side

SQL> select name from v$datafile;

NAME

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

+DATA/erpdbdr/datafile/system.298.929218701

+DATA/erpdbdr/datafile/sysaux.300.929218693

+DATA/erpdbdr/datafile/undotbs1.302.929218687

+DATA/erpdbdr/datafile/users.299.929218693

+DATA/erpdbdr/datafile/example.297.929218701

+DATA/erpdbdr/datafile/undotbs2.301.929218687

/u01/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00007

7 rows selected.

SQL>

SQL> alter system set standby_file_management='manual';

System altered.

SQL> alter database create datafile '/u01/app/oracle/product/11.2.4/db_1/dbs/UNNAMED00007' as '+DATA/erpdbdr/datafile/tbs_gg.dbf';

Database altered.

SQL> alter system set standby_file_management='auto';

System altered.

SQL>

SQL> select name from v$datafile;

NAME

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

+DATA/erpdbdr/datafile/system.298.929218701

+DATA/erpdbdr/datafile/sysaux.300.929218693

+DATA/erpdbdr/datafile/undotbs1.302.929218687

+DATA/erpdbdr/datafile/users.299.929218693

+DATA/erpdbdr/datafile/example.297.929218701

+DATA/erpdbdr/datafile/undotbs2.301.929218687

+DATA/erpdbdr/datafile/tbs_gg.dbf

7 rows selected.

SQL>