Finding and Monitoring SQLs in Execution

 Dictionary view V$SQL_MONITOR is used to find out SQLs currently in execution and a little history of SQLs executed in the recent past. There are several columns in this view that can be useful in different scenarios. Most common use it to find out the elapsed time, CPU time etc. For parallel executions, each parallel slave would appear in this view with column PX_QCSID showing SID of the query coordinator session (session that executed the SQL). For example, if an SQL is executing with 8 parallel servers, there would be 9 rows in this view (WHERE sql_id = <SQL_ID> AND STATUS = ‘EXECUTION’).

ORA-65093: multitenant container database not set up properly

RMAN-00571: ======================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =========== RMAN-00571: ======================================================== RMAN-03002: failure of Duplicate Db command at 06/20/2024 08:01:54 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-06136: Oracle error from auxiliary database: ORA-65093: multitenant container database not set up properly

ORA-00058: DB_BLOCK_SIZE must be 16384 to mount this database

This error may be returned during different scenarios; however, the root cause may be the same. I was trying to use duplicate command to build a database from backup of another database that had default block size of 16k. Since 8k is default block size of an Oracle database, we MUST specify db_block_size in the init file of the auxiliary instance to the same block size of the source database we are using to build this new database. After I modified the value of this parameter to 16384 in the init file of the auxiliary instance, error did not appear on second attempt of DUPLICATE database command and I was able to build the new database.

ORA-31655: no data or metadata objects selected for job

 Whenever datapump export is initiated, it should (mainly) either by database level, schema level, or table level. If you omit to tell expdp about the mode of datapump export, ORA-31655 is returned. Therefore, you should always make sure that that you provide level/mode of export datapump whenever you invoke expdp. For example, use parameter full=y for database level export, or schemas parameter for schema level or tables parameter for table level export.

ORA-16086: Redo data cannot be written to the standby redo log

2024-11-22T07:09:14.930561+00:00 Errors in file /data/oracle/MYDB/admin/diag/rdbms/MYDB/MYDB7/trace/MYDB7_lgwr_178713.trc: ORA-16086: Redo data cannot be written to the standby redo log LGWR (PID:178713): Error 16086 for LNO:2 to 'MYDB_sb' LGWR (PID:178713): Failed to archive LNO:2 T-1.S-1765560, error=16086 2024-11-22T07:09:14.945680+00:00 Thread 1 advanced to log sequence 1765560 (LGWR switch), current SCN: 13393654452914 Current log# 2 seq# 1765560 mem# 0: +DATA/MYDB/onlinelog/group_2.471.1046140279 Current log# 2 seq# 1765560 mem# 1: +REC/MYDB/onlinelog/group_2.19611.1046140279 2024-11-22T07:09:16.758563+00:00 ARC1 (PID:184077): Archived Log entry 7135590 added for T-1.S-1765559 ID 0x794f6c68 LAD:1 2024-11-22T07:09:26.011951+00:00

RMAN-06172: no AUTOBACKUP found or the specified handle is not a valid copy or piece

     

RMAN> restore controlfile from '/home/oracle/current.257.1073287779_20240609_0030';     Starting restore at 12-JUN-24  using target database control file instead of recovery catalog  allocated channel: ORA_DISK_1  channel ORA_DISK_1: SID=2049 device type=DISK     RMAN-00571: ============================================== =============  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  RMAN-00571: ============================================== =============  RMAN-03002: failure of restore command at 06/12/2024 08:38:03  RMAN-06172: no AUTOBACKUP found or the specified handle is not a valid copy or piece

ORA-01103: database name ... in control file is not ...

SQL> srtup pfile=init.ora  ORACLE instance started.     Total System Global Area 3226043912 bytes  Fixed Size                  9172488 bytes  Variable Size            2080374784 bytes  Database Buffers         1006632960 bytes  Redo Buffers              129863680 bytes  ORA-01103: database name 'NEWDB' in control file is not 'SALMAN'

RMAN-06617: UNTIL TIME is ahead of last NEXT TIME in archived logs

RMAN-00571: ===========================================================  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============  RMAN-00571: ===========================================================  RMAN-03002: failure of Duplicate Db command at 06/26/2024 05:32:00  RMAN-05501: aborting duplication of target database  RMAN-06617: UNTIL TIME (20-JUN-24) is ahead of last NEXT TIME in archived logs (20-JUN-24)

ORA-29339: tablespace block size 4096 does not match configured block sizes

SQL> create tablespace testtbs blocksize 4k datafile '+DATA' size 2m;  create tablespace testtbs blocksize 4k datafile '+DATA' size 2m  *  ERROR at line 1:  ORA-29339: tablespace block size 4096 does not match configured block sizes

ORA-03214: File size specified is smaller than minimum required

SQL> alter database datafile '+DATA/MYDB/19AF24AD633634AEE0632502F50A727A/DATAFILE/testtbs.5971.1170569741' resize 6m;  alter database datafile '+DATA/MYDB/19AF24AD633634AEE0632502F50A727A/DATAFILE/testtbs.5971.1170569741' resize 6m  *  ERROR at line 1:  ORA-03214: File size specified is smaller than minimum required