* DBMS_ASSERT - Sanitise User Input to Help Prevent SQL Injection
create table tt as (select/*+ materialize */ decode(owner,'SYS','SYS','SYSTEM','SYSTEM','OTHERS') owner, object_type from dba_objects); SYS INDEX SYS INDEX SYS TABLE SYS INDEX SYS TABLE SYS INDEX SYS VIEW SYS VIEW OTHERS PROCEDURE SYS INDEX PARTITION SYS INDEX PARTITION SYS INDEX PARTITION SYS INDEX PARTITION SYS INDEX PARTITION SYS INDEX PARTITION ... OTHERS SYNONYM SYS VIEW OTHERS SYNONYM SYS VIEW OTHERS SYNONYM SYS VIEW SYS INDEX PARTITION SYS INDEX PARTITION SYS LOB PARTITION OTHERS JOB OTHERS JOB OTHERS JOB OTHERS JOB OTHERS JOB OTHERS JOB OTHERS JOB OTHERS JOB OTHERS SEQUENCE OTHERS TABLE OTHERS INDEX OTHERS LOB SYSTEM TABLE SYSTEM INDEX SYSTEM LOB SYSTEM INDEX SYSTEM INDEX SYSTEM INDEX SYSTEM TABLE PARTITION SYSTEM TABLE SYSTEM TABLE SYSTEM TABLE SYSTEM TABLE SYSTEM INDEX
select * from tt pivot ( count(*) for owner in ('SYS','SYSTEM','OTHERS') );
OBJECT_TYPE 'SYS' 'SYSTEM' 'OTHERS'
----------------------- ---------- ---------- ----------
SEQUENCE 196 7 56
PACKAGE BODY 649 0 115
PROCEDURE 174 0 36
CONTEXT 7 0 0
TABLE SUBPARTITION 32 0 24
RULE SET 17 0 4
RULE 1 0 0
FUNCTION 121 6 14
INDEX PARTITION 174 79 26
UNIFIED AUDIT POLICY 8 0 0
WINDOW 9 0 0
JOB 20 0 1984
JOB CLASS 15 0 0
EVALUATION CONTEXT 11 0 2
PACKAGE 671 0 117
VIEW 6910 10 181
LOB PARTITION 43 0 10
XML SCHEMA 0 0 19
TRIGGER 3 0 64
DIRECTORY 26 0 0
PROGRAM 11 0 0
QUEUE 25 0 4
INDEXTYPE 0 0 2
TABLE 1579 134 476
EDITION 1 0 0
SYNONYM 8 8 6500
CONTAINER 1 0 0
TYPE 1817 13 205
RESOURCE PLAN 11 0 0
SCHEDULE 4 0 0
SCHEDULER GROUP 4 0 0
CLUSTER 10 0 0
UNDEFINED 15 0 0
DATABASE LINK 1 0 16
LIBRARY 182 0 25
OPERATOR 7 0 16
TABLE PARTITION 357 52 33
INDEX 1744 167 881
LOB 279 17 345
TYPE BODY 178 0 9
CONSUMER GROUP 18 0 0
DESTINATION 2 0 0
42 rows selected.
When upgrading to 18c, passwords that only use version 10g will have to be changed. This is done by expiring the passwords for those users.
Find the relevant usernames
SELECT USERNAME FROM DBA_USERS WHERE ( PASSWORD_VERSIONS = '10G ' OR PASSWORD_VERSIONS = '10G HTTP ') AND USERNAME <> 'ANONYMOUS';
Set database access to be less restrictive than the default. Put this in sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
Expire the passwords
ALTER USER username PASSWORD EXPIRE;
Using straight data pump to export a sample does not take referential constraints into account.
Using this method will fix that oversight.
http://www.dba-oracle.com/t_data_pump_export_rows_subset.htm
col window_name for a25
col resource_plan for a30
col repeat_interval for a80
col duration for a15
set lines 1000
select window_name,resource_plan, repeat_interval, duration from dba_scheduler_windows
/
begin
dbms_scheduler.disable(name => 'SATURDAY_WINDOW');
dbms_scheduler.disable(name => 'SUNDAY_WINDOW');
dbms_scheduler.set_attribute ( name => 'SATURDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY=SAT;BYHOUR=6;BYMINUTE=0;BYSECOND=0'
);
dbms_scheduler.set_attribute ( name => 'SATURDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(20, 'hour')
);
dbms_scheduler.set_attribute ( name => 'SUNDAY_WINDOW',
attribute => 'REPEAT_INTERVAL',
value => 'FREQ=WEEKLY;BYDAY=SUN;BYHOUR=6;BYMINUTE=0;BYSECOND=0'
);
dbms_scheduler.set_attribute ( name => 'SUNDAY_WINDOW',
attribute => 'DURATION',
value => numtodsinterval(20, 'hour')
);
dbms_scheduler.enable(name => 'SATURDAY_WINDOW');
dbms_scheduler.enable(name => 'SUNDAY_WINDOW');
end;
/
select window_name,resource_plan, repeat_interval, duration from dba_scheduler_windows
/
set lines 1000 col name for a30 col value for a100 col con_id nopri select * from v$rman_configuration;
Either use rman command line and specify:
configure controlfile autobackup on;
or use SQL*Plus
var a number
exec :a := dbms_backup_restore.setconfig('CONTROLFILE AUTOBACKUP','ON');
PL/SQL procedure successfully completed.
print :a
A
----------
1
Verify by either 'show all' in rman or as above selecting from v$rman_configuration.
CONF# NAME VALUE
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
1 CONTROLFILE AUTOBACKUP ON
2 RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS
Other options are dbms_backup_restore.deleteconfig(<config number>) to restore one item to its default value
SQL> exec dbms_backup_restore.deleteconfig(1);
PL/SQL procedure successfully completed.
SQL> print a;
A
----------
SQL> select * from v$rman_configuration;
CONF# NAME VALUE
---------- ------------------------------ ----------------------------------------------------------------------------------------------------
2 RETENTION POLICY TO RECOVERY WINDOW OF 28 DAYS
or dbms_backup_restore.resetconfig to remove all customisations.
SQL> exec dbms_backup_restore.resetconfig; PL/SQL procedure successfully completed. SQL> select * from v$rman_configuration; no rows selected SQL>
Show how far back the RMAN catalog goes for each instance
select db_name instance , min(start_time) earliest_date , input_type backup_type from rc_rman_backup_job_details group by db_name,input_type order by 2;
produces something like
DB_NAME MIN(START_TIME) INPUT_TYPE -------- --------------------------- ------------- EBSM 11-MAY-16 16:34:55 ARCHIVELOG CRELSASP 25-JUN-16 18:29:14 ARCHIVELOG CRELSASP 29-JUN-16 18:59:48 DB FULL DOCT 01-JUL-16 19:31:58 DB FULL CLNP2 08-JUL-16 12:25:20 ARCHIVELOG BILP2 08-JUL-16 12:25:20 ARCHIVELOG CRELSASD 08-JUL-16 16:30:22 ARCHIVELOG CLNP2 11-JUL-16 19:04:54 DB FULL CRELSASD 13-JUL-16 18:59:03 DB FULL BILP2 20-JUL-16 18:59:43 DB FULL BILF 16-AUG-16 22:33:12 ARCHIVELOG FORMN 18-AUG-16 14:31:49 ARCHIVELOG CLNF 19-AUG-16 14:32:44 ARCHIVELOG LOANF 19-AUG-16 14:32:48 ARCHIVELOG FORMN 19-AUG-16 15:33:30 CONTROLFILE LOANF 19-AUG-16 19:09:08 DB FULL BILF 22-AUG-16 19:00:47 DB FULL FORMN 23-AUG-16 19:00:34 DB FULL CLNF 25-AUG-16 19:00:29 DB FULL ADSNA 27-AUG-16 12:31:47 ARCHIVELOG ...
If these dates go back further than the retention period in the RMAN configuration then these entries need clearing out. This script will do it if the controlfile_record_keep_time is set correctly on all databases.
RMANUSR="rman"
RMANPWD="rman"
RMANCAT=${1:-rman12d}
# =======================================
# thats it, nothing to change after here!
# =======================================
export PATH="/usr/local/bin/oraenv:${PATH}"
ORAENV_ASK=NO
SIDLIST=$(ps -ef | grep [o]ra_pmon | awk '{print $NF}' | sed -e 's/ora_pmon_//' | egrep -v 'grep|///|sed|awk|ASM|^$')
for i in ${SIDLIST}
do
export ORACLE_SID="${i}"
unset ORACLE_HOME
. oraenv
if [[ "${ORACLE_HOME}" == "" ]]; then
echo "WARNING: Unable to set environment for ${i}, skipping."
continue
fi
# find out if the database is in archive log mode
LOG_TEXT=$(echo 'archive log list' | "${ORACLE_HOME}/bin/sqlplus" -s / as sysdba)
LOG_MODE=$(echo $LOG_TEXT | perl -ne 'print "$2" if /Database log mode (.+) Automatic archival (.+) Archive destination (.+?) /')
if [[ "${LOG_MODE}" == "Disabled" ]]; then
echo "INFO: Instance ${i} is not archiving so will not be in catalog ${RMANCAT}, skipping."
continue
fi
# test we can see the database from the rman instance
# if this errors out, the instance probably is not in this catalog
RMANTEST=$(echo "exit;" | "${ORACLE_HOME}/bin/rman" target / catalog ${RMANUSR}/${RMANPWD}@${RMANCAT})
echo "${RMANTEST}" | egrep "(not started|RMAN-04004|RMAN-06004)" >/dev/null 2>&1
if [[ $? -eq 0 ]]; then
echo "INFO: Instance ${i} is not accessible from or not in catalog ${RMANCAT}, skipping."
continue
fi
echo
echo "Processing ${i} in catalog ${RMANCAT}"
"${ORACLE_HOME}/bin/rman" <<EORMAN
connect target /
connect catalog ${RMANUSR}/${RMANPWD}@${RMANCAT}
unregister database noprompt;
register database;
EORMAN
done
The UNREGISTER verb does not exist in 9i so have to use PL/SQL method.
Recovery Manager: Release 9.2.0.8.0 - 64bit Production
Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
connected to target database: PVCS (DBID=2561628519)
connected to recovery catalog database
RMAN> unregister database;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01005: syntax error: found "identifier": expecting one of: "allocate, alter, backup, beginline, blockrecover, catalog, change, connect, copy, create, crosscheck, configure, duplicate, debug, delete, drop, exit, endinline, host, {, library, list, mount, open, print, quit, recover, register, release, replace, report, renormalize, reset, restore, resync, rman, run, rpctest, set, setlimit, sql, spool, startup, shutdown, send, show, test, upgrade, validate"
RMAN-01008: the bad identifier was: unregister
RMAN-01007: at line 1 column 1 file: standard input
From SQL*Plus, get the db_key and dbid from the catalog
select db_key, dbid, name from rc_database where name = 'PVCS';
DB_KEY DBID NAME ---------- ---------- -------- 5102549 2561628519 PVCS
Use these values as parameters to the packaged procedure
SQL> execute dbms_rcvcat.unregisterdatabase (5102549, 2561628519); PL/SQL procedure successfully completed.
Templates are normally in $ORACLE_HOME/install/response/
oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v18.0.0 oracle.install.option=INSTALL_DB_SWONLY UNIX_GROUP_NAME=oinstall INVENTORY_LOCATION=/cln/exp/ora_bin1/app/oraInventory ORACLE_BASE=/cln/exp/ora_bin1/app/oracle oracle.install.db.InstallEdition=EE oracle.install.db.OSDBA_GROUP=dba oracle.install.db.OSOPER_GROUP= oracle.install.db.OSBACKUPDBA_GROUP=dba oracle.install.db.OSDGDBA_GROUP=dba oracle.install.db.OSKMDBA_GROUP=dba oracle.install.db.OSRACDBA_GROUP=dba
cd $ORACLE_HOME ./runInstaller -silent -responseFile install/response/db_18c_sw_only.rsp
<DatabaseTemplate name="Stuart 18c no options" description="No options, archive and temp in 2, users in 3, separated controlfiles" version="18.0.0.0.0">
<CommonAttributes>
<option name="OMS" value="false" includeInPDBs="false"/>
<option name="JSERVER" value="false" includeInPDBs="false"/>
<option name="SPATIAL" value="false" includeInPDBs="false"/>
<option name="IMEDIA" value="false" includeInPDBs="false"/>
<option name="ORACLE_TEXT" value="false" includeInPDBs="false">
<tablespace id="SYSAUX"/>
</option>
<option name="SAMPLE_SCHEMA" value="false" includeInPDBs="false"/>
<option name="CWMLITE" value="false" includeInPDBs="false">
<tablespace id="SYSAUX"/>
</option>
<option name="APEX" value="false" includeInPDBs="false"/>
<option name="DV" value="false" includeInPDBs="false"/>
</CommonAttributes>
<Variables>
<variable name="ORABASE" value="/cln/exp/ora_bin1/app/oracle"/>
<variable name="ORADATA1" value="/cln/exp/ora_data1"/>
<variable name="ORADATA2" value="/cln/exp/ora_data2"/>
<variable name="ORADATA3" value="/cln/exp/ora_data3"/>
<variable name="ORAARCH" value="{ORADATA2}/archivelog"/>
</Variables>
<CustomScripts Execute="false"/>
<InitParamAttributes>
<InitParams>
<initParam name="db_name" value="{SID}"/>
<initParam name="dispatchers" value="(PROTOCOL=TCP) (SERVICE={SID}XDB)"/>
<initParam name="audit_file_dest" value="{ORABASE}/{SID}/adump"/>
<initParam name="compatible" value="18.0.0"/>
<initParam name="remote_login_passwordfile" value="EXCLUSIVE"/>
<initParam name="log_archive_dest_1" value="'LOCATION={ORAARCH}/{SID}'"/>
<initParam name="sga_target" value="2" unit="GB"/>
<initParam name="processes" value="900"/>
<initParam name="local_listener" value="LISTENER_{SID}"/>
<initParam name="undo_tablespace" value="UNDOTBS1"/>
<initParam name="control_files" value="("{ORADATA1}/{SID}/control01.ctl", "{ORADATA2}/{SID}/control02.ctl", "{ORADATA3}/{SID}/control03.ctl")"/>
<initParam name="diagnostic_dest" value="{ORABASE}"/>
<initParam name="audit_trail" value="db"/>
<initParam name="log_archive_format" value="{SID}_%t_%s_%r.dbf"/>
<initParam name="nls_territory" value="AMERICA"/>
<initParam name="db_block_size" value="8192"/>
<initParam name="open_cursors" value="300"/>
<initParam name="nls_language" value="AMERICAN"/>
<initParam name="pga_aggregate_target" value="1" unit="GB"/>
</InitParams>
<MiscParams>
<databaseType>MULTIPURPOSE</databaseType>
<maxUserConn>0</maxUserConn>
<percentageMemTOSGA>0</percentageMemTOSGA>
<customSGA>false</customSGA>
<characterSet>AL32UTF8</characterSet>
<nationalCharacterSet>AL16UTF16</nationalCharacterSet>
<archiveLogMode>true</archiveLogMode>
<initParamFileName>{ORACLE_BASE}/admin/{SID}/pfile/init.ora</initParamFileName>
</MiscParams>
<SPfile useSPFile="true">{ORACLE_HOME}/dbs/spfile{SID}.ora</SPfile>
</InitParamAttributes>
<StorageAttributes>
<ControlfileAttributes id="Controlfile">
<maxDatafiles>100</maxDatafiles>
<maxLogfiles>16</maxLogfiles>
<maxLogMembers>3</maxLogMembers>
<maxLogHistory>1</maxLogHistory>
<maxInstances>8</maxInstances>
<image name="control01.ctl" filepath="{ORADATA1}/{SID}/"/>
<image name="control02.ctl" filepath="{ORADATA2}/{SID}/"/>
<image name="control03.ctl" filepath="{ORADATA3}/{SID}/"/>
</ControlfileAttributes>
<DatafileAttributes id="{ORADATA1}/{SID}/sysaux01.dbf" con_id="1">
<tablespace>SYSAUX</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">550</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORADATA1}/{SID}/system01.dbf" con_id="1">
<tablespace>SYSTEM</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">700</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">10240</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORADATA1}/{SID}/undotbs01.dbf" con_id="1">
<tablespace>UNDOTBS1</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">200</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">5120</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORADATA2}/{SID}/users01.dbf" con_id="1">
<tablespace>USERS</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">5</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">1280</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<DatafileAttributes id="{ORADATA3}/{SID}/temp01.dbf" con_id="1">
<tablespace>TEMP</tablespace>
<temporary>false</temporary>
<online>true</online>
<status>0</status>
<size unit="MB">20</size>
<reuse>true</reuse>
<autoExtend>true</autoExtend>
<increment unit="KB">640</increment>
<maxSize unit="MB">-1</maxSize>
</DatafileAttributes>
<TablespaceAttributes id="SYSAUX" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">64</initSize>
<increment unit="KB">64</increment>
<incrementPercent>50</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>4096</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<bigfile>false</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORADATA1}/{SID}/sysaux01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="SYSTEM" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>3</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">64</initSize>
<increment unit="KB">64</increment>
<incrementPercent>50</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>-1</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<bigfile>false</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORADATA1}/{SID}/system01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="TEMP" con_id="1">
<temporary>true</temporary>
<defaultTemp>true</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="MB">1</uniAllocSize>
<initSize unit="KB">64</initSize>
<increment unit="KB">64</increment>
<incrementPercent>0</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>0</maxExtends>
<minExtendsSize unit="KB">64</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<bigfile>false</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORADATA3}/{SID}/temp01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="UNDOTBS1" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>true</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">512</initSize>
<increment unit="KB">512</increment>
<incrementPercent>50</incrementPercent>
<minExtends>8</minExtends>
<maxExtends>4096</maxExtends>
<minExtendsSize unit="KB">512</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<bigfile>false</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORADATA1}/{SID}/undotbs01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<TablespaceAttributes id="USERS" con_id="1">
<temporary>false</temporary>
<defaultTemp>false</defaultTemp>
<undo>false</undo>
<local>true</local>
<blockSize>-1</blockSize>
<allocation>1</allocation>
<uniAllocSize unit="KB">-1</uniAllocSize>
<initSize unit="KB">128</initSize>
<increment unit="KB">128</increment>
<incrementPercent>0</incrementPercent>
<minExtends>1</minExtends>
<maxExtends>4096</maxExtends>
<minExtendsSize unit="KB">128</minExtendsSize>
<logging>true</logging>
<recoverable>false</recoverable>
<maxFreeSpace>0</maxFreeSpace>
<autoSegmentMgmt>true</autoSegmentMgmt>
<bigfile>false</bigfile>
<datafilesList>
<TablespaceDatafileAttributes id="{ORADATA2}/{SID}/users01.dbf"/>
</datafilesList>
</TablespaceAttributes>
<RedoLogGroupAttributes id="1">
<reuse>false</reuse>
<fileSize unit="KB">512000</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo01.log" filepath="{ORADATA1}/{SID}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="2">
<reuse>false</reuse>
<fileSize unit="KB">512000</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo02.log" filepath="{ORADATA1}/{SID}/"/>
</RedoLogGroupAttributes>
<RedoLogGroupAttributes id="3">
<reuse>false</reuse>
<fileSize unit="KB">512000</fileSize>
<Thread>1</Thread>
<member ordinal="0" memberName="redo03.log" filepath="{ORADATA1}/{SID}/"/>
</RedoLogGroupAttributes>
</StorageAttributes>
</DatabaseTemplate>
$ORACLE_HOME/bin/dbca -silent \
-createDatabase \
-templateName $ORACLE_HOME/assistants/dbca/templates/db_18c_no_options.dbt \
-gdbName oem \
-Sid oem \
-sysPassword qwertyuiop \
-systemPassword asdfghjkl \
-sysmanPassword qazwsxedc
perl -ne 'print unless m/^\s*#/ or m/^\s*$/' $ORACLE_HOME/assistants/dbca/dbca.rsp
Stop the gathering of stats for the TEMP tablespace
Suggested by Oracle in
Use the following workaround to reduce the temp usage by emagent / dbsnmp:
exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); /* deletes the statistics on the fixed object */
exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR'); /* lock that object so that statistics will not be collected in future */
alter system flush shared_pool;
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'test.kill_old_report_sessions_job',
comments => 'Kill old reports if they have been running for longer than 1 hour.',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN admin_tasks_user.kill_old_report_sessions; END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=hourly; byminute=0,15,30,45; bysecond=0;',
enabled => TRUE);
END;
/
Stop collecting stats on the TEMP tablespace
Oracle recommends the following:
exec dbms_stats.delete_table_stats('SYS','X$KCCRSR'); -- deletes the statistics on the fixed object
exec dbms_stats.lock_table_stats('SYS','X$KCCRSR'); -- lock that object so that statistics will not be collected in future
alter system flush shared_pool; -- cannot be skipped
COLUMN value FORMAT A100 SELECT value FROM v$diag_info WHERE name = 'Default Trace File' /
Reproduced from Fixed Objects Statistics and Why They are Important by Maria Colgan
Fixed objects are the “X$” tables and their indexes.
The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN).
Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically used for SQL statement involving X$ tables when optimizer statistics are missing.
The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a sub-optimal execution plan, which could cause severe performance problems in your system.
It is for this reason that we strong recommend you gather fixed objects statistics.
Prior to Oracle Database 12c Release 1 fixed object statistics are not created or maintained by the automatic statistics gathering job.
You can collect statistics on fixed objects using DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.
BEGIN DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; END; /
The DBMS_STATS.GATHER_FIXED_OBJECTS_STATS procedure gathers the same statistics as DBMS_STATS.GATHER_TABLE_STATS except for the number of blocks. Blocks is always set to 0 since the X$ tables are in memory structures only and are not stored on disk.
You must have the ANALYZE ANY DICTIONARY or SYSDBA privilege or the DBA role to update fixed object statistics.
Because of the transient nature of the X$ tables it is important that you gather fixed object statistics when there is a representative workload on the system. This may not always be feasible on large system due to additional resource needed to gather the statistics.
If you can’t do it during peak load you should do it after the system has warmed up and the three key types of fixed object tables have been populated:
* Structural Data For example, views covering datafiles, controlfile contents, etc. * Session-based Data For example, v$session, v$access, etc. * Workload Data For example, v$sql, v$sql_plan etc.
It is recommended that you re-gather fixed object statistics if you do a major database or application upgrade, implement a new module or make changes to the database configuration.
For example if you increase the SGA size then all of the X$ tables that contain information about the buffer cache and shared pool may change significantly, such as X$ tables used in v$buffer_pool or v$shared_pool_advice.
From Oracle Database 12c Release 1 the automatic statistics gathering job will gather statistics for fixed tables that have missing stats. For this to happen, there will need to be some time available inside the batch window after statistics for the other tables in the system have been gathered.
Even with this new functionality, it is still good practice to gather fixed table stats with DBMS_STATS.GATHER_FIXED_OBJECTS_STATS when there's a representative workload running, especially after major changes have been made to the system
SET PAGESIZE 60
SET LINESIZE 300
COLUMN username FORMAT A20
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 99999
SELECT s.username,
s.sid,
s.serial#,
t.used_ublk,
t.used_urec,
rs.segment_name,
r.rssize,
r.status
FROM v$transaction t,
v$session s,
v$rollstat r,
dba_rollback_segs rs
WHERE s.saddr = t.ses_addr
AND t.xidusn = r.usn
AND rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC
/
An annoying message in the alertlog is
Thread 1 cannot allocate new log, sequence 170734 Private strand flush not complete
More info here
with my_data as (select sample_id,
cast(sample_time as date) as sample_time,
max(time_waited) / 1000000 as time_waited_s,
min(event) as event
from v$active_session_history
where event like '%log file switch%'
and session_state='WAITING'
group by sample_id, sample_time),
my_data2 AS (select row_number() over (order by sample_id) r,
sample_id,
sample_time,
decode(sample_id - lag(sample_id) over (order by sample_id),1,1,null) as diff,
time_waited_s,
event
from my_data)
select d.sample_time,
d.event,
(select sum(time_waited_s) from my_data2 d2 where d2.r between d.r and d.r2) as time_waited_s
from (select d.*,
nvl((select min(d2.r)-1 from my_data2 d2 where d2.r > d.r and d2.diff is null),
(select max(d2.r) from my_data2 d2))as r2
from my_data2 d
) d
where diff is null
/
SAMPLE_TIME EVENT TIME_WAITED_S
--------------------------- ---------------------------------------------------------------- -------------
20-NOV-19 17:42:52 log file switch (private strand flush incomplete) .0302
20-NOV-19 21:46:13 log file switch (private strand flush incomplete) .028813
20-NOV-19 22:00:46 log file switch (private strand flush incomplete) .035936
20-NOV-19 22:11:41 log file switch (private strand flush incomplete) .032628
20-NOV-19 23:21:37 log file switch (private strand flush incomplete) .033289
21-NOV-19 02:26:00 log file switch completion .026524
21-NOV-19 06:46:02 log file switch (private strand flush incomplete) .025534
21-NOV-19 09:36:10 log file switch (private strand flush incomplete) .038597
21-NOV-19 10:00:28 log file switch (private strand flush incomplete) .045918
-------------
sum .297439
so only a third of a second lost since yesterday
SQL*Plus and anonymous PL/SQL blocks cannot avoid soft parsing the way stored procedures can. session_cached_cursors can be set to help this situation by caching the DML and reusing it.
See also https://hoopercharles.wordpress.com/2011/07/21/session_cached_cursors-possibly-interesting-details/
and https://asktom.oracle.com/pls/asktom/f%3Fp%3D100:11:0::::P11_QUESTION_ID:801662252143
In case you need to set a parameter that is only modifiable in the spfile, it can still be changed at session level. This changes it for all current sessions.
For example the session_cached_cursors is not modifiable with “scope=both”.
begin for c in (select sid, serial# from v$session) loop dbms_system.set_int_param_in_session(c.sid,c.serial#,'session_cached_cursors', 100); end loop; end; /
This will return the percentage hit ratio for the current setting of pga_aggregate_target
select percs.cache_hit_perc
from (
select vpta.estd_pga_cache_hit_percentage cache_hit_perc
from v$pga_target_advice vpta
, v$parameter vp
where 1=1
and vp.value <= vpta.pga_target_for_estimate
and vp.name = 'pga_aggregate_target'
) percs
where rownum = 1
/
if this value is deemed to be a little off, use this to check what the best value would be
select round(pga_target_for_estimate/1024/1024) target_mb,
estd_pga_cache_hit_percentage cache_hit_perc,
estd_overalloc_count
from v$pga_target_advice;
TARGET_MB CACHE_HIT_PERC ESTD_OVERALLOC_COUNT
---------- -------------- --------------------
384 95 221282
768 95 221261
1536 95 221202
2304 95 220416
3072 99 218777
3686 99 215757
4301 99 197912
4915 99 169319
5530 99 145761
6144 99 140660
9216 100 89848
12288 100 76968
18432 100 0
24576 100 0
so here, setting pga_aggregate_target to 3G would give 99% hit rate.
Shows:
Trick: Using grep on SQL result set to filter and format lines
F_list_OH () {
F_colordef
echo
echo "Listing existing golden images:"
RESULT=`$SQLPLUS -S -L ${REPO_CREDENTIALS} <<EOF | grep ";"
set line 200 pages 1000
set feed off head off
col name format a32
alter session set nls_timestamp_format='YYYY-MM-DD';
select name||';'||created||';'||fullpath from oh_golden_images order by created desc;
EOF
`
echo
printf "%-35s %-10s %-18s\n" "OH_Name" "Created" "Installed locally?"
echo "----------------------------------- ---------- ------------------"
for line in $RESULT ; do
L_GI_Name=`echo $line | awk -F\; '{print $1}'`
L_GI_Date=`echo $line | awk -F\; '{print $2}'`
L_GI_Path=`echo $line | awk -F\; '{print $3}'`
L_Installed=`F_OH_Installed "$L_GI_Name"`
printf "%-35s %-10s %-18s\n" "$L_GI_Name" "$L_GI_Date" "$L_Installed"
done
}