resmgr:pq queued

When trying to connect to database (11g,12c)  I  see wait events on "resmgr:pq queued" indefinitely.
What can be done to resolve the resmgr:pq queued wait message?

By changing the following parameter, this issue was resolved :

alter system set "_parallel_statement_queuing"=FALSE scope=both;

Clusterware out of sync

I had strange inconsistency in Clusterware 12c.

crsctl stat res -t -w "NAME = ora.dbname.db"

return information that db is configured on:
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details      
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.dbname.db
      1        ONLINE  ONLINE       node04              Open,Readonly,STABLE
      2        ONLINE  UNKNOWN      node01              Abnormal Termination
                                                             ,STABLE
--------------------------------------------------------------------------------

but when I checked configuration for this database:

srvctl config database -d dbname
...
Database instances: dbname1,dbname1
Configured nodes: node03,node04

 In documentation is a info that crsctl has a option "relocate resource" but dosn't work :/

To relocate resource I had to use:
srvctl modify instance -d dbname -i dbname1 -n node03


In one of document on metalink I found that only modify option force clusterware to synchronize information about resources

KTBCONVERTREDO

ORA-600 [KTBCONVERTREDO_1]

After ora-4030 errors and sequence of ora-600 ktbConvertRedo_1

and  led to an instance crash. Restarting database didn't help

(database was up ~ 5minutes).

We tried also switchover  to physical standby but unfortunately

we received the same error. 

Smon generated trace and if you carefully look in trace there is

 a object_id. When we tried 

run queries on this object then we received the same error. 

Bingo!!! Object had been corrupted.

luckily that was table with logs from application and we can

 easy drop table and recreate empty structure.   
 

In metalink I found information that  event 10153 should be set

 to see object_id but without that in alertlog this information was.

Shrink segments

Script to shrink segments:
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' shrink space compact;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||OWNER
  ||'.'
  ||TABLE_NAME
  ||' shrink space;' sql1
FROM DBA_TABLES
WHERE TABLESPACE_NAME IN ('USERS')
AND TABLE_NAME NOT    IN
  (SELECT TABLE_NAME
  FROM DBA_TAB_PARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  UNION ALL
  SELECT TABLE_NAME
  FROM DBA_TAB_SUBPARTITIONS
  WHERE TABLESPACE_NAME IN ('USERS')
  )
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' modify partition '
  ||PARTITION_NAME
  ||' SHRINK SPACE;' sql1
FROM DBA_TAB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' enable row movement;' sql1
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS')
UNION
SELECT 'alter table '
  ||TABLE_OWNER
  ||'.'
  ||TABLE_NAME
  ||' modify subpartition '
  ||SUBPARTITION_NAME
  ||' SHRINK SPACE;' sql1
FROM dba_tab_subpartitions
WHERE tablespace_name IN ('USERS')
UNION
SELECT 'alter table '
  ||owner
  ||'.'
  ||table_name
  ||' modify lob('
  ||column_name
  ||') (shrink space);' sql1
FROM dba_lobs
WHERE TABLESPACE_NAME IN ('USERS')
AND PARTITIONED        ='NO'
UNION
SELECT 'ALTER TABLE '
  ||TABLE_OWNER
  ||'."'
  || TABLE_NAME
  || '" MOVE SUBPARTITION '
  || SUBPARTITION_NAME
  ||'  TABLESPACE '
  ||TABLESPACE_NAME
  ||' LOB ('
  ||COLUMN_NAME
  ||') STORE AS (TABLESPACE '
  ||TABLESPACE_NAME
  ||');'
FROM DBA_LOB_SUBPARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
union
SELECT 'ALTER TABLE '
  ||TABLE_OWNER
  ||'."'
  || TABLE_NAME
  || '" MOVE PARTITION '
  || PARTITION_NAME
  ||'  TABLESPACE '
  ||TABLESPACE_NAME
  ||' LOB ('
  ||COLUMN_NAME
  ||') STORE AS (TABLESPACE '
  ||TABLESPACE_NAME
  ||');'
FROM DBA_LOB_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
UNION
SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild partition '
  || PARTITION_NAME
  ||' online;' sql1
FROM DBA_IND_PARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
AND SUBPARTITION_COUNT =0
UNION
SELECT 'ALTER INDEX '
  ||INDEX_OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild subpartition '
  || SUBPARTITION_NAME
  ||' online;' sql1
FROM DBA_IND_SUBPARTITIONS
WHERE TABLESPACE_NAME IN ('USERS')
UNION
SELECT 'ALTER INDEX '
  ||OWNER
  ||'.'
  || INDEX_NAME
  ||' rebuild online;' sql1
FROM DBA_INDEXES
WHERE TABLESPACE_NAME IN ('USERS')
AND PARTITIONED        ='NO' ;

RMAN - unregister not existed database from catalog

Normally when I tried unregister non-existed database in RMAN catalog I received error:

execute dbms_rcvcat.unregisterdatabase(818673443,879389610);
BEGIN dbms_rcvcat.unregisterdatabase(818673443,879389610); END;

*
ERROR at line 1:
ORA-02292: integrity constraint (RMAN.TSATT_F2) violated - child record found
ORA-06512: at "RMAN.DBMS_RCV

Probably some informations about backups are still in catalog for this database but without database I can't unregister this datbase.

Solution:

select name,DB_KEY,DBINC_KEY,dbid from rman.rc_database where name=<database_name>;
delete rman.tsatt where DBINC_KEY=<DBINC_KEY for db>;

commit;

execute dbms_rcvcat.unregisterdatabase(<DB_KEY for db>,<DBID for db>);

PL/SQL procedure successfully completed.


 

Changing database link definition in pl/sql

To change database link definition in Oracle database we have to connect as a owner of this database link. In most cases it's possible by using output from dba_db_links and connect from sqlplus as a owner.

But if we use undocumented function dbms_sys_sql.parse_as_user, then it's possible to change definition from pl/sql:

-- if we use specific password schema
define pass_prefix = 'pre'
define pass_postfix = 'post'

DECLARE
  sqltext  VARCHAR2(1000);
  l_result NUMBER;
  l_cursor INTEGER;
  l_cursors dbms_sql.Number_Table;
BEGIN
  -- modify db_links
  FOR cur IN
  (SELECT u.user_id,
    d.OWNER,
    d.DB_LINK,
    d.USERNAME,
    d.HOST
  FROM dba_db_links d,
    dba_users u
  WHERE d.owner=u.username
  )
  LOOP
    --parse the cursor only if we haven't already
    sqltext:= 'alter database link "'||cur.DB_LINK||'" connect to '||cur.USERNAME||' identified by &&pass_prefix'||cur.USERNAME||'&&pass_postfix';
    IF ( NOT l_cursors.exists(cur.user_id) ) THEN
      l_cursors(cur.user_id):=dbms_sys_sql.open_cursor;
      --parsing anonymous PL/SQL block as a job owner
      dbms_sys_sql.parse_as_user( c => l_cursors(cur.user_id), STATEMENT => sqltext, language_flag => dbms_sql.native, userid => cur.user_id );
    END IF;
    --bind the job number
    --remove the job by executing
    l_result:=sys.dbms_sys_sql.execute(l_cursors(cur.user_id));
  END LOOP;
END;
/

RMAN - restore process failed

On test server I had to restore database (3TB).
Due to network issue RESTORE (not recovery) process  failed. In normal situation I have to  start restore process from beginning once again but I already have almost 2.5 TB restored. How to use this data?

Database is in mount mode and in view V$DATAFILE_COPY we have information about file_id and datafile name (this information is needfull if OMF is used).

select 'set newname for datafile '||FILE#||' to '''||name||''';' from V$DATAFILE_COPY where name is not null order by file#;


This query generate set newname for all datafiles already restored.

run {

set until time "to_date('2015-01-05 00:11:00','yyyy-mm-dd hh24:mi:ss')";
<generated set newname> 

restore database;
SWITCH DATAFILE ALL;
SWITCH TEMPFILE ALL;
recover database DELETE ARCHIVELOG MAXSIZE 90G;
}

and if we run this script, rman continue restore process.

If in v$datafile_copy you don't have information for some datafiles, then RMAN not restored then already and for these datafiles you have to created fake name.