In RAC or ASM environment, we missed the ‘+’ sign while adding datafile in tablespace. we got error in ora-01110, ora-27037 in alert log. ORA-01157: cannot identify/lock data file 3129 – see DBWR trace file ORA-01110: data file 3129: ‘/home/oracle/product/10.2.0/db/dbs/DATA’ ORA-27037: unable to obtain file status ORA-01186: file 3129 failed verification tests ORA-01157: cannot identify/lock […]
All posts by Skant Gupta
Fractured block in Oracle
A block in which the header and footer are not consistent at a given SCN. In a user-managed backup, an operating system utility can back up a datafile at the same time that DBWR is updating the file. It is possible for the operating system utility to read a block in a half-updated state, so that […]
How to Fail Over and Reinstate Oracle Database on Oracle Cloud
In this article, we will show you how to fail over and reinstate Oracle Database in the Oracle Cloud. This article is written by Skant Gupta and Joel Pérez in Oracle OTN. Note: To use the procedure presented in this article, you need two Oracle Cloud database instances. Also, the procedure presented was performed on Oracle Database […]
Top 10 new Features of Oracle 12c Release 2
Hello Everyone, As I was going to Oracle through Oracle 12c release 2 documentation, I came across following important new features. This is not the complete list, there are many more new features came out with Oracle release 12c release 2. Multi-Instance Redo Apply : With Oracle 12c Release 2, you can apply redo logs to […]
Oracle RAC: Applying and Removing PSU in Database Home with Conflict Detection on Oracle 12c
In this article we will present how to apply and reverse a PSU applied to Database Home on a node with the conflict resolution method. To do this you need to have at least a 2-node RAC Database. If you want to patch on Grid Infrastructure Home in Oracle 12c then click here Our test environment: Grid […]
Oracle RAC – Applying and Removing PSUs in Grid Infrastructure Home with Conflict Detection (12.1.0.2.0)
In this article we will present how to apply and remove PSU in Grid Infrastructure Home on only one node using the conflict resolution method with roll forward method. To do this we need a RAC of at least 2 nodes. If you want to patch on Database Home in Oracle 12c then click here […]
Extracts DDL statements for specified objects
exec dbms_metadata.set_transform_param( dbms_metadata.session_transform,’SQLTERMINATOR’, TRUE); select dbms_metadata.get_ddl( object_type, object_name, owner ) from all_objects where object_type NOT LIKE ‘%PARTITION’ AND object_type NOT LIKE ‘%BODY’ AND upper(object_name) LIKE upper(CASE WHEN INSTR(‘&1′,’.’) > 0 THEN SUBSTR(‘&1’,INSTR(‘&1′,’.’)+1) ELSE ‘&1’ END ) AND owner LIKE CASE WHEN INSTR(‘&1′,’.’) > 0 THEN UPPER(SUBSTR(‘&1’,1,INSTR(‘&1′,’.’)-1)) ELSE user END /
Displays information on all long operations
COLUMN sid FORMAT 999 COLUMN serial# FORMAT 9999999 COLUMN machine FORMAT A30 COLUMN progress_pct FORMAT 99999999.00 COLUMN elapsed FORMAT A10 COLUMN remaining FORMAT A10 SELECT s.sid, s.serial#, s.machine, ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed, ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining, ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct FROM v$session s, v$session_longops sl WHERE s.sid = sl.sid AND s.serial# = […]
Lists all locked objects for whole RAC
COLUMN owner FORMAT A20 COLUMN username FORMAT A20 COLUMN object_owner FORMAT A20 COLUMN object_name FORMAT A30 COLUMN locked_mode FORMAT A15 SELECT b.inst_id, b.session_id AS sid, NVL(b.oracle_username, ‘(oracle)’) AS username, a.owner AS object_owner, a.object_name, Decode(b.locked_mode, 0, ‘None’, 1, ‘Null (NULL)’, 2, ‘Row-S (SS)’, 3, ‘Row-X (SX)’, 4, ‘Share (S)’, 5, ‘S/Row-X (SSX)’, 6, ‘Exclusive (X)’, b.locked_mode) […]
FIND THE ARCHIVE LAG BETWEEN PRIMARY AND STANDBY
select LOG_ARCHIVED-LOG_APPLIED “LOG_GAP” from (SELECT MAX(SEQUENCE#) LOG_ARCHIVED FROM V$ARCHIVED_LOG WHERE DEST_ID=1 AND ARCHIVED=’YES’), (SELECT MAX(SEQUENCE#) LOG_APPLIED FROM V$ARCHIVED_LOG WHERE DEST_ID=2 AND APPLIED=’YES’);