Skip to content

Posts from the ‘Uncategorized’ Category

Oracle DBA Interview Question

Question: User called and said he or she dropped all tables in schema and want to restore it. How will you restore all tables?

Please post your answer in comments.

Oracle Script to find unindexed Foreign key that cause blocking

This script suggest index on foreign key that cause blocking

run this code and enable dbms output to see result. it will show existing index on the table and recommended index


SET SERVEROUTPUT ON
DECLARE
    PROCEDURE print_all (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;

    PROCEDURE print_ddl (s VARCHAR2)
    IS
    BEGIN
        NULL;
        DBMS_OUTPUT.put_line (s);
    END;
BEGIN
    DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform,
                                       'SEGMENT_ATTRIBUTES',
                                       FALSE);

    FOR a
        IN (  SELECT COUNT (*)                                       samples,
                     event,
                     p1,
                     p2,
                     o.owner                                         c_owner,
                     o.object_name                                   c_object_name,
                     p.object_owner                                  p_owner,
                     p.object_name                                   p_object_name,
                     id,
                     operation,
                     MIN (p1 - 1414332420 + 4)                       lock_mode,
                     MIN (sample_time)                               min_time,
                     MAX (sample_time)                               max_time,
                     CEIL (10 * COUNT (DISTINCT sample_id) / 60)     minutes
                FROM dba_hist_active_sess_history
                     LEFT OUTER JOIN dba_hist_sql_plan p USING (dbid, sql_id)
                     LEFT OUTER JOIN dba_objects o ON object_id = p2
                     LEFT OUTER JOIN dba_objects po
                         ON po.object_id = current_obj#
               WHERE     event LIKE 'enq: TM%'
                     AND p1 >= 1414332420
                     AND sample_time > SYSDATE - 15
                     AND p.id = 1
                     AND operation IN ('DELETE', 'UPDATE', 'MERGE')
            GROUP BY event,
                     p1,
                     p2,
                     o.owner,
                     o.object_name,
                     p.object_owner,
                     p.object_name,
                     po.owner,
                     po.object_name,
                     id,
                     operation
            ORDER BY COUNT (*) DESC)
    LOOP
        print_ddl (
               '--  '
            || a.operation
            || ' on '
            || a.p_owner
            || '.'
            || a.p_object_name
            || ' has locked '
            || a.c_owner
            || '.'
            || a.c_object_name
            || ' in mode '
            || a.lock_mode
            || ' for '
            || a.minutes
            || ' minutes between '
            || TO_CHAR (a.min_time, 'dd-mon hh24:mi')
            || ' and '
            || TO_CHAR (a.max_time, 'dd-mon hh24:mi'));

        FOR s
            IN (SELECT DISTINCT
                       REGEXP_REPLACE (
                           CAST (SUBSTR (sql_text, 1, 2000) AS VARCHAR2 (60)),
                           '[^a-zA-Z ]',
                           ' ')    sql_text
                  FROM dba_hist_active_sess_history
                       JOIN dba_hist_sqltext t USING (dbid, sql_id)
                 WHERE     event LIKE 'enq: TM%'
                       AND p2 = a.p2
                       AND sample_time > SYSDATE - 15)
        LOOP
            print_all ('--      ' || 'blocked statement: ' || s.sql_text);
        END LOOP;

        FOR c
            IN (WITH
                    c
                    AS
                        (SELECT p.owner          p_owner,
                                p.table_name     p_table_name,
                                c.owner          c_owner,
                                c.table_name     c_table_name,
                                c.delete_rule,
                                c.constraint_name
                           FROM dba_constraints  p
                                JOIN dba_constraints c
                                    ON (    c.r_owner = p.owner
                                        AND c.r_constraint_name =
                                            p.constraint_name)
                          WHERE     p.constraint_type IN ('P', 'U')
                                AND c.constraint_type = 'R')
                    SELECT c_owner                     owner,
                           constraint_name,
                           c_table_name,
                              CONNECT_BY_ROOT (p_owner || '.' || p_table_name)
                           || SYS_CONNECT_BY_PATH (
                                     DECODE (delete_rule,
                                             'CASCADE', '(cascade delete)',
                                             'SET NULL', '(cascade set null)',
                                             ' ')
                                  || ' '
                                  || c_owner
                                  || '"."'
                                  || c_table_name,
                                  ' referenced by')    foreign_keys
                      FROM c
                     WHERE     LEVEL <= 10
                           AND c_owner = a.c_owner
                           AND c_table_name = a.c_object_name
                CONNECT BY NOCYCLE     p_owner = PRIOR c_owner
                                   AND p_table_name = PRIOR c_table_name
                                   AND (   LEVEL = 1
                                        OR PRIOR delete_rule IN
                                               ('CASCADE', 'SET NULL'))
                START WITH     p_owner = a.p_owner
                           AND p_table_name = a.p_object_name)
        LOOP
            print_all (
                   '--      '
                || 'FK chain: '
                || c.foreign_keys
                || ' ('
                || c.owner
                || '.'
                || c.constraint_name
                || ')'
                || ' unindexed');

            FOR l
                IN (SELECT *
                      FROM dba_cons_columns
                     WHERE     owner = c.owner
                           AND constraint_name = c.constraint_name)
            LOOP
                print_all ('--         FK column ' || l.column_name);
            END LOOP;

            print_ddl (
                   '--      Suggested index: '
                || REGEXP_REPLACE (
                       TRANSLATE (
                           DBMS_METADATA.get_ddl ('REF_CONSTRAINT',
                                                  c.constraint_name,
                                                  c.owner),
                           CHR (10) || CHR (13),
                           '  '),
                       'ALTER TABLE ("[^"]+"[.]"[^"]+") ADD CONSTRAINT ("[^"]+") FOREIGN KEY ([(].*[)]).* REFERENCES ".*',
                       'CREATE INDEX ON \1 \3;'));

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE     (index_owner, index_name) IN
                                   (SELECT owner, index_name
                                      FROM dba_indexes
                                     WHERE     owner = c.owner
                                           AND table_name = c.c_table_name)
                           AND column_name IN
                                   (SELECT column_name
                                      FROM dba_cons_columns
                                     WHERE     owner = c.owner
                                           AND constraint_name =
                                               c.constraint_name))
            LOOP
                print_ddl ('--      Existing candidate indexes ' || x.ddl);
            END LOOP;

            FOR x
                IN (SELECT RTRIM (
                               TRANSLATE (
                                   DBMS_METADATA.get_ddl ('INDEX',
                                                          index_name,
                                                          index_owner),
                                   CHR (10) || CHR (13),
                                   '  '))    ddl
                      FROM dba_ind_columns
                     WHERE (index_owner, index_name) IN
                               (SELECT owner, index_name
                                  FROM dba_indexes
                                 WHERE     owner = c.owner
                                       AND table_name = c.c_table_name))
            LOOP
                print_all ('--       Other existing Indexes: ' || x.ddl);
            END LOOP;
        END LOOP;
    END LOOP;
END;
/


Sample Ouput

— MERGE on hr.addr has locked hr.address in mode 5 for 5 minutes between 09-aug 15:56 and 13-aug 14:50
— blocked statement: MERGE INTO addr C USING SELECT C CNTC ID FROM ac
— FK chain: hr.addr referenced by hr”.”address (hr.add4) unindexed
— FK column CNTC_ID_PAR
— Suggested index: CREATE INDEX ON “hr”.”address” (“CNTC_ID_PAR”);
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE UNIQUE INDEX “hr”.”addr_hist” ON “hr”.”address” (“add1”, “add2”, “add3”)
— Other existing Indexes: CREATE INDEX “hr”.”ITD_address_NDX1″ ON “hr”.”address” (“CNTC_ID”)

PLS-00306: wrong number or types of arguments in call to ‘GETDCBACKUPHISTORY’ RMAN-06004 ORA-06550

SYMPTOMS

Rman backup fails when connected through catalog. But when rman connects directly the jobs run OK.

DBGSQL: RCVCAT> declare first boolean := FALSE;
bhistoryRec dbms_rcvman.bhistoryRec_t;
atAnyScn boolean := FALSE; +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
begin if (:first > 0) THEN first := TRUE; end if;
if (:atAnyScn > 0) THEN atAnyScn := TRUE; end if;
dbms_rcvman.getDcBackupHistory( backedUpDev => :devtype:devtype_i, first => first, bhistoryRec => bhistoryRec, doingCmd => :backup, keepTag => :tag, toDest1 => :todest1, toDest2 => :tod est2, toDest3 => :todest3, toDest4 => :todest4, atAnyScn => atAnyScn );
:fileno := bhistoryRec.dfNumber; :crescn := bhistoryRec.create_scn; :rlgscn := bhistoryRec.reset_s cn;
:rlgtime := bhistoryRec.reset_time; :ckpscn := bhistoryRec.ckp_scn; :nbackups := bhistoryRec.nbackups;
:compTime := bhistoryRec.compTime; end;
DBGSQL: sqlcode = 6550
DBGSQL: B :fileno = NULL
DBGSQL: B :crescn = NULL
DBGSQL: B :rlgscn = NULL
DBGSQL: B :rlgtime = NULL
DBGSQL: B :ckpscn = NULL
DBGSQL: B :nbackups = NULL
DBGSQL: B :compTime = NULL
DBGSQL: B :first = 1
DBGSQL: B :devtype = SBT_TAPE
DBGSQL: B :backup = B
DBGSQL: B :tag = NULL
DBGSQL: B :todest1 = NULL
DBGSQL: B :todest2 = NULL
DBGSQL: B :todest3 = NULL
DBGSQL: B :todest4 = NULL
DBGSQL: B :atAnyScn = 0
released channel: T1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 09/09/2019 10:27:58
RMAN-06004: ORACLE error from recovery catalog database: ORA-06550: line 1, column 211:
PLS-00306: wrong number or types of arguments in call to ‘GETDCBACKUPHISTORY’
ORA-06550: line 1, column 211:
PL/SQL: Statement ignored

CAUSE 1

A new variable, atAnyScn, is passed to dbms_rcvman.GETDCBACKUPHISTORY in new RMAN catalog versions.
If this variable is not part of the dbms_rcvman description then this error is returned.
 
DESCRIBE on dbms_rcvman on recovery catalog schema is not showing this variable atAnyScn

 

SOLUTION 1

Run DESCRIBE on dbms_rcvman on recovery catalog schema and see if it’s showing this variable : atAnyScn

a.  Connected to the catalog schema with sqlplus, execute:

SQL>  desc dbms_rcvman;

b.  Check for the existence of atAnyScn under PROCEDURE GETDCBACKUPHISTORY

c.  If it does not exist, this catalog schema needs to be upgraded.

CAUSE 2

forgot upgrade catalog after datapatch -verbose

 

SOLUTION 2

rman catalog username/password@rman

RMAN> UPGRADE CATALOG;

RMAN> UPGRADE CATALOG;

ORA-16688: command cannot be issued on a disabled member

Cause: Disable and Enable standby database when connecting to standby database

dgmgrl sys@stby

DGMGRL> disable database stby
Disabled.

DGMGRL> enable database stby
Error: ORA-16688: command cannot be issued on a disabled member

Failed.

DESCRIPTION

trying to enable database from disable standby database and that failed

Solution:

connect with primary database instead of standby and issue command again

dgmgrl sys@prmy

DGMGRL> enable database stby
Enabled.

ORA-12514: TNS:listener does not currently know of service requested in connect

it can be one of these

  • check database status for single instance and CDB and PDB status for multitenant
  • srvctl status database -d dbname or show pdbs from sqlplus
  • check services status with – srvctl status service -d dbname
  • check OUD or TNS entry for correct service name
  • check remote_listener parameter – show parameter remote_listener
  • check PDB name

Single quotes in strings – oracle SQL query

Apostrophe/single quote in a concatenated string

If you were to concatenate an apostrophe/single quote in a string, you need to enter 4 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Today' || '''' || 's Date: ' || sysdate FROM dual;

Today’s Date: 09-DEC-20

select 'PDB Name is : ' || name || ':' from v$pdbs;

PDB Name is : ABC:

SELECT 'Hello' || '''' || 's World' FROM dual;

Hello’s World

Apostrophe/single quote at start of string

When the apostrophe/single quote is at the start of the string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT '''Hello World' FROM dual;

would return

'Hello World

Apostrophe/single quote in the middle of a string

When the apostrophe/single quote is in the middle of the string, you need to enter 2 single quotes for Oracle to display a quote symbol. For example:

SELECT 'She''s Here' FROM dual;

would return

She's Here

Apostrophe/single quote at the end of a string

When the apostrophe/single quote is at the end of a string, you need to enter 3 single quotes for Oracle to display a quote symbol. For example:

SELECT 'Worlds''' FROM dual;

would return

Worlds'

CRS-4995 CRS-2510 CRS-2514

Problem

Error duing cluster upgrade dry run and also when you try to remove resource that is not exists

CLSRSC-729: Checking whether CRS entities are ready for upgrade, cluster upgrade will not be attempted now. This operation may take a few minutes.
PRCR-1129 : Failed to upgrade resource type ora.service.type
PRCR-1071 : Failed to register or update resource ora.abc.abcsvc.svc
CRS-2510: Resource ‘ora.abc.db’ used in dependency ‘hard’ does not exist or is not registered.
CRS-2514: Dependency attribute specification ‘hard’ is invalid in resource ‘ora.abc.abcsvc.svc’
CLSRSC-180: An error occurred while executing the command ‘/opt/oracle/crs/grid19c/bin/srvctl upgrade model -s 12.2.0.1.0 -d 19.0.0.0.0 -p first’
CLSRSC-694: failed to validate CRS entities for upgrade, aborting the upgrade
CLSRSC-362: The pre-upgrade checks failed, aborting the upgrade
Died at /opt/oracle/crs/grid19c/crs/install/crsupgrade.pm line 3779.

Solution:

Remove Resource using CRSCTL depends if exists or not use one of this command

crsctl delete resource ora.abc.abcsvc.svc

(if it throws error CRS-4995 then use below command)

crsctl delete resource ora.abc.abcsvc.svc -unsupported

CRS-4995: The command ‘Delete resource’ is invalid in crsctl. Use srvctl for this command

$ crsctl delete resource ora.abc.abcsvc.svc

CRS-4995: The command ‘Delete resource’ is invalid in crsctl. Use srvctl for this command.

Add “-unsupported” option to the command. It should apply to “crsctl modify resource” as well.

crsctl delete resource ora.abc.abcsvc.svc -unsupported

ORA-12008 ORA-06512 ORA-01722

Error:

Refreshing or creating view gives ORA-01722: invalid number

Executing the query “call emp.rfrsh_mv()” failed with the following error: “ORA-12008: error in materialized view or zonemap refresh path ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2952 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2370 ORA-01722: invalid number ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 85 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 245 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2352 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 2908 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3191 ORA-06512: at “SYS.DBMS_SNAPSHOT_KKXRCA”, line 3221 ORA-06512: at “SYS.DBMS_SNAPSHOT”, line 15 ORA-06512: at “emp.rfrsh_mv”, line 35 ORA-06512: at “emp.rfrsh_mv”, line 35″. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.

Solution:

Try to run select query from materialized view outside and see if error is data related. in my case select query itself was returning ORA-01722: invalid number.

The error was that there was a conditional (case) statement that called the TO_NUMBER function against a column with a potential non numeric value ​. when it hit this non-numeric value, it threw the Oracle error. It had not before encountered this value so it had always worked. it was fixed by separating that specific conditional ( it was the second part of a conditional using the OR predicate) into 2 conditional statements so that the TO_NUMBER function would not be called against it if it had that particular non numeric value

Upgrade to 18c is Failing With ORA-29521: referenced name oracle/xdb/XMLType could not be found

  • Errors during upgrade to 18c:
    • ORA-29521: referenced name oracle/xdb/XMLType could not be found
    • You may also see errors similar to ORA-29532: Java call terminated by uncaught Java exception: oracle.aurora.server.tools.loadjava.ToolsError:Error during loadjava: Failures occurred during processing.
  • XDK is not installed prior to the upgrade

CHANGES

CAUSE

  •  This is caused by unpublished <Bug 27916323 – XML COMPONENT NEEDS TO UPDATE UPGRADE/DOWNGRADE SCRIPTS FOR VERSION 18c>

SOLUTION

  • If you have XDK installed prior to the upgrade to 18c, this error will not take place.
    • Install XDK 10.2 or Later.
    • It should be noted that a full JVM install will install XDK which can be accomplished using  Safe repair/reinstall of the JVM Component in 11.2 and up>

OR

  • Apply <Patch 27916323 – XML COMPONENT NEEDS TO UPDATE UPGRADE/DOWNGRADE SCRIPTS FOR VERSION 18c>. If the patch is not available for your platform please log a support with the XML DB(XDB) team with your list of your one patches (opatch lsinventory -detail >inventory.txt).

RMAN commands

 

LIST BACKUP OF database COMPLETED BETWEEN ’01-APR-2020′ and ’02-APR-2020′;
LIST BACKUP SUMMARY COMPLETED BETWEEN ’01-APR-2020′ and ’02-APR-2020′;

%d bloggers like this: