RMAN - transfer archivelog from primary database to standby

rman target /@rman_prm catalog [catalog]@rmancat auxiliary /@rman_stb

backup as copy archivelog sequence between  5643  and 5683   thread 2  auxiliary format '+RECOC1';

Checking most heavy queries every snapshot and system statistics

Most important system statiscts/performance overview:

SELECT begin_time,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN 'SQL Service Response
Time (secs)'
    WHEN 'Response Time Per Txn'
    THEN 'Response Time Per Txn
(secs)'
    ELSE METRIC_NAME
  END METRIC_NAME,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MINVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MINVAL / 100),2)
    ELSE MINVAL
  END MININUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((MAXVAL / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((MAXVAL / 100),2)
    ELSE MAXVAL
  END MAXIMUM,
  CASE METRIC_NAME
    WHEN 'SQL Service Response Time'
    THEN ROUND((AVERAGE / 100),2)
    WHEN 'Response Time Per Txn'
    THEN ROUND((AVERAGE / 100),2)
    ELSE AVERAGE
  END AVERAGE
FROM SYS.DBA_HIST_SYSMETRIC_SUMMARY
WHERE METRIC_NAME IN ('CPU Usage Per Sec', 'CPU Usage Per Txn', 'Database CPU Time Ratio', 'Database Wait Time Ratio', 'Executions Per Sec', 'Executions Per Txn', 'Response Time Per Txn', 'SQL Service Response Time', 'User Transaction Per Sec','User Commits Per Sec')
AND BEGIN_TIME BETWEEN sysdate -1 and sysdate
ORDER BY 1;

Output:

Most heavy queries:

SELECT t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  t.PARSING_SCHEMA_NAME username,
  t.executions_delta exec_count,
  begin_interval_time,
  ROUND(SUM(t.elapsed_time_delta/1000000)/SUM(t.executions_delta),4) time_exec
FROM dba_hist_sqlstat t,
  dba_hist_snapshot s,
  DBA_HIST_SQLTEXT q
WHERE t.snap_id           = s.snap_id
AND t.dbid                = s.dbid
AND q.sql_id              =t.sql_id
AND t.instance_number     = s.instance_number
AND t.executions_delta   IS NOT NULL
AND t.elapsed_time_delta IS NOT NULL
AND t.executions_delta    > 0
AND s.begin_interval_time BETWEEN TRUNC(sysdate)-1 AND TRUNC(sysdate)
AND t.PARSING_SCHEMA_NAME NOT                  IN ('SYS','SYSTEM','DBSNMP') -- yesterday's stats
GROUP BY t.sql_id,
  dbms_lob.substr(q.SQL_TEXT,100,1),
  PARSING_SCHEMA_NAME,
  t.executions_delta,
  s.begin_interval_time
ORDER BY 5,6 DESC;

Output:

 

RMAN-06004: ORACLE error from recovery catalog database: ORA-20999: internal error: found non-null and null site name

In metalink is only one solution. Recreate controlfile and this issue occurs for database with long db_unique_name ( 30 chars in length) and when catalog is in use.

RMAN-06004: ORACLE error from recovery catalog database: ORA-20999: internal error: found non-null and null site name

I found another solution. I set debug option for rman and in trace file was information:

DBGSQL:           RCVCAT> select count(*) into cnt from db
DBGSQL:              sqlcode = 905
DBGSQL:           error: ORA-00905: missing keyword (krmkosqlerr)

So. It's looks like that rman wrongly generate code. After that I decided to use:

upgrade catalog 

and now everything works ...

Probably is a small difference in catalog (for example after PSU) and all packages must be recreated.

What is strange that command "upgrade catalog" had been executed on newly created catalog... 

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' ;