Post Database Creation Tasks

<- Create

Overview

What follows are common tasks performed after a new database is created.

TOC

ADR 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

--DB

 set homepath diag\rdbms\MySID\MySID;
 set control (SHORTP_POLICY = 240);
 set control (LONGP_POLICY = 1080);

--Listener

 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

https://<hostname>:<port>/em
   https://lnx01:5500/em

https//<RACScan>.<hostname>:<port>/em
   https://rac-scan.localdomain:5500/em

Configure (if need be):

 For HTTPS: exec dbms_xdb_config.sethttpsport(5500);
 For HTTP:  exec dbms_xdb_config.sethttpport(8080);

Display Ports Used

 For HTTPS: select dbms_xdb_config.getHttpsPort() from dual;
 For HTTP:  select dbms_xdb.getHttpPort() from dual;

Grant View-Only Access

 grant EM_EXPRESS_BASIC to <user>;

Known Issues

• Oracle Linux 7.x\Firefox Bug Work Around

Password Changes

Password Policy

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;

Redo Logs

• 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.

RMAN

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

1. Edit Startup File: /etc/oratab
   Set the restart flag for each instance to Y
   db1:/u01/app/oracle/product/12.1.0/db_home1:Y
2. Configure dbora script.

Tablespace Sizing

• 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.

Undo Retention

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.