Wednesday, December 13, 2017

ora-01031 insufficient privileges windows

Cause: Your OS (Operating System) user is not a member of ora_dba Group.

Action: Make your operating system user as a member of ora_dba Group.

Operation: The following activity has to perform from Operating System Administrative user.

1. Open the command prompt (cmd) and write compmgmt.msc and hit Enter.


2. Select Group from Local user and Groups 

3. Right Click on ora_dba and Click Add Group to




4. Now Click Add ...



5. Now write your OS username in the box “Enter the object name to select” and click OK” 


Wednesday, October 11, 2017

ORA-00600: internal error code, arguments: [17285], [0x1109F69A0]

ORA-00600: internal error code, arguments: [17285], [0x1109F69A0], [1], [0x700000ED7586810], [], [], [], [], [], [], [], []
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Wed Oct 11 16:37:11 2017
Sweep [inc][1123699]: completed

Cause: It was due to Bug 17984204.

Solution: Change the value of parameter _session_cached_instantiations set it to open_cursors+1 . Restart the instance. Note that this is not an official workaround so it may not work in some environments. 


SQL> show parameter open_cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     6000

SQL> alter system set "_session_cached_instantiations"= 6001 scope=spfile;

System altered.


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

Total System Global Area 3.9272E+10 bytes
Fixed Size                  2593640 bytes
Variable Size            5100276888 bytes
Database Buffers         3.4158E+10 bytes
Redo Buffers               10874880 bytes
Database mounted.
Database opened.
SQL>
SQL> exit

Tuesday, September 26, 2017

ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn]

Cause: This Errors May Occur after a switchover Physical Standby database, and it is due to Oracle Bug 8895202. 

Solution:  Apply the patch where this issue is fixed , Patch 8895202 or Patch Sets >= 11.2.0.2 or Patch Bundles >= 11.1.0.7. If Your database version is >=11.2.0.2 then just change the parameter _ktb_debug_flags to 8. There is no danger in keeping _ktb_debug_flags=8. It gets only activated by invalid scn in itl.

After change the parameter analyze the object which has been affected.

bash-4.3$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Sep 26 14:31:11 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

SQL> alter system set "_ktb_debug_flags"=8 scope=both sid='*';

System altered.

SQL> EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
bash-4.3$
bash-4.3$



ORA-01555 caused by SQL statement below (SQL ID: 89w0nk4mvs89k, Query Duration=0 sec, SCN: 0x0b0e.183c53aa):

Dump continued from file: /u01/app/oracle/diag/rdbms/erpdcdb/erpdcdb1/trace/erpdcdb1_ora_7798850.trc
ORA-00600: internal error code, arguments: [ktbdchk1: bad dscn], [], [], [], [], [], [], [], [], [], [], []

========= Dump for incident 5041023 (ORA 600 [ktbdchk1: bad dscn]) ========
----- Beginning of Customized Incident Dump(s) -----
[ktbdchk] -- ktbgcl4 -- bad dscn
dependent scn: 0x0b0e.6ada21f5 recent scn: 0x0b0e.183bdd5c current scn: 0x0b0e.183bdd5c
----- End of Customized Incident Dump(s) -----

ANALYZE TABLE TABLE_NAME VALIDATE STRUCTURE CASCADE;

Dump continued from file: /u01/app/oracle/diag/rdbms/erpdcdb/erpdcdb2/trace/erpdcdb2_ora_5767956.trc
ORA-00600: internal error code, arguments: [2663], [2830], [698760342], [2830], [1792623805], [], [], [], [], [], [], []

========= Dump for incident 2720876 (ORA 600 [2663]) ========

*** 2017-09-26 13:52:19.908
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=2pbup9vshvk0u) -----
ANALYZE TABLE TABLE_NAME VALIDATE STRUCTURE CASCADE;

Sunday, September 24, 2017

ORA-12519: TNS:no appropriate service handler found

ORA-12519: TNS: no appropriate service handler found

Cause: Number of session and process reached the MAX_UTILIZATION level when listener find the current number of connections has reached maximum load it may set the state of the service handler for an instance to "blocked" and begin refusing incoming client connections.

Solution: Check the value of process parameter and increase the value of the parameter.


[grid@DB ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2017 13:32:38

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1158 refused:0 state:ready
         LOCAL SERVER
Service "SPFTLDB" has 1 instance(s).
  Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:73880 refused:0 state:blocked
         LOCAL SERVER
Service "SPFTLDBXDB" has 1 instance(s).
  Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: DB.spftl.com, pid: 18815>
         (ADDRESS=(PROTOCOL=tcp)(HOST=db.spftl.com)(PORT=43078))
The command completed successfully

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     3
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
SQL> ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

System altered.

SQL>

Bounce the database.

Wednesday, July 26, 2017

Hanging ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL

Cause: Archive log are not applying into standby database but recovery process (MRP) are active, in this situation I decided to restart recovery process but whenever I am applying  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL, the command itself hanging situation.

Solution: Rather than bouncing the standby database just find and kill the MRP process and start the media recovery process.



SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
*
ERROR at line 1:
ORA-01153: an incompatible media recovery is active


SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Database altered.

SQL>

Log Details:

Archived Log entry 20997 added for thread 1 sequence 11504 ID 0xffffffffaec2031b dest 1:
Thu Jul 27 10:33:23 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:45:35 2017
Managed Standby Recovery Canceled (erpdb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:46:23 2017
Recovery coordinator died, shutting down parallel recovery
Thu Jul 27 10:46:25 2017
RFS[516]: Selected log 13 for thread 1 sequence 11506 dbid -********** branch 928567359
Thu Jul 27 10:46:28 2017
Archived Log entry 20998 added for thread 1 sequence 11505 ID 0xffffffffaec2031b dest 1:
Thu Jul 27 10:46:41 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
ORA-1153 signalled during: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION...
Thu Jul 27 10:46:59 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:47:24 2017
RFS[514]: Selected log 21 for thread 2 sequence 11340 dbid -********** branch 928567359
Thu Jul 27 10:47:27 2017
Archived Log entry 20999 added for thread 2 sequence 11339 ID 0xffffffffaec2031b dest 1:
Thu Jul 27 10:47:35 2017
Logmerger died, shutting down parallel recovery slaves
Recovery interrupted!
Recovery interrupted!
Thu Jul 27 10:47:37 2017
Managed Standby Recovery Canceled (erpdb)
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL
Thu Jul 27 10:48:08 2017
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Attempt to start background Managed Standby Recovery process (erpdb)
Thu Jul 27 10:48:08 2017
MRP0 started with pid=38, OS id=9764900
MRP0: Background Managed Standby Recovery process started (erpdb)
 started logmerger process
Thu Jul 27 10:48:14 2017
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION
Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_2_seq_11299.1849.950272469
Media Recovery Log +DG_FRA/erpdb/archivelog/2017_07_25/thread_1_seq_11451.1854.950272385

Monday, June 5, 2017

MRP0: Background Media Recovery process shutdown

Errors with log +FRA/erpdrdb/archivelog/2017_06_02/thread_2_seq_9952.2503.945625685
MRP0: Background Media Recovery terminated with error 10485
Errors in file /u01/app/oracle/diag/rdbms/erpdrdb/erpdrdb/trace/erpdrdb_pr00_9109800.trc:
ORA-10485: Real-Time Query cannot be enabled while applying migration redo.
Recovery interrupted!
MRP0: Background Media Recovery process shutdown (erpdrdb)


Cause: After starting the database as upgrade mode you have to disconnect the recovery database.

Solution: Disconnect the recovery database, shutdown, startup and start the recovery process.

SQL> alter database recover managed standby database disconnect ;

Database altered.

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

Total System Global Area 6.3069E+10 bytes
Fixed Size                  2393728 bytes
Variable Size            8388610432 bytes
Database Buffers         5.4660E+10 bytes
Redo Buffers               18116608 bytes
Database mounted.
SQL> alter database recover managed standby database using current logfile disconnect ;

Database altered.

SQL>

Tuesday, May 23, 2017

How to load sql plan baseline from AWR

1. Find out the SQL ID and Number of SQL Plan exist in AWR History.

SELECT SQL_ID, COUNT (*)
    FROM (SELECT DISTINCT SQL_ID, PLAN_HASH_VALUE FROM DBA_HIST_SQL_PLAN
    WHERE SQL_ID='&SQL_ID')
GROUP BY SQL_ID
ORDER BY 2 DESC;



2. Find out the SQL Plan Hash Value using SQL ID.

SELECT DISTINCT PLAN_HASH_VALUE,SQL_ID  FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='&SQL_ID';

3. Check the SQL Execution Plan Cost and choose the best one. Note that you have to select Hash Vale and SNAP_ID for which you want to load into your Plan Baseline.

SELECT SS.SNAP_ID,
     SS.INSTANCE_NUMBER,
     BEGIN_INTERVAL_TIME,
     SQL_ID,
     PLAN_HASH_VALUE,OPTIMIZER_COST,
     DISK_READS_TOTAL,
     BUFFER_GETS_TOTAL,
     ROWS_PROCESSED_TOTAL,
     CPU_TIME_TOTAL,
     ELAPSED_TIME_TOTAL,
     IOWAIT_TOTAL,
     NVL (EXECUTIONS_DELTA, 0) EXECS,
       (  ELAPSED_TIME_DELTA
        / DECODE (NVL (EXECUTIONS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
     / 1000000
        AVG_ETIME,
     (  BUFFER_GETS_DELTA
      / DECODE (NVL (BUFFER_GETS_DELTA, 0), 0, 1, EXECUTIONS_DELTA))
        AVG_LIO
FROM DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
WHERE     SQL_ID = '&SQL_ID'
     AND SS.SNAP_ID = S.SNAP_ID
     AND SS.INSTANCE_NUMBER = S.INSTANCE_NUMBER
     AND EXECUTIONS_DELTA > 0
ORDER BY 1, 2, 3;


4. Check that STS has already existed or not in history if it is exist chose a different name of drop the existing one.

SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22'));

BEGIN
   DBMS_SQLTUNE.DROP_SQLSET (sqlset_name => 'STS_f9948h4y2aa22');
END;


5. Create STS.

BEGIN
  DBMS_SQLTUNE.CREATE_SQLSET(
    sqlset_name => 'STS_f9948h4y2aa22',
    description => 'SQL Tuning Set for loading plan into SQL Plan Baseline');
END;

6. Load STS 

DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE(P)
    FROM TABLE(
       dbms_sqltune.select_workload_repository(begin_snap=>6450, end_snap=>6451,basic_filter=>'sql_id = ''f9948h4y2aa22''',attribute_list=>'ALL')
              ) p;
     DBMS_SQLTUNE.LOAD_SQLSET( sqlset_name=> 'STS_f9948h4y2aa22', populate_cursor=>cur);
  CLOSE cur;
END;

7. Check the loaded Plan details.

SELECT
  first_load_time          ,
  executions as execs              ,
  parsing_schema_name      ,
  elapsed_time  / 1000000 as elapsed_time_secs  ,
  cpu_time / 1000000 as cpu_time_secs           ,
  buffer_gets              ,
  disk_reads               ,
  direct_writes            ,
  rows_processed           ,
  fetches                  ,
  optimizer_cost           ,
  sql_plan                ,
  plan_hash_value          ,
  sql_id                   ,
  sql_text
   FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(sqlset_name => 'STS_f9948h4y2aa22')
             );


8. In final Stage Load SQL Plan from STS (AWR History) using plan Hash Value.

DECLARE
my_plans pls_integer;
BEGIN
  my_plans := DBMS_SPM.LOAD_PLANS_FROM_SQLSET(
    sqlset_name => 'STS_f9948h4y2aa22', 
    basic_filter=>'plan_hash_value = ''3166698470'''
    );
END;

9. Check your loaded plan from baseline.

SELECT * FROM dba_sql_plan_baselines ;


Sunday, May 14, 2017

Resolve Standby Gaps by Applying Archive log File

Description: In my database archive log file are deleted from the primary database after taken RMAN backup. In physical standby database, there is log gap which is already deleted from the primary database and MRP process is waiting for the log file.

Solution:
1. Find out the backup archive log file.
list backup of archivelog sequence 8760;
2. Transfer backup archive log file into Standby database.
scp 
3. Restore archive log file using log sequence number.
restore archivelog from sequence 8760 until sequence 8760 thread=1;

Checking in Standby Database for find out the archive log file details.

SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, RESETLOG_ID,
 THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS
 FROM V$MANAGED_STANDBY;

PROCESS          PID STATUS       CLIENT_P CLIENT_PID    RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ------------- ----------- ---------- ---------- ---------- ---------- ----------
ARCH         5636392 CLOSING      ARCH     5636392         928567359          2       9357      75776        317          0
ARCH         2097262 CLOSING      ARCH     2097262         928567359          1       9461     552960        541          0
ARCH         8061076 CLOSING      ARCH     8061076         928567359          2       9359     364544       1996          0
ARCH         4390984 CONNECTED    ARCH     4390984                 0          0          0          0          0          0
MRP0         8323150 WAIT_FOR_GAP N/A      N/A             928567359          1       8760          0          0          0
RFS          7536982 IDLE         ARCH     8257884                 0          0          0          0          0          0
RFS          2818346 IDLE         ARCH     10682474                0          0          0          0          0          0
RFS          6356994 IDLE         LGWR     14876892        928567359          2       9360     461397          2          0
RFS          9175274 IDLE         LGWR     8061206         928567359          1       9462    1506619         12          0
RFS         10289552 IDLE         UNKNOWN  10223730                0          0          0          0          0          0

10 rows selected.

SQL> SELECT NAME
  FROM V$ARCHIVED_LOG
 WHERE THREAD# = 1
AND DEST_ID = 1
AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

no rows selected

Checking in primary database is there any archive log file exist or not if it does not then find the backup file and transfer into the standby database.

SQL> SELECT NAME, STANDBY_DEST, DELETED
  FROM GV$ARCHIVED_LOG
 WHERE THREAD# = 1
 AND DEST_ID = 1
 AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

NAME                                                         STA DEL
------------------------------------------------------------ --- ---
                                                             NO  YES
                                                             NO  YES

SQL>

Find out the archive log file backup location

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 14 12:54:18 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ******* (DBID=******* )

RMAN> list backup of archivelog sequence 8760 thread 1;

using target database control file instead of recovery catalog

List of Backup Sets
===================


BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3308    3.22G      DISK        00:06:39     09-MAY-17
        BP Key: 3308   Status: AVAILABLE  Compressed: YES  Tag: ORCL_ARCHIVE
        Piece Name: /rmanbackup/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

  List of Archived Logs in backup set 3308
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8760    12151347030306 08-MAY-17 12151347165919 08-MAY-17

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
3323    13.44G     SBT_TAPE    00:05:57     09-MAY-17
        BP Key: 3323   Status: AVAILABLE  Compressed: NO  Tag: TAG20170509T051719
        Handle: dks3piuo_1_1   Media: 2851

  List of Archived Logs in backup set 3323
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    8760    12151347030306 08-MAY-17 12151347165919 08-MAY-17

RMAN> exit


Recovery Manager complete.
bash-4.3$

Transfer into standby database 

scp /rmanbackup/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch oracle@****:/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch

Connect RMAN from Standby database and restore archive log file 

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Sun May 14 13:36:45 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ***** (DBID=******* )

RMAN> catalog backuppiece '/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch';

cataloged backup piece
backup piece handle=/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch RECID=170 STAMP=943969336

RMAN> restore archivelog from sequence 8760 until sequence 8760 thread=1;

Starting restore at 14-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=6114 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8760
channel ORA_DISK_1: reading from backup piece /rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch
channel ORA_DISK_1: piece handle=/rman/RMAN_ARC_inc_******* _943495084_3496_1_d8s3p5tc_1_1.arch tag=ORCL_ARCHIVE
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:08:05
Finished restore at 14-MAY-17

RMAN> exit


Recovery Manager complete.

Now Standby database will start applying the archive log file. If it is not started just stop and start recovery of the standby database.

Checking the applying start or not.


SQL> SELECT NAME
  FROM V$ARCHIVED_LOG
 WHERE THREAD# = 1
AND DEST_ID = 1
AND SEQUENCE# BETWEEN 8760 AND 8760;  2    3    4    5

NAME
--------------------------------------------------------------------------------
+FRA/****/archivelog/2017_05_14/thread_1_seq_8760.2508.943969813

SQL>
SQL> SELECT PROCESS, PID, STATUS, CLIENT_PROCESS, CLIENT_PID, RESETLOG_ID,
 THREAD#, SEQUENCE#, BLOCK#, BLOCKS, DELAY_MINS
 FROM V$MANAGED_STANDBY;   2    3

PROCESS          PID STATUS       CLIENT_P CLIENT_PID                               RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ---------------------------------------- ----------- ---------- ---------- ---------- ---------- ----------
ARCH         5636392 CLOSING      ARCH     5636392                                    928567359          2       9282     129024        828          0
ARCH         2097262 CLOSING      ARCH     2097262                                    928567359          1       9462    1767424       1537          0
ARCH         8061076 CLOSING      ARCH     8061076                                    928567359          2       9359     364544       1996          0
ARCH         4390984 CONNECTED    ARCH     4390984                                            0          0          0          0          0          0
MRP0         8323150 APPLYING_LOG N/A      N/A                                        928567359          1       8799      12876     944628          0
RFS          7536982 RECEIVING    UNKNOWN  8257884                                    928567359          1       8941      32769       2048          0
RFS         10355038 IDLE         ARCH     10682474                                           0          0          0          0          0          0
RFS          8454490 IDLE         LGWR     14876892                                   928567359          2       9362     442233          4          0
RFS          9175274 IDLE         LGWR     8061206                                    928567359          1       9463    1334595          5          0
RFS         10289552 IDLE         UNKNOWN  10223730                                           0          0          0          0          0          0
RFS          2818374 RECEIVING    ARCH     14877160                                           0          0          0          0          0          0

PROCESS          PID STATUS       CLIENT_P CLIENT_PID                               RESETLOG_ID    THREAD#  SEQUENCE#     BLOCK#     BLOCKS DELAY_MINS
--------- ---------- ------------ -------- ---------------------------------------- ----------- ---------- ---------- ---------- ---------- ----------
RFS          6095152 RECEIVING    UNKNOWN  29163686                                   928567359          2       9296     761858       2048          0
RFS          7733286 RECEIVING    UNKNOWN  10092786                                   928567359          1       8940      49153       2048          0

13 rows selected.

SQL>



Monday, April 24, 2017

Export metadata only using expdp.

expdp system/password schemas=SCHEMA_NAME directory=DMPDIR dumpfile=file_name.dmp logfile=explogfile.log content=METADATA_ONLY

Friday, April 21, 2017

Human Resource Management System (C Programming Project)

Overview: The followings feature has been developed using C programming Language.

1. Add New Employee
2. List of Employees
3. Employee Attendance
4. Employee Payment
5. Employee Payment List
6. Employee Search
7. Exit


#include <stdio.h>
#include <conio.h>
#include <windows.h>
#include <string.h>  
#include <ctype.h>

COORD coord = {0,0}; /// top-left corner of window

void gotoxy(int x,int y)
{
    coord.X = x;
    coord.Y = y;
    SetConsoleCursorPosition(GetStdHandle(STD_OUTPUT_HANDLE),coord);
}

int IsNumberValue(char *s)
{
    //return value : true if the string is all numbers.
    while(*s)
        if(!isdigit(*s++))
            return 0;
    return 1;
}

int IsCharacterValue(char *s)
{
    //return value : true if the string is all numbers. if (*c <= ' ' || *c >= 127) return 0;

    while(*s)
        if(!isalpha(*s++))
            return 0;
    return 1;
}

int AddressCheck(char *array)
{
    int j,x;
    char val;
    int val_flag=0;

    j=strlen(array);
    for (x=0; x<j; x++)
    {
        val=array[x];
        {
            if (isalpha(val) ||(val)==' ' || (val)=='#' || (val)=='_') // check every character of str
            {
                val_flag=0;
            }
            else
            {
                val_flag=1;
                return 1;
            }
        }
    }
    return 0;
}

int EmailCheck(char *array)
{
    int j,x;
    char val;

    j=strlen(array);
    for (x=0; x<j; x++)
    {
        val=array[x];
        if (val=='@')
            return 1;
    }
    return 0;
}

/** Main function started */

int main()
{
    FILE *file_employee;
    FILE *file_attendance;
    FILE *file_experience;
    FILE *file_education;
    FILE *file_payment;

    char another, choice;

    /** structure that represent a employee */
    struct employee
    {
        char employee_name[100];
        char employee_city[100];
        char employee_mob_no[11];
        char employee_email[100];
        char employee_address[100];
        float basic_salary;
    };

    struct employee emp_temp; /// structure variable creation

    struct employee_attendance
    {
        int employee_id;
        int attend_month;
        int attend_year;
        int total_office;
        int total_attendance;
        int total_absent;
    };

    struct employee_attendance employee_attendance_temp;

    struct employee_experience
    {
        char company_name[200];
        char company_location[200];
        char position[200];
        char responsibilities[1000];
        char start_date[200];
        char end_date[200];
    };

    struct employee_experience employee_experience_temp;

    struct employee_education
    {
        char certification[200];
        char institute[200];
        char pass_year[200];
        char result[200];
    };

    struct employee_education employee_education_temp;

    struct employee_payment
    {
        int employee_id;
        int pay_month;
        int pay_year;
        char pay_date[200];;
        int total_payment;
    };

    struct employee_payment employee_payment_temp;

    char file_data[200][200];
    char single_line[200];
    char search_str[200];
    int match_flag=0;

    void emp_experience()
    {
        char another_exp='y';
        while (another_exp=='y'||another_exp=='Y')
        {
            system("cls");
            gotoxy(30,1);
            printf("\n Employment History ");
            file_experience = fopen("file_experience.txt", "a");
            fseek(file_experience,0,SEEK_END);

Position11:
            printf("\nCompany Name : ");
            gets(employee_experience_temp.company_name);

            if(!IsCharacterValue(employee_experience_temp.company_name))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position11;
            }

Position12:
            printf("\nCompany Location : ");
            gets(employee_experience_temp.company_location);

            if(!IsCharacterValue(employee_experience_temp.company_location))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position12;
            }

Position13:
            printf("\nPosition Held : ");
            gets(employee_experience_temp.position);

            if(!IsCharacterValue(employee_experience_temp.position))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position13;
            }
            printf("\nResponsibilities : ");
            gets(employee_experience_temp.responsibilities);

            printf("\nFrom Date : ");
            gets(employee_experience_temp.start_date);
            printf("\nTo Date : ");
            gets(employee_experience_temp.end_date);
            fprintf(file_experience, "%s,%s,%s,%s,%s,%s\n", employee_experience_temp.company_name,
                    employee_experience_temp.company_location,
                    employee_experience_temp.position,employee_experience_temp.responsibilities,
                    employee_experience_temp.start_date,employee_experience_temp.end_date
                   ); // write to file
            fclose(file_experience);
            printf("\nAdd Another Employment History (y/n) ");
            another_exp=getche();
        }
    }

    void emp_education()
    {
        char another_education='y';
        while (another_education=='y'||another_education=='Y')
        {
            system("cls");
            gotoxy(30,1);
            printf("\n Academic Qualification ");
            file_education = fopen("file_education.txt", "a");
            fseek(file_education,0,SEEK_END);
            printf("\nDegree Title : ");
            gets(employee_education_temp.certification);
            printf("\nInstitute Name : ");
            gets(employee_education_temp.institute);

Position21:
            printf("\nYear of Passing : ");
            gets(employee_education_temp.pass_year);

            if(!IsNumberValue(employee_education_temp.pass_year))
            {
                printf("\nSorry, Input is invalid\n");
                goto Position21;
            }


            printf("\nResult : ");
            gets(employee_education_temp.result);
            fprintf(file_education, "%s,%s,%s,%s\n", employee_education_temp.certification,employee_education_temp.institute,employee_education_temp.pass_year,employee_education_temp.result
                   );
            fclose(file_education);
            printf("\nAdd Another Academic Qualification (y/n) ");
            another_education=getche();
        }
        printf("\nAdd Employment History (y/n) ");
        another = getche();
        if (another=='y' || another=='Y')
        {
            emp_experience();
        }
    }


    int employee_id_gen()
    {
        FILE * file_empid;
        int employee_id;

        file_empid=fopen("employee_id.txt", "r");
        if (!file_empid)
        {
            fclose(file_empid);
            file_empid=fopen("employee_id.txt", "w");
            fprintf(file_empid,"100");
            employee_id=100;
        }
        else
        {
            fscanf(file_empid, "%d", &employee_id);
            employee_id ++ ;
            fclose(file_empid);
            file_empid=fopen("employee_id.txt", "w");
            printf("%d", employee_id);
            fprintf(file_empid,"%d", employee_id);
        }
        fclose(file_empid);
        return employee_id ;
    }


    void employee_add()
    {
        int new_emp_id=100 ;
        new_emp_id = employee_id_gen();
        system("cls");
        file_employee = fopen("file_employee.txt", "a");  /// Open the using append mode to add employee information with existing
        /// information.
        fseek(file_employee,0,SEEK_END); /// search the file and move cursor to end of the file
        gotoxy(30,1);
        printf("Employee Details Information \n");
        another = 'n';

Position1:
        printf("\nEmployee name: ");
        gets(emp_temp.employee_name);

        if(!IsCharacterValue(emp_temp.employee_name))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position1;
        }

Position2:
        printf("\nCity: ");
        gets(emp_temp.employee_city);

        if(!IsCharacterValue(emp_temp.employee_city))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position2;
        }


Position3:
        printf("\nMobile Number: ");
        gets(emp_temp.employee_mob_no);

        if(!IsNumberValue(emp_temp.employee_mob_no))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position3;
        }

Position4:
        printf("\nEmail: ");
        gets(emp_temp.employee_email);

        if(!EmailCheck(emp_temp.employee_email))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position4;
        }

Position5:
        printf("\nAddress: ");
        gets(emp_temp.employee_address);

        if(AddressCheck(emp_temp.employee_address))
        {
            printf("\nSorry, Input is invalid\n");
            goto Position5;
        }

        printf("\nEmployee Salary: ");
        scanf("%f", &emp_temp.basic_salary);

        fprintf(file_employee, "%d,%s,%s,%s,%s,%s,%f\n",new_emp_id,emp_temp.employee_name,emp_temp.employee_city,emp_temp.employee_mob_no,emp_temp.employee_email,emp_temp.employee_address,emp_temp.basic_salary);
        fclose(file_employee);
        printf("\nEmployee Added With ID %d \n",new_emp_id);
        printf("\nAdd Academic Qualification (y/n) ");
        another = getche();
        if (another=='y' || another=='Y')
        {
            emp_education();
        }
    }

    void employee_list()
    {
        system("cls");
        int c;
        FILE *file_employee;
        file_employee = fopen("file_employee.txt", "r");
        if (file_employee)
        {
            while ((c = getc(file_employee)) != EOF)
                putchar(c);
            fclose(file_employee);
        }
        getch();
    }

    void employee_search()
    {
        system("cls");
        int c;
        FILE *file_employee;
        file_employee = fopen("file_employee.txt", "r");
        if (file_employee)
        {
            while ((c = getc(file_employee)) != EOF)
                putchar(c);
            fclose(file_employee);
        }
        getch();
    }

    void emp_attendance()
    {
        char another_attendance='y';
        while (another_attendance=='y'||another_attendance=='Y')
        {
            system("cls");
            file_attendance = fopen("file_attendance.txt", "a");
            fseek(file_attendance,0,SEEK_END);
            int total_absent=0;
            printf("\nEmployee ID : ");
            scanf("%i",&employee_attendance_temp.employee_id);
            printf("\nMonth : ");
            scanf("%i",&employee_attendance_temp.attend_month);
            printf("\nYear : ");
            scanf("%i",&employee_attendance_temp.attend_year);
            printf("\nTotal Office: ");
            scanf("%i", &employee_attendance_temp.total_office);
            printf("\nTotal Attend : ");
            scanf("%i", &employee_attendance_temp.total_attendance);
            total_absent=employee_attendance_temp.total_office-employee_attendance_temp.total_attendance;
            printf("\nTotal Absent :%i ",total_absent);
            fprintf(file_attendance, "%i,%i,%i,%i,%i,%i\n", employee_attendance_temp.employee_id,
                    employee_attendance_temp.attend_month,
                    employee_attendance_temp.attend_year,
                    employee_attendance_temp.total_office,
                    employee_attendance_temp.total_attendance,
                    total_absent
                   ); // write to file
            fclose(file_attendance);
            printf("\nAdd Another Attendance (y/n) ");
            another_attendance=getche();
        }
    }

    void emp_payment()
    {
        char another_payment='y';
        while (another_payment=='y'||another_payment=='Y')
        {
            system("cls");
            file_payment = fopen("file_payment.txt", "a");
            fseek(file_payment,0,SEEK_END);
            int total_absent=0;
            printf("\nEmployee ID : ");
            scanf("%i",&employee_payment_temp.employee_id);
            printf("\nMonth : ");
            scanf("%i",&employee_payment_temp.pay_month);
            printf("\nYear : ");
            scanf("%i",&employee_payment_temp.pay_year);
            printf("\nPayment Date : ");
            scanf("%s", &employee_payment_temp.pay_date);
            printf("\nTotal Payment : ");
            scanf("%i", &employee_payment_temp.total_payment);

            fprintf(file_payment, "%i,%i,%i,%s,%i\n", employee_payment_temp.employee_id,
                    employee_payment_temp.pay_month,
                    employee_payment_temp.pay_year,
                    employee_payment_temp.pay_date,
                    employee_payment_temp.total_payment
                   );
            fclose(file_payment);
            printf("\nAdd Another Payment (y/n) ");
            another_payment=getche();
        }
    }

    void payment_list()
    {

        system("cls");
        int line;
        FILE *file_payment;
        file_payment = fopen("file_payment.txt", "r");
        if (file_payment)
        {
            while ((line = getc(file_payment)) != EOF)  /// Print the information untile End of File.
                putchar(line);
            fclose(file_payment);
        }
        getch();
    }

    int string_comparing(char *array)
    {
        int i,j,array_length=0,total_str=0;
        int search_str_length=0;
        char val;

        array_length=strlen(array);
        search_str_length=strlen(search_str);
        match_flag=0;

        for (i=0; i<array_length; i++)
        {
            val=array[i];
            total_str=total_str+1;
            if (match_flag==1)
            {
                break;
            }
            if (val==',')
            {
                for(j=0; j<search_str_length; j++)
                {
                    if (array[(i-(total_str-1)+j)]==search_str[j])
                    {
                        match_flag=1;
                    }
                    else
                    {
                        match_flag=0;
                    }
                }
                total_str=0;
            }
        }
        return 0;
    }

    void employee_searching()
    {
        system("cls");
        int i=0,n=0;
        match_flag=0;
        int total_result=0;
        FILE *file;
        printf("\nEnter Search String : ");
        gets(search_str);
        file = fopen("file_employee.txt", "r");
        while(fgets(file_data[n++],200,file))
        {
        }
        fclose(file);

        for (i=0 ; i<n; i++)
        {
            strcpy(single_line,file_data[i]);
            string_comparing(single_line);
            if (match_flag==1)
            {
                printf("%s",single_line);
                ++total_result;
            }
        }

        if (total_result==0)
        {
            printf("Result Not Found.");
        }

        getch();
    }

    system("cls"); ///clear the console window
    gotoxy(30,1);
    printf("Please press enter to start the project");
    getch();  /// Waiting for pressing enter.
    /// infinite loop continues untile the break statement encounter
    while(1)
    {
        system("cls"); ///clear the console window
        gotoxy(30,1);
        printf("1. Add New Employee");
        gotoxy(30,2);
        printf("2. List of Employees");
        gotoxy(30,3);
        printf("3. Employee Attendance");
        gotoxy(30,4);
        printf("4. Employee Payment");
        gotoxy(30,5);
        printf("5. Employee Payment List");
        gotoxy(30,6);
        printf("6. Employee Search");
        gotoxy(30,7);
        printf("7. Exit");
        gotoxy(30,9);
        printf("Your Choice: ");
        fflush(stdin); /// flush the input buffer
        choice  = getche(); /// get the input from keyboard
        switch(choice)
        {
        case '1':
            employee_add();
            break;
        case '2':
            employee_list();
            break;
        case '3':
            emp_attendance();
            break;
        case '4':
            emp_payment();
            break;
        case '5':
            payment_list();
            break;
        case '6':
            employee_searching();
            break;
        case '7':
            exit(0); /// exit from the program
        }
    }
    return 0;
}

Thursday, April 20, 2017

Creating Unique Index By Using Table Name and Column Serial Number.

1. Create procedure for generating scripts and creating Unique Constraints.

CREATE OR REPLACE PROCEDURE SP_TABLE_CONSTRAINT
(P_TABLE_NAME IN VARCHAR2,
 P_COLUMN_SL IN VARCHAR2
 )
 IS
 V_SQL_SCRIPTS VARCHAR2(4000);
 V_COLUMN_LIST VARCHAR2(1000);
 BEGIN
    SELECT TO_CHAR(WM_CONCAT(COLUMN_NAME)) COLUMN_LIST
    INTO V_COLUMN_LIST
    FROM USER_TAB_COLUMNS C, (SELECT REGEXP_SUBSTR(P_COLUMN_SL,'[^,]+', 1, LEVEL) COLUMN_SL FROM DUAL
     CONNECT BY REGEXP_SUBSTR(P_COLUMN_SL, '[^,]+', 1, LEVEL) IS NOT NULL) S
    WHERE TABLE_NAME=P_TABLE_NAME
    AND C.COLUMN_ID=S.COLUMN_SL;
   
    V_SQL_SCRIPTS:='CREATE UNIQUE INDEX IDX_'||P_TABLE_NAME||'_UNQ_'||REPLACE(P_COLUMN_SL,',')||' ON '||P_TABLE_NAME||'('||V_COLUMN_LIST||')';
   
    EXECUTE IMMEDIATE V_SQL_SCRIPTS;
   
    DBMS_OUTPUT.PUT_LINE(V_SQL_SCRIPTS);
 
  EXCEPTION
 
     WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20100,SQLERRM);
   
 END;

2. Call Procedure for Creating Unique Constraints.

DECLARE
  P_TABLE_NAME VARCHAR2(32767);
  P_COLUMN_SL VARCHAR2(32767);

BEGIN
  P_TABLE_NAME := 'CONNECTIONS';
  P_COLUMN_SL := '1,2';

  SP_TABLE_CONSTRAINT ( P_TABLE_NAME, P_COLUMN_SL );

END;

Tuesday, April 4, 2017

ORA-00245: control file backup failed; target is likely on a local file system

Cause: From 11gR2 onwards, the controlfile backup happens without holding the controlfile enqueue. For non-RAC database, this doesn't change anything. But for RAC database, due to the changes made to the controlfile backup mechanism in 11gR2, any instance in the cluster may write to the snapshot controlfile. Due to this snapshot controlfile need to be visible to all instances. 

Solution: The snapshot controlfile MUST be accessible by all nodes of a RAC database, if the snapshot controlfile does not reside on a shared device error will be raised at the time of RMAN backup while taking snapshot of controlfile. So placed controlfile into the Shared Disk.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================

RMAN-03009: failure of backup command on CH3 channel at 04/05/2017 01:54:55
ORA-00245: control file backup failed; target is likely on a local file system

RMAN>

bash-4.3$ rman target/

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Apr 5 11:32:13 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ERPDB (DBID=2929713817)

RMAN> show snapshot controlfile name;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name erpdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_erpdb1.f'; # default

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DG_DATA/erpdb/controlfile/snapcf_erpdb.f';

new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DG_DATA/erpdb/controlfile/snapcf_erpdb.f';
new RMAN configuration parameters are successfully stored

RMAN> show snapshot controlfile name;

RMAN configuration parameters for database with db_unique_name erpdb are:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+DG_DATA/erpdb/controlfile/snapcf_erpdb.f';

RMAN> exit


Recovery Manager complete.
bash-4.3$

Monday, March 20, 2017

ORA-00059: maximum number of DB_FILES exceeded

SQL>  sho parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200
SQL> alter system set db_files = 1000 scope = spfile;

System altered.

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

Total System Global Area 1.9003E+10 bytes
Fixed Size                  2399752 bytes
Variable Size            2256536056 bytes
Database Buffers         1.6735E+10 bytes
Redo Buffers                9273344 bytes
Database mounted.
Database opened.
SQL> sho parameter db_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     1000

Wednesday, March 15, 2017

Using SQL Loader

1. Create Table 

CREATE TABLE H1BCASE_DATA
( CASE_SL             NUMBER(10),
  CASE_STATUS         VARCHAR2(1000 BYTE),
  EMPLOYER_NAME       VARCHAR2(1000 BYTE),
  SOC_NAME            VARCHAR2(1000 BYTE),
  JOB_TITLE           VARCHAR2(1000 BYTE),
  FULL_TIME_POSITION  VARCHAR2(1000 BYTE),
  PREVAILING_WAGE     VARCHAR2(1000 BYTE),
  YEAR                VARCHAR2(1000 BYTE),
  WORKSITE            VARCHAR2(1000 BYTE),
  LON                 VARCHAR2(1000 BYTE),
  LAT                 VARCHAR2(1000 BYTE)
);

2. Create ctl file.

bash-4.3$ vi h1b_kaggle.ctl

LOAD DATA
INFILE '/home/oracle/h1b_kaggle.csv'
BADFILE '/home/oracle/h1b_kaggle.bad'
DISCARDFILE '/home/oracle/h1b_kaggle.dsc'
INSERT INTO TABLE H1BCASE_DATA
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(CASE_SL, CASE_STATUS, EMPLOYER_NAME, SOC_NAME, JOB_TITLE, FULL_TIME_POSITION, PREVAILING_WAGE, YEAR, WORKSITE, LON, LAT)

3. Load data into table 

bash-4.3$ sqlldr userid=HR/HR control=h1b_kaggle.ctl log=h1b_kaggle.log

Sunday, February 19, 2017

Split word from sentence using oracle SQL Query

SQL> SELECT LEVEL, REGEXP_SUBSTR('RAJIB NARSINGDI DHAKA BANGLADESH','[^ ]+', 1, LEVEL) FROM DUAL
 CONNECT BY REGEXP_SUBSTR('RAJIB NARSINGDI DHAKA BANGLADESH', '[^ ]+', 1, LEVEL) IS NOT NULL;  2

     LEVEL REGEXP_SUBSTR('RAJIBNARSINGDIDHA
---------- --------------------------------
         1 RAJIB
         2 NARSINGDI
         3 DHAKA
         4 BANGLADESH

SQL> SELECT ROWNUM SL, EXTRACTVALUE(XT.COLUMN_VALUE, 'e') COLUMN_VALUES
  2              FROM TABLE(XMLSEQUENCE(EXTRACT(XMLTYPE('<coll><e>' ||
  3                                                     REPLACE(REPLACE(REPLACE('RAJIB NARSINGDI DHAKA BANGLADESH','&',''),':',''),
  4                                                             ' ',
  5                                                             '</e><e>') ||
  6                                                     '</e></coll>'),
  7                                             '/coll/e'))) XT;

     SL COLUMN_VALUES
---------- --------------------------------
         1 RAJIB
         2 NARSINGDI
         3 DHAKA
         4 BANGLADESH

SQL>

Sunday, January 22, 2017

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.

Thursday, January 19, 2017

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;