Specific SQL statement is not available in memory (cursor cache) or may be AWR
kIsH@Xhydra<>DECLARE
v_tune_task_id varchar2(50);
BEGIN
v_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
sql_id => '5qmm8vw5dvy8b',
time_limit => 1000,
2 3 4 5 6 7 task_name => '5qmm8vw5dvy8b_tt',
description => 'Tuning advisory for 5qmm8vw5dvy8b');
DBMS_OUTPUT.PUT_LINE('v_tune_task_id: '|| v_tune_task_id);
END;
/
8 9 10 11 DECLARE
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 257
ORA-06512: at "SYS.DBMS_SQLTUNE", line 778
ORA-06512: at line 4
Check if the SQL existing in cache
kIsH@Xhydra<>select * from v$sql where sql_id='5qmm8vw5dvy8b';
no rows selected
Solution:
Specify the AWR snapshots, in case the sql is stored in disk
If a snapshot of PDB is required for the testing purpose, then it become very easy process.
COW technology is used for snapshot clone feature from storage layer. Oracle leverage this feature to create the sparse files from original datafiles of PDB. These sparse files
Sparse files should be supported by OS. Mostly as per oracle, linux systems should support this feature or dNFS or ACFS or any other filesystems.
Snapshot clone
kIsH@STAX<>show parameter clone
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE
clonedb_dir string
kIsH@STAX<>alter system set clonedb=true scope=spfile;
System altered.
kIsH@STAX<>alter system set clonedb_dir='/apps01/oradata' scope=spfile;
System altered.
kIsH@STAX<>startup force
ORACLE instance started.
Total System Global Area 1962932432 bytes
Fixed Size 9136336 bytes
Variable Size 1358954496 bytes
Database Buffers 469762048 bytes
Redo Buffers 7639040 bytes
In-Memory Area 117440512 bytes
Database mounted.
Database opened.
kIsH@STAX<>show parameter clone
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean TRUE
clonedb_dir string
Create a source PDB in the CDB
kIsH@STAX<>create pluggable database PROX1 admin user pdbadmin identified by password;
Pluggable database created.
kIsH@STAX<>alter pluggable database PROX1 open;
Pluggable database altered.
kIsH@STAX<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROX1 READ WRITE NO
Tried to create pluggable database in read write but since the snapshot PDB can only be created in READ ONLY mode, hence restart the PDB. Then create snapshot clone of the original PDB.
Note the cloned files uses a bitmap file which cannot be dropped after snapshot PDB drop. These need to deleted manually.
kIsH@STAX<>create pluggable database PROX2 from PROX1 snapshot copy;
create pluggable database PROX2 from PROX1 snapshot copy
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode
kIsH@STAX<>alter pluggable database PROX1 close;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX1 open read only;
Pluggable database altered.
kIsH@STAX<>create pluggable database PROX2 from PROX1 snapshot copy;
Pluggable database created.
[oracle@staxhost ~]$ ls -lrt /apps01/oradata/*bitmap*
-rw-r-----. 1 oracle oinstall 2105344 Oct 20 16:48 /apps01/oradata/STAXDB_3951326993_bitmap.dbf
-rw-r-----. 1 oracle oinstall 2105344 Oct 20 17:02 /apps01/oradata/STAXDB_0259964111_bitmap.dbf
-rw-r-----. 1 oracle oinstall 2105344 Oct 20 17:03 /apps01/oradata/STAXDB_0900924109_bitmap.dbf
Check the PDB’s state and open it.
kIsH@STAX<>show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
4 PROX1 READ ONLY NO
6 PROX2 MOUNTED
kIsH@STAX<>alter pluggable database PROX2 open;
Pluggable database altered.
If the original PDB is tried to be opened in read write mode, then IO error is thrown because of snapshot PDB.
kIsH@STAX<>alter pluggable database PROX1 close;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX1 open;
alter pluggable database PROX1 open
*
ERROR at line 1:
ORA-01114: IO error writing block to file 21 (block # 1)
ORA-01110: data file 21:
'/u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile
/o1_mf_undotbs1_kmg6h19y_.dbf'
ORA-27091: unable to queue I/O
ORA-27041: unable to open file
Linux-x86_64 Error: 13: Permission denied
Additional information: 3
kIsH@STAX<>alter pluggable database PROX1 open read only;
Pluggable database altered.
If the original PDB is dropped, then the snapshot PDB still remain
If the snapshot PDB is closed and opened, the source files cannot be accessed and ORA-01157-ORA-01110 error is triggered.
kIsH@STAX<>alter pluggable database PROX2 close;
Pluggable database altered.
kIsH@STAX<>alter pluggable database PROX2 open;
alter pluggable database PROX2 open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 45 - see DBWR trace file
ORA-01110: data file 45:
'/u01/app/oracle/oradata/STAXDB/STAXDB/EB767DAECCF93496E053C738A8C03E21/datafile
/o1_mf_undotbs1_ko2dhp7b_.dbf'
kIsH@STAX<>drop pluggable database PROX2 including datafiles;
Pluggable database dropped.
Alert log message
alter pluggable database PROX2 open
2022-10-20T16:48:53.366344+05:30
PROX2(6):Pluggable database PROX2 opening in read write
PROX2(6):dNFS CloneDB bitmap file is invalidated
PROX2(6):CloneDB: open bitmap file /apps01/oradata/STAXDB_3951326993_bitmap.dbf
PROX2(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
PROX2(6):Autotune of undo retention is turned on.
PROX2(6):Endian type of dictionary set to little
PROX2(6):Undo initialization recovery: Parallel FPTR complete: start:9550295 end:9550298 diff:3 ms (0.0 seconds)
PROX2(6):Undo initialization recovery: err:0 start: 9550295 end: 9550299 diff: 4 ms (0.0 seconds)
PROX2(6):[13462] Successfully onlined Undo Tablespace 2.
PROX2(6):Undo initialization online undo segments: err:0 start: 9550299 end: 9550354 diff: 55 ms (0.1 seconds)
PROX2(6):Undo initialization finished serial:0 start:9550295 end:9550357 diff:62 ms (0.1 seconds)
PROX2(6):Deleting old file#19 from file$
PROX2(6):Deleting old file#20 from file$
PROX2(6):Deleting old file#21 from file$
PROX2(6):Adding new file#31 to file$(old file#19). fopr-3, newblks-48640, oldblks-19200
PROX2(6):Adding new file#32 to file$(old file#20). fopr-3, newblks-51200, oldblks-15360
PROX2(6):Adding new file#33 to file$(old file#21). fopr-3, newblks-19200, oldblks-12800
PROX2(6):Successfully created internal service PROX2 at open
Verify all the datafiles under original and snapshot PDB’s
kIsH@STAX<>set lines 200 pages 1000
col PDB_NAME for a20
col DATAFILE_NAME for a60
col OPEN_MODE for a20
select pdb.NAME as PDB_NAME,pdb.OPEN_MODE,
df.NAME as DATAFILE_NAME,df.bytes/1048576 MB
from v$pdbs pdb
inner join v$datafile df on ( pdb.GUID = REGEXP_SUBSTR('EB76DC7A4E5E3A46E053C738A8C0F02C','(.*?)( My Delimiter |$)', 1, 1, NULL, 1 ))
where df.CON_ID in (3,5)
order by PDB_NAME desc;kIsH@STAX<>kIsH@STAX<>kIsH@STAX<>kIsH@STAX<> 2 3 4 5 6
PDB_NAME OPEN_MODE DATAFILE_NAME MB
-------------------- -------------------- ------------------------------------------------------------ ----------
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C7 380
38A8C0F02C/datafile/o1_mf_system_ko2f6pc7_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C7 440
38A8C0F02C/datafile/o1_mf_sysaux_ko2f6pcb_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C7 230
38A8C09422/datafile/o1_mf_undotbs1_ko2hrkn6_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C7 380
38A8C09422/datafile/o1_mf_system_ko2hrkn5_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C7 460
38A8C09422/datafile/o1_mf_sysaux_ko2hrkn6_.dbf
PROX1 READ ONLY /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C7 230
38A8C0F02C/datafile/o1_mf_undotbs1_ko2f6pcb_.dbf
Look at the size of files for snapshot and original PDB
[oracle@staxhost ~]$ du -sh /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C738A8C09422/datafile/o1_mf_system_ko2hrkn5_.dbf
2.1M<===== Snapshot PDB /u01/app/oracle/oradata/STAXDB/STAXDB/EB77766474A143BFE053C738A8C09422/datafile/o1_mf_system_ko2hrkn5_.dbf
[oracle@staxhost ~]$ du -sh /u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C738A8C0F02C/datafile/o1_mf_system_ko2f6pc7_.dbf381M<=====Original PDB
/u01/app/oracle/oradata/STAXDB/STAXDB/EB76DC7A4E5E3A46E053C738A8C0F02C/datafile/o1_mf_system_ko2f6pc7_.dbf
Creating a snapshot clone fails with the below error.
kIsH@STAX<>create pluggable database PROX2 from PROX1 snapshot copy;
create pluggable database PROX2 from PROX1 snapshot copy
*
ERROR at line 1:
ORA-65169: error encountered while attempting to copy file
/u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile/
o1_mf_undotbs1_kmg6h19y_.dbf
ORA-17525: Database clone using storage snapshot not supported on file
/u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile/
o1_mf_undotbs1_kmg6h19y_.dbf
Alertlog message:
create pluggable database PROX2 from PROX1 snapshot copy
2022-10-20T16:26:02.845479+05:30
PROX1(4): AUDSYS.AUD$UNIFIED (SQL_TEXT) - CLOB populated
2022-10-20T16:26:02.867390+05:30
Errors in file /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_11209.trc:
ORA-17525: Database clone using storage snapshot not supported on file /u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile/o1_mf_undotbs1_kmg6h19y_.dbf
2022-10-20T16:26:02.867543+05:30
Errors in file /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_11209.trc:
ORA-17525: Database clone using storage snapshot not supported on file /u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile/o1_mf_system_kmg6h19o_.dbf
2022-10-20T16:26:02.867642+05:30
Errors in file /u01/app/oracle/diag/rdbms/staxdb/staxdb/trace/staxdb_ora_11209.trc:
ORA-17525: Database clone using storage snapshot not supported on file /u01/app/oracle/oradata/STAXDB/STAXDB/E9E87BBFFBE82E19E053C738A8C06078/datafile/o1_mf_sysaux_kmg6h19x_.dbf
Solution:
Enable the below parameter to support snapshot clone feature
kIsH@STAX<>show parameter clone
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean FALSE
clonedb_dir string
kIsH@STAX<>alter system set clonedb=true;
alter system set clonedb=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
kIsH@STAX<>alter system set clonedb=true scope=spfile;
System altered.
kIsH@STAX<>alter system set clonedb_dir='/apps01/oradata' scope=spfile;
System altered.
kIsH@STAX<>startup force
ORACLE instance started.
Total System Global Area 1962932432 bytes
Fixed Size 9136336 bytes
Variable Size 1358954496 bytes
Database Buffers 469762048 bytes
Redo Buffers 7639040 bytes
In-Memory Area 117440512 bytes
Database mounted.
Database opened.
kIsH@STAX<>show parameter clone
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
clonedb boolean TRUE
clonedb_dir string
Since 12cR1, moving datafile become easy in one command. Before that, DBA need to issue a set of command by bringing them offline, move them using OS cp utility and bring back online which caused lot of human error’s of removing the datafiles or corrupting them.
There was a situation, where datafiles were residing in mount point /u01 which suddenly filled due to bulk load into the table. Hence, only way to release space from the mount point is to move the datafile which consume more space.
kIsH@STAX<>col NAME for a20
kIsH@STAX<>set lines 200 pages 1000
kIsH@STAX<>select name,bytes/1073741824 GB from v$datafile order by GB desc;
NAME GB
-------------------- ----------
/u01/app/oracle/orad 4.96582031
ata/STAXDB/undotbs01
.dbf
/u01/app/oracle/orad 1.18164063
ata/STAXDB/system01.
dbf
/u01/app/oracle/orad .9375
ata/STAXDB/sysaux01.
dbf
/u01/app/oracle/orad .4296875
ata/STAXDB/pdbseed/s
ysaux01.dbf
Datafiles can be transferred using
file_id
file_name
With 2 options
reuse(default)
keep
Under certain conditions
if OMF is configured, then keep clause is not allowed in the command and destination is not specified due to db_create_file_dest parameter.
if OMF is not configured, then destination is required
kIsH@STAX<>show parameter db_create_
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u01/app/oracle/oradata/STAXDB
/
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
Move the datafile using alter command. Below is the syntax for reuse of the original file.
Warning: If the datafile size is huge (30G for example), then move operation will be very slow depending on the IO capacity and resource. It is always safe to move the files during non busy hours of the database.
kIsH@STAX<>select file#,name from v$datafile where name like '%undo%';
FILE# NAME
------- --------------------
4 /u01/app/oracle/oradata/
STAXDB/undotbs01.dbf
8 /u01/app/oracle/orad
ata/STAXDB/pdbseed/u
ndotbs01.dbf
21 /u01/app/oracle/orad
ata/STAXDB/STAXDB/E9
E87BBFFBE82E19E053C7
38A8C06078/datafile/
FILE# NAME
------- --------------------
o1_mf_undotbs1_kmg6h
19y_.dbf
kIsH@STAX<>alter database move datafile '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' to '/apps01/oradata/undotbs01.dbf' reuse;
Database altered.
Validate the moved files
kIsH@STAX<>!ls -lrth /u01/app/oracle/oradata/STAXDB/undotbs01.dbf
ls: cannot access '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf': No such file or directory
kIsH@STAX<>!ls -lrth /apps01/oradata/undotbs01.dbf
-rw-r-----. 1 oracle oinstall 5.0G Oct 19 16:05 /apps01/oradata/undotbs01.dbf
alertlog entries records the move operation
alter database move datafile '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' to '/apps01/oradata/undotbs01.dbf' reuse
2022-10-19T15:11:20.779618+05:30
Moving datafile /u01/app/oracle/oradata/STAXDB/undotbs01.dbf (4) to /apps01/oradata/undotbs01.dbf
2022-10-19T15:11:35.638905+05:30
Move operation committed for file /apps01/oradata/undotbs01.dbf
2022-10-19T15:11:37.994600+05:30
Completed: alter database move datafile '/u01/app/oracle/oradata/STAXDB/undotbs01.dbf' to '/apps01/oradata/undotbs01.dbf' reuse
2022-10-19T15:17:19.146890+05:30
Control autobackup written to DISK device
handle '/apps01/RECO/STAXDB/autobackup/2022_10_19/o1_mf_s_1118503039_knzkx739_.bkp'
kcffo_mv_prepare(kernel cache file management file operations) prepare the datafile for online move by creating a clone of the original file using OS cp utility.
kcffo_mv_domove actually copy the blocks from original file to clone and remove the original file.
Control file auto backup is taken.
*** 2022-10-19T15:11:20.779490+05:30 (CDB$ROOT(1))
*** SESSION ID:(69.34374) 2022-10-19T15:11:20.779515+05:30
*** CLIENT ID:() 2022-10-19T15:11:20.779521+05:30
*** SERVICE NAME:(SYS$USERS) 2022-10-19T15:11:20.779525+05:30
*** MODULE NAME:(sqlplus@staxhost (TNS V1-V3)) 2022-10-19T15:11:20.779529+05:30
*** ACTION NAME:() 2022-10-19T15:11:20.779533+05:30
*** CLIENT DRIVER:(SQL*PLUS) 2022-10-19T15:11:20.779537+05:30
*** CONTAINER ID:(1) 2022-10-19T15:11:20.779541+05:30
Moving datafile /u01/app/oracle/oradata/STAXDB/undotbs01.dbf (4) to /apps01/oradata/undotbs01.dbf
kcffo_mv_prepare: the secondary file /apps01/oradata/undotbs01.dbf is created with size 650880
*** 2022-10-19T15:11:35.635386+05:30 (CDB$ROOT(1))
kcffo_mv_domove: Blocks copied for file /apps01/oradata/undotbs01.dbf size 650880
Move operation committed for file /apps01/oradata/undotbs01.dbf
*** 2022-10-19T15:11:36.700132+05:30 (CDB$ROOT(1))
Move operation completed for file /apps01/oradata/undotbs01.dbf
Check the space in /u01 mount. Space should be released after some time of the move but not immediately.
Again moved the datafile by duplicating the original file using ‘KEEP’ option. This option will be helpful for backup reasons. In case, there is any corruption or mistake during movement of the datafile, original file is in place. Using keep option, the same file can be cloned multiple times with different names or different directories as per needs.
Tried to move temporary files but unable to do because, tempfiles can be dropped and recreated if no sorting data is stored in tempfiles.
kIsH@STAX<>alter database move tempfile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep;
alter database move tempfile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep
*
ERROR at line 1:
ORA-00905: missing keyword
kIsH@STAX<>alter database move datafile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep;
alter database move datafile '/u01/app/oracle/oradata/STAXDB/temp01.dbf' to '/apps01/oradata/temp01.dbf' keep
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file
"/u01/app/oracle/oradata/STAXDB/temp01.dbf" in the current container
Can we have multiple materialized view logs for a base object?
The answer is no. One materialized view log per base object can be used.
kIsH@STAX<>create materialized view log on xtbl with sequence,rowid(XID,XDATE) including new values;
create materialized view log on xtbl with sequence,rowid(XID,XDATE) including new values
*
ERROR at line 1:
ORA-12000: a materialized view log already exists on table 'XTBL'
Check the existing mview logs and drop if required
kIsH@STAX<>select count(*) from dba_mview_logs where master='XTBL';
COUNT(*)
----------
1
Primary key columns are not allowed in rowid clause, if primary key is specified in the syntax explicitly or the column really does not exist due to drop.
kIsH@STAX<>!oerr ora 12026
12026, 0000, "invalid filter column detected"
// *Cause: One or more of the specified filter columns did not exist or was a
// primary key column or a primary key based object identifier.
// *Action: Ensure that all specified filter columns exist in the master table
// and ensure that primary key columns or primary key based object
// identifiers are not included in the list of filter columns.
Create mview fails with the below error
kIsH@STAX<>create materialized view log on xtbl
with primary key,
rowid(XID,XDATE)
including new values; 2 3 4
create materialized view log on xtbl
*
ERROR at line 1:
ORA-12026: invalid filter column detected
Solution:
Check the definition of the columns and remove the primary key column in the rowid clause
kIsH@STAX<>desc xtbl
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(100)
XID NOT NULL NUMBER(20)
CITY VARCHAR2(100)
COUNTRY VARCHAR2(100)
DEPARTMENT VARCHAR2(100)
ZIPCODE NUMBER(20)
XDATE NOT NULL DATE
SALARY NUMBER(38)
kIsH@STAX<>create materialized view log on xtbl
2 with primary key,
3 rowid(XDATE)
4 including new values;
Materialized view log created.
There are multiple features in the data which cannot be displayed with multiple colours due to limited colour provided
#color=['brown','red','green'] # three colors can be used
#mp.scatter(x="stat",y=["cpu","wait"], color=color, label="DB CPU STATS") # there are more than 3 features
raise ValueError("RGBA sequence should have length 3 or 4")
ValueError: RGBA sequence should have length 3 or 4
Solution:
Either remove the parameter color to set the default colour or provide multiple colours as per the total number of features
mp.scatter(x="stat",y=["cpu","wait"], label="DB CPU STATS")
[oracle@hydrupgrd ~]$ python3 plot_RT.py
Traceback (most recent call last):
File "plot_RT.py", line 14, in <module>
encoding="UTF-8")
cx_Oracle.DatabaseError: DPI-1047: Cannot locate a 64-bit Oracle Client library: "libclntsh.so: cannot open shared object file: No such file or directory". See https://cx-oracle.readthedocs.io/en/latest/user_guide/installation.html for help
