Post Database Creation Tasks
What follows are common tasks performed after a new database is created.
- ADR Retention
- AWR Snapshot Retention
- Database Express | Oracle Linux 7.x\Firefox Bug Work Around
- Deferred Segment Creation
- Exports (Configure Data Pump)
- Hugepages Settings
- NTP, Configure
- Password Changes
- Redo Logs
- SQLPlus (glogin.sql)
- Start-Stop DB on OS Boot-Shutdown (for non-RAC DB)
- Tablespace Sizing
- Undo Retention
To collect enough useful logs but not overwhelm disk space set ADR to below values. This will also significantly speed up SQL queries into ADR.
OS> adrci adrci> show homes
set homepath diag\rdbms\MySID\MySID; set control (SHORTP_POLICY = 240); set control (LONGP_POLICY = 1080);
cd .. set homepath diag\tnslsnr\MySID\listener; set control (SHORTP_POLICY = 72); set control (LONGP_POLICY = 240);
AWR Snapshot Retention
The Automatic Workload Repository (AWR) takes hourly snapshots of key performance indicators. Oracle's predictive analysis of the database can be improved if this is increased from the default (7 days). Consider retaining this information for at least a month. You can use the following command to do this.
BEGIN DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(43200,60); END;
Deferred Segment Creation
In Oracle 11g and later, in some instances, you may sporadically get Quota Errors upon INSERT operations and DDL for tables if you do not set Deferred_Segment_Creation to FALSE. This is not the default so you must make this change to avoid these errors.
ALTER SYSTEM SET deferred_segment_creation=FALSE SCOPE=both;
Exports (Configure Data Pump)
-- Init Physical Directory
OS> mkdir -p /u02/exports OS> chown -R oracle:oinstall /u02/exports OS> chmod -R 775 /u02/exports
-- Init Directory Object
SQL> CREATE OR REPLACE DIRECTORY datapump AS '/u02/exports'; SQL> GRANT read,write ON DIRECTORY datapump TO system;
OEM Database Express
12c Oracle did away with dbconsole and put in its place Oracle Enterprise Manager Database Express. To configure OEM Database Express you simply need to run one of the below commands and specify a port.
Access: from local system use Firefox, from remote system use IE.
To Configure (if need be):
For HTTPS: exec dbms_xdb_config.sethttpsport(5500); For HTTP: exec dbms_xdb_config.sethttpport(8080);
To see what ports have been configured you can run one of the below commands.
For HTTPS: select dbms_xdb_config.getHttpsPort() from dual; For HTTP: select dbms_xdb.getHttpPort() from dual;
To grant users view only access to see the web UI interface you can grant them the role EM_EXPRESS_BASIC.
grant EM_EXPRESS_BASIC to <user>;
Passwords should be at least eight characters in length and contain combinations of the following:
- Numbers (1, 2, 3...)
- Upper case letters
- Lower case letters
- Characters (!, @, $, %, *, etc.)
Both Oracle and user DBA accounts should reflect this policy. This includes but is not limited to the accounts SYS, SYSTEM and SYSMAN. Ideally use for this.
Password Policy Expiration
In 11g and later the DEFAULT policy is now set to expire all passwords after 180 days. To turn this off use the below command:
ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME UNLIMITED;
- The goal here is to have a log switch about every 20-30 minutes. Use the above values if you are not sure then resize them after analyzing the production load.
- In the process of applying major patchsets ideally you will need to have at least 4 redo log groups where each member is at least 100 mb.
- On some systems where you suspect disk I/O is inadequate you can create just one redo group and one archive log destination later adding a second as disk I/O proves to be acceptable.
Configure RMAN directory.
mkdir -p /u02/rman chown -R oracle:oinstall /u02/rman chmod -R 775 /u02/rman
Then configure RMAN Scripts.
SQLPlus - Set Global Profile Settings
Edit $ORACLE_HOME/sqlplus/admin/glogin.sql to set common values.
SET LINESIZE 240 SET PAGESIZE 9999 SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER> "
Start-Stop Database on OS Boot-Shutdown
- Edit Startup File: /etc/oratab
Set the restart flag for each instance to Y
- Configure dbora script.
- Import operations and routine Oracle patches may require more capacity for TEMP and UNDO tablespaces than the standard data load. 250 mb for TEMP and UNDO should accommodate these operations in most cases. If not, size these accordingly for your environment.
- Disk space permitting, enable auto-extend for all tablespaces and set a max size less than the size of your disk or volume.
You may wish to set UNDO_RETENTION at this point to help avoid getting "snapshot too old" messages.
Common values in this instance range from 21600 (6 hours) to 43200 (12 hours).
ALTER SYSTEM SET UNDO_RETENTION=43200 SCOPE=both;
Init Env Example Session
Tablespace Changes ALTER TABLESPACE temp RESIZE 100g; ALTER TABLESPACE undotbs1 RESIZE 32g; ALTER TABLESPACE undotbs2 RESIZE 32g; ALTER SYSTEM SET UNDO_RETENTION=21600 SCOPE=both SID='sid|*'; ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME unlimited; File System Changes su - mkdir -p /u01/app/scripts chown -R oracle:oinstall /u01/app/scripts chmod -R 775 /u01/app/scripts # Node 1 mkdir -p /u02/rman chown -R oracle:oinstall /u02/rman chmod -R 775 /u02/rman # Node 2 mkdir -p /u02/exports chown -R oracle:oinstall /u02/exports chmod -R 775 /u02/exports Data Pump CREATE OR REPLACE DIRECTORY datapump AS '/u02/exports'; GRANT read,write ON DIRECTORY datapump TO system; .bashrc alias dev='cd /u01/app/scripts;pwd' alias dmp='cd /u02/exports;pwd' alias bkp='cd /u02/rman;pwd'
Modify these common init actions for your environment.