Summary steps to apply PSU 11.2.0.3.2

It’s recommended to patch the PSU manually  on IBM Linux on z and other platform.  The opatch auto is still testing by Oracle support. and it won’t be stable (at least from what we’ve been tested)  until July 2013 PSU patchsets.  There are couple note that worth to mention: 

·         Before applying GI home, you must run rootcrs.pl to unlock the permissions.  Otherwise, the patching process will be failed with Opatch failed with error code 39.

·         When applying a GI patch to either the Grid Home or Database Home, you use "napply"

·         When applying a Database patch to either Database Home or Grid Home, you use "apply"

1)  export PATH=$ORACLE_HOME/OPatch:$PATH

2)  opatch version

     OPatch Version: 11.2.0.3.4 ß-------- Make sure you have the latest opatch version.

3) opatch prereq CheckconflictAgainstOHWithDetail -phBaseDir ./13696251

     Oracle Interim Patch Installer version 11.2.0.3.4

    Copyright (c) 2012, Oracle Corporation.  All rights reserved.

…….

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

4)  sudo -u root $ORACLE_HOME/crs/install/rootcrs.pl –unlock Don’t forget this step!

[sudo] password for oracle:

Using configuration parameter file: /u01/app/oracle/11.2.0.3/grid/crs/install/crsconfig_params

Grid Home Patch:

5)  opatch napply -oh /u01/app/oracle/11.2.0.3/grid -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696251

6) opatch apply -oh /u01/app/oracle/11.2.0.3/grid -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696216

Database Patch:

7) opatch napply -oh /u01/app/oracle/11.2.0.3/racdb -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696251/custom/server/13696251

8) opatch apply -oh /u01/app/oracle/11.2.0.3/racdb -local /data/oracle/bkup1/software/psu11.2.0.3.2/13696216

9) /data/oracle/bkup1/software/psu11.2.0.3.2/13696251/custom/server/13696251/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/11.2.0.3/racdb

10) as root:  /u01/app/oracle/11.2.0.3/grid/rdbms/install/rootadd_rdbms.sh

11) as root :  /u01/app/oracle/11.2.0.3/grid/crs/install/rootcrs.pl -patch

OPatch failed to locate Central Inventory

Problem:

Inventory load failed... OPatch cannot load inventory for the given Oracle Home.
Possible causes are:
   Oracle Home dir. path does not exist in Central Inventory
   Oracle Home is a symbolic link
   Oracle Home inventory is corrupted
LsInventorySession failed: OracleHomeInventory gets null oracleHomeInfo
OPatch failed with error code 73

Solution:

+ASM2 > /aps/oracle/product/11.2.0.3/grid/oui/bin/runInstaller -attachHome -noClusterEnabled ORACLE_HOME=/aps/oracle/product/11.2.0.3/grid ORACLE_HOME_NAME=Ora11g_gridinfrahome1 CLUSTER_NODES=glscind01,glscind02 "INVENTORY_LOCATION=/aps/oracle/oraInventory" -invPtrLoc "/aps/oracle/product/11.2.0.3/grid/oraInst.loc" LOCAL_NODE=glscind02   
Starting Oracle Universal Installer...
   
    Checking swap space: must be greater than 500 MB.   Actual 7844 MB    Passed
    The inventory pointer is located at /aps/oracle/product/11.2.0.3/grid/oraInst.loc
    The inventory is located at /apps/oracle/oraInventory
    'AttachHome' was successful.

Frequency v.s Heigh Balanced Histogram

Frequency

·         Each bucket has its own size (counts of rows with that value)

·         NDV <= Buckets request <=254

·         One distinct value per bucket

·         Bucket_size = rows with “that” particular value

  

Height Balanced

·         Bucket size = (num_rows – num_nulls) / num_buckets

·         Values are sorted before splitting them into buckets

·         One or more distinct values per bucket

·         Only values of end-points are stored in histogram

·         Bucket_size = (Orig_CarD – Nnulls) / Buckets_total

dbms_stats

SQL> select dbms_stats.get_stats_history_retention from dual;

GET_STATS_HISTORY_RETENTION

---------------------------

                         31 => this is the default value

If you like to change it to 45 days, then use the ALTER_STATS_HISTORY_RETENTION procedure

SQL> exec dbms_stats.alter_stats_history_retention(45);

PL/SQL procedure successfully completed.

SQL> select dbms_stats.get_stats_history_availability from dual; => this query will return the date statistics have been purged up to. 

GET_STATS_HISTORY_AVAILABILITY

---------------------------------------------------------------------------

18-APR-13 07.20.37.099029000 PM -05:00

SQL> select table_name, stats_update_time from dba_tab_stats_history where owner = ‘GLS’;  è this query shows the time when  given tables’ statistics were re-gathered

  

TABLE_NAME                     STATS_UPDATE_TIME

------------------------------ ---------------------------------------

GLS_CONTACT_NEW                 19-MAY-13 09.56.39.845243 PM -05:00

GLS_CONTACT_NEW                 06-MAY-13 05.22.14.338999 PM -05:00

To restore table statistics:   For example, for GLS_CONTACT_NEW table, the statistics on May 19 is bad but May 6^th is good, so you like to restore it to May 6^th.

SQL> execute dbms_stats.restore_table_stats('GLS','GLS_CONTACT_NEW','06-MAY-13 05.22.14.338999 PM -05:00');

PL/SQL procedure successfully completed.

To compare statistics for a table from two timestamps or date in the past

select * from table(dbms_stats.diff_table_stats_in_history
(ownname => upper('&ownname'),
tabname => upper('&tabname'),
time1 => systimestamp,
time2 => to_timestamp('&time2','mm/dd/yyyy'),
pctthreshold => 0))

Whenever it is necessary, statistics can be restored from the history.  The dbms_stats provides:

Execute dbms_stats.restore_table_stats(‘owner’, ‘table’, date)

Execute dbms_stats.restore_database_stats(date)

Execute dbms_stats.restore_dictionary_stats(date)

Execute dbms_stats.restore_fixed_objects_stats(date)

Execute dbms_stats.restore_schemas_stats(‘owner’,date)

Execute dbms_stats.restore_system_stats(date)

To set table statistics, you can use dbms_stats.set_table_stats

SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE', numrows=>140);
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_table_stats(ownname=>'GLS', tabname=>'TEST_ISSUE',numblks=>10);
PL/SQL procedure successfully completed.

To set index statistics, you can use dbms_stats.set_index_stats to set number rows in index, number of leaf blocks, distinct keys, clustering factor, heigh of index 

exec dbms_stats.set_index_stats (ownname=>'GLS',tabname=>'PROD_NEW',numdist=>1000);

DBMS_SQLTUNE package to Run the Sql Tuning Advisor (ID 262687.1)

Check out the SQL TUNING document is from metalink (ID 262687.1).  It’s very handy when you need to get some advices to tune a poor SQL query using dbms_sqltune package. 

DECLARE

  my_task_name VARCHAR2(30);

  my_sqltext CLOB;

BEGIN

  my_sqltext := 'SELECT * '   ||

                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                           sql_text => my_sqltext,

                           user_name => 'SH',

                           scope => 'COMPREHENSIVE',

                           time_limit => 60,

                           task_name => ‘gls_sql_tuning_task’,

                           description => 'Task to tune a query on a specified PRODUCT');

END;

Execute the tuning task.:

Execute dbms_sqltune.Execute_tuning_task (task_name => ‘gls_sql_tuning_task’);

Check the status of the task using following query:

 select status from dba_advisor_log where task_name=‘gls_sql_tuning_task’;

View the Recommendations

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task(‘gls_sql_tuning_task’) from dual;

The report can suggest to add index, or gather statistics, or SQL profile.  In my case, the sql_profile was recommended and to accept it, the performance was improved 99.9%

execute dbms_sqltune.accept_sql_profile(task_name =>‘gls_sql_tuning_task’, task_owner => 'SYSTEM', replace => TRUE);

12c Database cheat sheet

This sheet is work in progress.  Please come back for the update...

select view_name from dab_views where view_name like 'CDB%'

select name, pdb from cdb_services;

show con_name

alter pluggable database all open;

startup nomount

select name, open_mode from v$pdbs;

alter database mount; --> select name, open_mode from v$pdbs; all mount

alter database open; -->PDBs$SEED mount mode

alter pluggable database all open:  all pluggable databases in read/write 

select name, pdb from cdb_services;

connect sys/oracle@pdb1 as sysdba

show con_name

show parameter ddl_lock_timeout

There is a single SPFILE per CDB to store prameters. 

SQL> connect sys/oracle@pdb1 as sysdba
Connected.

Instance PDB changes example:

SQL> show parameter ddl_lock_timeout

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout             integer     0
SQL> alter system set ddl_lock_timeout=10;
System altered.

SQL> show parameter timeout

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
ddl_lock_timeout             integer     10

SQL> alter system set sessions=150;
System altered.

select name, value, ispdb_modifiable, con_id from v$system_parameter where ispdb_modifiable='TRUE' ==> there are 174 parameters

select p.con_id, p.name, p.dbid, s.name, s.value, s.ispdb_modifiable

from v$system_parameter s, v$pdbs p

where s.con_id = p.con_id and

s.name = 'sessions';

CREATE Permanent tablespaces in a CDB

SQL> create tablespace cdb_tools datafile '/u01/app/oracle/oradata/cdb1/cdb_tools01.dbf' size 10m;                                 
Tablespace created.

SQL> show con_name
CON_NAME
------------------------------
PDB2
SQL> create tablespace pdb2_tools datafile '/u01/app/oracle/oradata/cdb1/pdb2/pdb2_tools01.dbf' size 5m;
Tablespace created.

Create user in Container ==> has to be inside the container DB

SQL> create user C##_MYID_ALL identified by ali container=ALL;
User created.

Create user in PDB ==> from PDB

SQL> create user MYID_DBA identified by ali container=CURRENT;
User created.

Create Tablespaces CDB and PDB

Prior version of Oracle, all the tablespaces belong to one database. In the CDB, however, there is one set of tablespaces belong to the root container and each PDB has its own set of tablespaces. All common objects are created in the root container's tablespace and is visible in the PDBs through links.

There is only one active UNDO tablespace per CDB, and it's common to all PDBs.

Create a tablespace in a root CDB

SQL> create tablespace cdb_tools datafile '/u01/app/oracle/oradata/cdb1/cdb_tools01.dbf' size 10m;

Tablespace created.

Create a permanent tablespace in a PDB

SQL> show con_name

CON_NAME
------------------------------
PDB2

SQL> create tablespace pdb2_tools datafile '/u01/app/oracle/oradata/cdb1/pdb2/pdb2_tools01.dbf' size 5m;

Tablespace created.

Managing CDB and PDB Databases

When you startup a CDB, the sequence of operations performed as following:

Instance is started -->Control files are opened --> The root container is opened redo logs and root data files -->The seed pluggable database in READ ONLY mode-->PDBs are stilled in MOUNTED mode--> Triggers can fire to open other PDBs

SQL> alter database cdb1 open;

SQL> alter pluggable database all close;

SQL> alter pluggable database all open;

SQL> alter pluggable database all close;

SQL > alter pluggable database all except GLS close;

SQL> alter pluggable database all open read only;

SQL> select name, open_mode from v$pdbs;

NAME       OPEN_MODE

------------------------------ ----------

PDB$SEED       READ ONLY

PDB1       READ ONLY

PDB2       READ ONLY

GLS       READ ONLY

SQL> alter pluggable database gls close;

SQL> alter pluggable database gls open;

SQL> select * from cdb_pdbs;

SQL > select * from cdb_tablespaces;

SQL> select * from cdb_data_files;

select name, pdb from cdb_services

SQL> select name, open_mode from v$pdbs;

NAME        OPEN_MODE

------------------------------ ----------

PDB$SEED        MOUNTED

PDB1        MOUNTED

PDB2        MOUNTED

GLS        MOUNTED

SQL > select name, pdb, network_name from cdb_pdb;

SQL> connect sys/oracle123@pdb1 as sysdba

Connected.

SQL > show con_name

To be continued...

Container and Pluggable Databases

Pluggable Database is the new feature and allows the DBA to consolidate larget number of small database applications departmental applications into a single larger RDBMS installation.  It's a set of database schemas that appears logically to users and applications as a separate database.  The Oracle Metadata and user data are totally separated into two sections.  One is Container DB (CDB) stores metadata and One is Pluggable DB(PDB) which stores user data.  This will reduce significant percentage of hardware deployment, patch and upgrade, instance and storage overhead and DBA time.

With Oracle 11g, there are non container databases and share nothing.  As a result, there are too many background processes with high shared memory and many copies of Oracle metadata.  When you need to upgrade/patch the database applications, it's time consuming as you have to upgrade each database.

Benefits of Pluggable Databases (PDBs):
Multiple databases in a centrally managed platform
Easy and Fast provisioning, patching and upgrading time savings
Central Management and administration of databases (backups, restore, disaster recovery, patching/upgrading)
Secure separation of duties (the administrator of an application can do all the required tasks, but cannot see other PDBs in the same Container Database(CDB)

From the Enterprise Manager
A Container Database (CDB) can be defined as target and a Pluggable database can be setup as a sub target of a CDB target.  After the creation of the Oracle database, only objects in the data dictionary ar the Oracle-supplied objects.

Create a new CDB either via DBCA or SQLPLUS:

• Configure an instance with init.ora parameter file then STARTUP NOMOUNT.  Be sure to check (DB_NAME, CONTROL_FILES, DB_BLOCK_SIZE, _ENABLE_PLUGGABLE_DATABASE=TRUE
• Create CBD using the CREATE DATABASE command with a new clause ENABLE PLUGGABLE DATABASE.  This will create the root container with the control files during the mount phase, the redo log files and root data files.  The root data files are used for the SYSTEM (metadata & dictionary) and SYSAUX (AWR).  It also create the seed pluggable database with its own data files used for SYSTEM and SYSAUX.  The FILE_NAME_CONVERT, like RMAN clone, to rename the data files of the seed pluggable database while copying from the root container to another location (see demo below)

SQL> connect / as sysdba
SQL > Startup nomount
SQL > Create database ORDER user sys identified by passcode user SYSTEM identified by passcode2 EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE temp UNDO TABLESPACE UNDOTBS DEFAULT tablespace users ENABLE PLUGGABLE DATABASE

Create Pluggable database:

SQL> create pluggable database GLS admin user admin identified by admin file_name_convert= ('/pdbseed/','gls/');

Pluggable database created


SQL> alter pluggable database GLS open;

If you create the database with DBCA, then select the "Create As Container Database"

Enterprise Manager (EM) Cloud Control and Database Express

Enterprise Manager Database Control is not available in 12c database.  There are enhancement in Enterprise Manager Cloud Control Interface and Database Express Navigation.  The enterprise manager cloud control is composed of Four main components

1. The Oracle Management Repository (OMR)
2. The Oracle Management Service (OMS)
3. The Oracle Management Agent (OMA or agent) with target specific plug-ins
4. The Cloud Control Console

To start the whole enterprise manager cloud control frame work, follow the steps below:

• Startup the repository database listener:  lnsrctl start
• Startup the repository database:  $ORACLE_HOME/bin/sqlplus / as sysdba -> Startup
• Start the OMS including OHS and WebLogic Managed Server:  emctl start oms
• Start the AGENT on OMS repository host:  emctl start agent
• Start the AGENT on the managed/target servers:  emctl start agent

To stop the enterprise manager cloud control, perform the above steps (bottom to top)

Type of Credentials and Three levels of access 

Named Credentials:  You can store credential username/password, public key-private key pair.  It's used when performing operation like system management tasks (running jobs, patching, changing password, etc)

Preferred Credentials:  You can store access of managed targets login credentials in the Management Repository to simplify the access

Default Credentials:  You can set a particular target type and is available for all the targets of the target type.  

There are three level of access:  View access, Edit access (change the credentials, name/password), and Full access (complete access including the ability to delete the named credential)

Enterprise Manager Database Express:  It's a replacement of EM Database Control and is created via DBCA with XMLDB components installed.  The homepage represents an overall view of the database instance status and activity.  The EM Database Express is built on the Common Reporting Framework in the database and is available when the database is opened.