Automatic Tuning of Undo Retention and Optimization

Oracle Database automatically tunes the undo retention period based on how the undo tablespace is configured.

If the undo tablespace is fixed size, the database tunes the retention period for the best possible undo retention for that tablespace size and the current system load. This tuned retention period can be significantly greater than the specified minimum retention period.

If the undo tablespace is configured with the AUTOEXTEND option, the database tunes the undo retention period to be somewhat longer than the longest-running query on the system at that time. Again, this tuned retention period can be greater than the specified minimum retention period.

Determine the current retention period by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT view. This view contains one row for each 10-minute statistics collection interval over the last 4 days. (Beyond 4 days, the data is available in the DBA_HIST_UNDOSTAT view.) TUNED_UNDORETENTION is given in seconds.

select to_char(begin_time, 'DD-MON-RR HH24:MI') begin_time,
 to_char(end_time, 'DD-MON-RR HH24:MI') end_time, tuned_undoretention
 from v$undostat order by end_time;
BEGIN_TIME END_TIME TUNED_UNDORETENTION
 --------------- --------------- -------------------
 04-FEB-05 00:01 04-FEB-05 00:11 12100
 ...
 07-FEB-05 23:21 07-FEB-05 23:31 86700
 07-FEB-05 23:31 07-FEB-05 23:41 86700
 07-FEB-05 23:41 07-FEB-05 23:51 86700
 07-FEB-05 23:51 07-FEB-05 23:52 86700
576 rows selected.

Calculating UNDO_RETENTION for given UNDO Tabespace

The following query will helps to optimize the UNDO_RETENTION parameter:

Otimal Undo Retention

 

 

 

 

Image Source

Because these following queries use the V$UNDOSTAT statistics, run the queries only after the database has been running with UNDO for a significant and representative time!

Actual Undo Size

SELECT SUM(a.bytes) "UNDO_SIZE"
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#;
UNDO_SIZE
----------
 1572864000

Undo Blocks per Second

SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 "UNDO_BLOCK_PER_SEC"
 FROM v$undostat;
UNDO_BLOCK_PER_SEC
------------------
 249.398333333333333333333333333333333333

DB Block Size

SELECT TO_NUMBER(value) "DB_BLOCK_SIZE [KByte]"
 FROM v$parameter
WHERE name = 'db_block_size';
DB_BLOCK_SIZE [Byte]
--------------------
 8192

Optimal Undo Retention

770 [Sec]

Using Inline Views:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 ROUND((d.undo_size / (to_number(f.value) *
 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
 FROM (
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size'
/
ACTUAL UNDO SIZE [MByte]
------------------------
1500
UNDO RETENTION [Sec]
--------------------
900
OPTIMAL UNDO RETENTION [Sec]
----------------------------
770

Calculating required UNDO Size for given Database Activity

If you are not limited by disk space, then it would be better to choose the UNDO_RETENTION time that is best for you (for FLASHBACK, etc.). Allocate the appropriate size to the UNDO tablespace according to the database activity:

Again, all in one query:

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
 g.undo_block_per_sec) / (1024*1024) 
 "NEEDED UNDO SIZE [MByte]"
 FROM (
 SELECT SUM(a.bytes) undo_size
 FROM v$datafile a,
 v$tablespace b,
 dba_tablespaces c
 WHERE c.contents = 'UNDO'
 AND c.status = 'ONLINE'
 AND b.name = c.tablespace_name
 AND a.ts# = b.ts#
 ) d,
 v$parameter e,
 v$parameter f,
 (
 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
 undo_block_per_sec
 FROM v$undostat
 ) g
 WHERE e.name = 'undo_retention'
 AND f.name = 'db_block_size'
/

ACTUAL UNDO SIZE [MByte]
------------------------
1500
UNDO RETENTION [Sec] 
--------------------
900
NEEDED UNDO SIZE [MByte]
------------------------
1753.582031249999999999999999999999999998

The previous query may return a “NEEDED UNDO SIZE” that is less than the “ACTUAL UNDO SIZE”. If this is the case, you may be wasting space. You can choose to resize your UNDO tablespace to a lesser value or increase your UNDO_RETENTION parameter to use the additional space.

References / Source:

Database Schema Documentation

Tools that provide options to generate database schema documentation are found all over web. But you may end up with a paid version of the tool in the end.

Oracle SQL Developer itself has an option to generate a HTML document on  database schema.

  • Open Oracle SQL Developer.
  • Create a connection to the desired database.
  • Right click on the connection and find ‘Generate DB Doc’ option.
  • Select the option and specify the target directory.

Other open source tools for database schema documentation:

Using MySQL with .Net

Required Components:

  • MySQL 5.0 database server
  • MySQL Connector/Net 1.0.6 or higher
  • MySQL Administrator 1.1 or higher
  • .NET Framework 1.0 or higher
  • Visual Studio 2002 or higher

Download and install MySQL Community Server
MySQL Community Edition is a freely downloadable version of the world’s most popular open source database that is supported by an active community of open source developers and enthusiasts.

Download Connector/Net
Connector/Net is a fully-managed ADO.NET driver for MySQL.

  • Download link : Connector/Net
  • Unzip the ZIP file to get the .MSI file and install it by double clicking on it.

Connect to MYSQL from .NET

  • Go to the solution explorer in Visual Studio and select Add References option.
  • From Add References dialog box, select “MySQL.Data” option and click on Ok.
  • Refer the following code to connect to MYSQL.
// Connection string for a typical local MySQL installation
string cnnString = 
 "Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=MySecretPassword";

// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();

// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);

// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);

// Bind the DataSet
// ... Place your databinding code here ...

If you’re already accustomed to data binding in .NET, 
you should immediately recognize the similarities.  
The only difference is you use the MySqlXxx classes 
instead of the OleDbXxx or SqlXxx classes.
Source/ Reference Links:

Oracle Database Security Checklist

Checklist to keep secure an Oracle Database:

1. Install only what is required
The Oracle Database CD pack contains a host of options and products in addition to the database server. Install additional products and options only as necessary.

2. Lock and Expire Default User Accounts
If a manual (i.e. without using Database Configuration Assistant) installation of Oracle Database is performed, then no default database users are locked upon successful installation of the database server. Left open in their default states, these user accounts can be exploited to gain unauthorized access to data or disrupt database operations.

a) Enterprise Manager Accounts
The preceding list of accounts depends on whether you choose to install Enterprise Manager. If so, SYSMAN and DBSNMP are open as well, unless you configure Enterprise Manager for Central Administration. In this case, the SYSMAN account (if present) will be locked as well.

3. Change Default User Passwords
The most trivial method by which Oracle Database can be compromised is a default database server user account which still has a default password associated with it even after installation.

a) Change default passwords of administrative users
Oracle Database 10g enables you to use the same or different passwords for the SYS, SYSTEM, SYSMAN and DBSNMP administrative accounts. Use different passwords for each: in any Oracle environment (production or test), assign strong, secure, and distinct passwords to these administrative accounts.

b) Change default passwords of all users
Each of the accounts install with a default password that is exactly the same as that user account (For example, user MDSYS installs with the password MDSYS).

c) Enforce password management
Oracle recommends that basic password management rules (such as password length, history, complexity, and so forth) as provided by the database be applied to all user passwords and that all users be required to change their passwords periodically.

Oracle also recommends, if possible, using Oracle Advanced Security (an option to the Enterprise Edition of Oracle Database) with network authentication services (such as Kerberos), token cards, smart cards or X.509 certificates.

4. Enable Data Dictionary Protection
Oracle recommends that customers implement data dictionary protection to prevent users having the ANY system privileges from using such privileges on the data dictionary.

To enable dictionary protection, set the following configuration parameter to FALSE, in the init<sid>.ora control file: O7_DICTIONARY_ACCESSIBILITY = FALSE

5. Practicing the principle of least privilege

a) Grant necessary privileges only
The principle of least privilege is that users be given only those privileges that are actually required to efficiently perform their jobs.

To implement this principle, restrict the following as much as possible:
1) The number of SYSTEM and OBJECT privileges granted to database users, and
2) The number of people who are allowed to make SYS-privileged connections to the database.

b) Revoke unnecessary privileges from the public user group
Revoke all unnecessary privileges and roles from the database server user group PUBLIC. PUBLIC acts as a default role granted to every user in an Oracle database.

The more powerful packages that may potentially be misused are:
1. UTL_SMTP 2. UTL_TCP 3. UTL_HTTP 4. UTL_FILE

c) Grant a role to users only if they need all privileges of the role
Ensure that the roles you define contain only the privileges that reflect job responsibility. If your application users do not need all the privileges encompassed by an existing role, then apply a different set of roles that supply just the right privileges.
Altenatively, create and assign a more restricted role.

d)Restrict permissions on run-time facilities
Do not assign all permissions to any database server run-time facility such as the Oracle Java Virtual Machine (OJVM). Grant specific permissions to the explicit document root file paths for such facilities that may execute files and packages outside the database server.

6. Enforce access controls cffectively and authenticate clients stringently

a) Authenticate client properly
By default, Oracle allows operating-system-authenticated logins only over secure connections, which precludes using Oracle Net and a shared server configuration. This default restriction prevents a remote user from impersonating another operating system user over a network connection.

Setting the initialization parameter REMOTE_OS_AUTHENT to TRUE forces the RDBMS to accept the client operating system user name received over a nonsecure connection and use it for account access. Since clients, such as PCs, are not trusted to perform operating system authentication properly, it is very poor security practice to turn on this feature.

7. Restrict Operating System Access
Limit the number of operating system users. Limit the privileges of the operating system accounts (administrative, root-privileged or DBA) on the Oracle Database host (physical machine) to the least privileges needed for the user’s tasks.

Oracle also recommends:
•     Restricting the ability to modify the default file and directory permissions for the Oracle Database home (installation) directory or its contents. Even privileged operating system users and the Oracle owner should not modify these permissions, unless instructed otherwise by Oracle.
•     Restricting symbolic links.

8. Restrict Network Access

a) Use a firewall
Keep the database server behind a firewall. Oracle Database network infrastructure, Oracle Net (formerly known as Net8 and SQL*Net), offers support for a variety of firewalls from various vendors.

b) Never poke a hole through a firewall
If Oracle Database is behind a firewall, then do not, under any circumstances, poke a hole through the firewall.
For example, do not leave open port 1521 for Oracle Listener to make a connection to the Internet or vice versa.

c) Protect the Oracle listener
Because the listener acts as the database gateway to the network, it is important to limit the consequences of malicious interference:
Restrict the privileges of the listener, so that it cannot read or write files in the database or the Oracle server address space.

Secure administration of the database by doing the following:

1. Prevent online administration by requiring the administrator to have write privileges on the LISTENER.ORA file and the listener password:

Add or alter this line in the LISTENER.ORA file ADMIN_RESTRICTIONS_LISTENER=ON
Then RELOAD the configuration.

2. Use SSL when administering the listener, by making the TCPS protocol the first entry in the address list as follows:

LISTENER=
 (DESCRIPTION=
 (ADDRESS_LIST=
 (ADDRESS=
 (PROTOCOL=tcps)
 (HOST = ed-pdsun1.us.oracle.com)
 (PORT = 8281)))

3. Always establish a secure, well-formed password for the Oracle listener to prevent remote configuration of the Oracle listener.
4. Remove the external procedure configuration from the listener.ora file if you do not intend to use such procedures.
5. Monitor listener activity.

d) Monitor who accesses your systems

Instead of authenticating client computers over the Internet, make use of user authentication which avoids client system issues that include falsified IP addresses, hacked operating systems or applications, and falsified or stolen client system identities.

The following steps improve client computer security:
a) Configure the connection to use SSL.
Using SSL (Secure Sockets Layer) communication makes eavesdropping unfruitful and enables the use of certificates for user and server authentication.
b) Set up certificate authentication for clients and servers such that:

i. The organization is identified by unit and certificate issuer and the user is identified by distinguished name and certificate issuer.
ii. Applications test for expired certificates.
iii. Certificate revocation lists are audited.

e) Check network IP addresses

Use the Oracle Net valid node checking security feature to allow or deny access to Oracle server processes from network clients with specified IP addresses. To use this feature, set the following protocol.ora (Oracle Net configuration file) parameters:

tcp.validnode_checking = YES
tcp.excluded_nodes = {list of IP addresses}
tcp.invited_nodes = {list of IP addresses}

The first parameter turns on the feature whereas the latter parameters respectively deny and allow specific client IP addresses from making connections to the Oracle listener (This helps in preventing potential Denial of Service attacks).

f) Encrypt network traffic

If possible, use Oracle Advanced Security to encrypt network traffic between clients, databases, and application servers.

g) Harden the operating system

Harden the host operating system by disabling all unnecessary operating system services. Both UNIX and Windows platforms provide a variety of operating system services, most of which are not necessary for most deployments. Such services include FTP, TFTP, TELNET, and so forth. Be sure to close both the UDP and TCP ports for each service that is being disabled. Disabling one type of port and not the other does not make the operating system more secure.

9. Apply all security patches

Always apply all relevant and current security patches for both the operating system on which Oracle Database resides and Oracle Database itself, and for all installed Oracle Database options and components.

10. Contact Oracle Security products if you come across a vulnerability in Oracle Database

Source: Based on Oracle Documentaion

MSVCP71.DLL and MSVCR71.DLL Missing on Windows 7

Both MSVCP71.DLL and MSVCR71.DLL are Microsoft C Runtime Library files. Some programs like SQL Developer require you to have any of this files for it to work successfully.

  • Download the DLL files from here.
  • Copy the files to System32 on 32 bit OS and SysWOW64 on 64 bit OS.

 

RMAN Configure

By default the RMAN configuration is stored in the control file. The default values for the configuration parameters can be viewed by using RMAN to connect to the database and issuing the SHOW ALL command.
RMAN> SHOW ALL;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 
 'E:ORACLEPRODUCT10.1.0DB_1DATABASESNCFTEST.ORA'; # default
RMAN>

Changing the configuration in RMAN is easy and simple. The output of ‘SHOW ALL’ command helps to reconfigure, like:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

To reset the configuration parameter to its default value, ‘CLEAR’ can be used:

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN configuration parameters are successfully reset to default value

List of commands that clear several RMAN settings back to their default values:

RMAN> CONFIGURE RETENTION POLICY CLEAR;
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;
RMAN> CONFIGURE DEFAULT DEVICE TYPE CLEAR;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP CLEAR;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK CLEAR;
RMAN> CONFIGURE CHANNEL DEVICE TYPE SBT CLEAR;
RMAN> CONFIGURE MAXSETSIZE CLEAR;
RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME CLEAR;
RMAN> # CONFIGURE EXCLUDE FOR TABLESPACE USERS_READ_TBS;

1. Retention Policy

Using RMAN a recovery window (have a rolling window of days) or a set redundancy (rolling number of backups) can be set to define the retention policy.

RMAN> configure retention policy to redundancy 2;

new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
new RMAN configuration parameters are successfully stored

2. Controlfile Autobackup

RMAN can be configured to automatically backup the control file when a data file is added or when a backup is taken with CONTROLFILE AUTOBACKUP. By default CONTROLFILE AUTOBACKUP is set to OFF.

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored
RMAN>
3. Controlfile Autobackup Storage Directory
To set the location in which to write the control file auto backup:
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
  'E:flash_recovery_areaautobackup%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO
  'E:flash_recovery_areaautobackup%F';
new RMAN configuration parameters are successfully stored

4. Backup Set and Storage on Disk

RMAN can store the backup in one or more binary files called a backup set that contain data from one ore more data files, archive logs, control files or an SPFILE. These files are written in an RMAN specific format and be compressed during the creation process. Each individual file in a back set is called a backup piece.

RMAN can also create exact copies of the data files, archive logs, control files and SPFILE. These image copies are not created in an RMAN specific format.

In RMAN the type of backup is configured via the device type to be used to create the backup. By default the default device type is disk and RMAN is configured to write backup sets to disk.

RMAN> SHOW DEFAULT DEVICE TYPE;
RMAN configuration parameters are:
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
RMAN> SHOW DEVICE TYPE;
RMAN configuration parameters are:
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

5. Backup Set Piece Size

In order for RMAN to read or write data it must have a channel configured. In configuring the channel we can set the FORMAT, or location on disk, in which to read and write backups. The channel is also were we configure the size of the backup piece in the case of backup sets.

Like the CONTROLFILE AUTOBACKUP FORMAT, the CHANNEL FORMAT has a substitution variable %U. The %U specifies a system generated unique file name.

RMAN> CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 
 'E:flash_recovery_areaTESTBACKUPSETbackup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1 G;
new RMAN configuration parameters:
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 
 'E:flash_recovery_areaTESTBACKUPSETbackup_db_%d_S_%s_P_%p_T_%t' MAXPIECESIZE 1 G;
new RMAN configuration parameters are successfully stored

Source / Reference Links:

ORA-04063

Error: ORA-04063

OEM Error when assigning an email id to SYS user:

Internal error. ORA-04063: package body "SYSMAN.EMD_NOTIFICATION" has errors
ORA-06508: PL/SQL: could not find program unit being called:
 "SYSMAN.EMD_NOTIFICATION"
ORA-06512: at "SYSMAN.MGMT_PREFERENCES", line 1536 ORA-06512: at line 1

Cause:

Oracle policy recommends taking away the execute privilege on UTL_SMTP from PUBLIC. This takes away the privileges from SYSMAN as well. SYSMAN needs execute on UTL_SMTP and UTL_TCP to send notifications.

Workaround:

1. Log into the repository database as sys:

grant execute on sys.utl_smtp to sysman;
grant execute on sys.utl_tcp to sysman;

2. Compile:

alter package sysman.emd_notification compile;

References:

Security Policy Violations in OEM

Found this four policy violations under ‘Security’ category in OEM:

1. EXECUTE UTL_FILE privileges to PUBLIC

Recommendation: Oracle recommends that you revoke EXECUTE privileges on powerful packages from PUBLIC

Violation Count:1 Details: Package ­UTL_FILE

Solution:

REVOKE EXECUTE ON UTL_FILE FROM PUBLIC;

Anyone that happens to get any userid and password on your database will be able to use UTL_FILE.

This may be legitimate, or it may be malicious – and you have absolutely no control over it if the package is granted to public.

For example, person A legitimately dumps confidential data (or has a comma-delimited spreadsheet with payroll info) in the UTL_FILE directory. Person B, a casual employee who is unhappy and about to be fired, happens to get access to the database because the demo user SCOTT is unlocked with password TIGER. Person B can now access the confidential data.

Oracle uses the ‘least privilege’ security principal. That means Oracle recommends several packages, such as UTL_FILE, be granted execute on a need-to-use basis instead of simply granting to public.

2. Excessive PUBLIC EXECUTE privileges

Recommendation: Oracle recommends that you revoke EXECUTE privileges on powerful packages from PUBLIC

Violation Count:4 Details: Package ­UTL_SMTP, ­DBMS_RANDOM, ­UTL_TCP, ­UTL_HTTP

Solution:

REVOKE EXECUTE ON UTL_SMTP FROM PUBLIC;
REVOKE EXECUTE ON DBMS_RANDOM FROM PUBLIC;­
REVOKE EXECUTE ON UTL_TCP­ FROM PUBLIC;
REVOKE EXECUTE ON UTL_HTTP FROM PUBLIC;

3. Well-known accounts Security

Recommendation: Oracle recommends that you to expire and lock well-known accounts

Violation Count:1 Details: Account­ OUTLN(open)

4. Default passwords Security

Recommendation: Oracle recommends that all default passwords be changed

Violation Count:1 Details: Account­OUTLN­

References:

Enable or Disable GATHER_STATS_JOB

Enable GATHER_STATS_JOB:

SQL> exec dbms_scheduler.enable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.

Check job status:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

Disable GATHER_STATS_JOB:

SQL> exec dbms_scheduler.disable(‘GATHER_STATS_JOB’);
PL/SQL procedure successfully completed.

Check job status:

SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

You can run the job manually via DBMS_SCHEDULER:

BEGIN
DBMS_SCHEDULER.RUN_JOB(
job_name => 'GATHER_STATS_JOB'
);
END;
/

References:

DISK_ASYNCH_IO

DISK_ASYNCH_IO controls whether I/O to datafiles, control files, and logfiles is asynchronous (that is, whether parallel server processes can overlap I/O requests with CPU processing during table scans).  If your platform supports asynchronous I/O to disk, Oracle recommends that you leave this parameter set to its default value. However, if the asynchronous I/O implementation is not stable, you can set this parameter to false to disable asynchronous I/O. If your platform does not support asynchronous I/O to disk, this parameter has no effect.

  • For optimum performance make sure you use asynchronous I/Os.
  • TRUE is the default parameter value for the majority of platforms.

SQL>SHOW PARAMETER disk_asynch_io
NAME TYPE VALUE
------------------------------------ ----------- ------
disk_asynch_io boolean TRUE
SELECT * FROM v$parameter WHERE lower(name) = 'disk_asynch_io' ;

These parameters enable or disable the operating system’s asynchronous I/O facility. They allow query server processes to overlap I/O requests with processing when performing table scans.

Asynchronous operations are currently supported for parallel table scans, hash joins, sorts, and serial table scans. However, this feature can require operating system specific configuration and may not be supported on all platforms.

Source/Reference Links: