Uncheck DNE LightWeight Filter from your Adapter's properties
Tuesday, February 25, 2020
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.
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>
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;
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
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.
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>
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>
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;
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>
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;
}
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;
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>
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
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
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>
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>
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.
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.
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>
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>
Subscribe to:
Posts (Atom)