Author: dbalifeeasy

How to Configure 12c Enterprise Manager Database Express

  • Architecture for Oracle Enterprise Manager Database Express:

EM_DB_X

  • Check and configure DISPATCHERS parameter:
SQL> show parameter dispatchers

NAME          TYPE      VALUE
------------- --------- --------------------------------
dispatchers   string    (PROTOCOL=TCP) (SERVICE=cdb2XDB)
  • Use DBMS_XDB_CONFIG to set up HTTP or/and HTTPS ports

For HTTPS:

SQL> exec DBMS_XDB_CONFIG.sethttpsport(5500);

or for HTTP:

SQL> exec DBMS_XDB_CONFIG.sethttpport(5501);
  • To check the HTTPs or HTTP ports by:
SQL> select dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT
------------
5500

SQL>select dbms_xdb_config.gethttpport from dual;

GETHTTPPORT
-----------
5501
  •  Connect to Enterprise Manager Database Express  Console:

https://hostname:5500/em

or

http://hostname:5501/em

OEM Logon

 

Automating 12c Database Startup and Shutdown on Oracle Linux 7

PURPOSE

Oracle recommends to configure the system to automatically start Oracle Database when the system starts, and to automatically shut it down when the system shuts down.

Environment:
Operating System:     Oracle Linux 7
Oracle Database:        Oracle 12.1.0.2

SOLUTION

  • Check and configure  /etc/oratab appropriately
    If you want dbstart to auto-start a single-instance database that uses an ASM server that is auto-started by CRS (this is the default behavior  for an ASM cluster), you must change the database’s ORATAB entry to use  a third field of “W” and the ASM’s ORATAB entry to use a third field of “N”.
  • Change directory to /etc/init.d, and create a file “dbora” with below contents. The Oracle doc has got typo for this script part  “runuser $HOST -l $ORACLE  $0 $1 ORA_DB”, which will cause failure.
$ cat dbora
#! /bin/sh -x
#
# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for your installation.
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1
#
# Change the value of ORACLE to the login name of the
# oracle owner at your site.
#
ORACLE=oracle
PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
#
if [ ! "$2" = "ORA_DB" ] ; then
 #runuser $HOST -l $ORACLE  $0 $1 ORA_DB
    runuser  -l $ORACLE  $0 $1 ORA_DB
if [ "$PLATFORM" = "Linux" ] ; then
touch /var/lock/subsys/dbora
fi
exit
fi
#
case $1 in
'start')
$ORACLE_HOME/bin/dbstart $ORACLE_HOME &
;;
'stop')
$ORACLE_HOME/bin/dbshut $ORACLE_HOME &
;;
*)
echo "usage: $0 {start|stop}"
exit
;;
esac
#
exit
  • Change file “dbaora” with right group name and permission.
# chgrp dba dbora
# chmod 750 dbora
  •  Create symbolic links to the dbora script in the appropriate run-level script directories:
Platform Symbolic Links Commands
Oracle Solaris 
# ln -s /etc/init.d/dbora /etc/rc0.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc3.d/S99dbora
Linux 
# ln -s /etc/init.d/dbora /etc/rc.d/rc0.d/K01dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc3.d/S99dbora
# ln -s /etc/init.d/dbora /etc/rc.d/rc5.d/S99dbora
IBM AIX on POWER Systems (64-Bit) 
# ln -s /etc/dbora /etc/rc.d/rc2.d/S99dbora
# ln -s /etc/dbora /etc/rc.d/rc0.d/K01dbora

Check the logs

  • /var/log/messages
  • $ORACLE_HOME/listener.log
  • $OACLE_HOME/shutdown.log
  • $ORACLE_HOME/startup.log

How to send email notifications for dbms_scheduler jobs

SITUATION

Jobs can be scheduled  from either OEM ( Oracle Enterprise Manager ) or database DBMS_SCHEDULER. The following two pictures  show the boxes ticked will trigger notifications when the event happens.

Environment:

                 OEM : 12c or 13c
                 Database: any versions

(picture 1 ) — OEM JOB

OEM Job Email Notification


( picture 2 )– DBMS_SCHEDULER JOB

DBMS_SCHEDULER Job Notification

Email notification for OEM jobs works fine, but we could not receive email notifications from DBMS_SCHEDULER  jobs.

SOLUTION

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Sample script to drop interval partitions

SITUATION

The big benefit of using interval partition is automatic partition creation when new records are inserted. But we need purge the history partitions by developing in-house scripts.

SQL> desc user_tab_partitions;

Name                    Null?         Type
----------------------- -------- --------------
TABLE_NAME                         VARCHAR2(30)
COMPOSITE                           VARCHAR2(3)
PARTITION_NAME                     VARCHAR2(30)
SUBPARTITION_COUNT                       NUMBER
HIGH_VALUE                                 LONG
HIGH_VALUE_LENGTH                        NUMBER
PARTITION_POSITION                       NUMBER

SOLUTION

Here is a basic sample script for dropping partitions more than 20 days old.

SQL> set serveroutput on
SQL> declare
     dt date;
     begin
       for x in (select table_name,partition_name, high_value 
                   from user_tab_partitions where INTERVAL='YES' )
       loop
        execute immediate 'select '||x.high_value||' from dual' into dt;
        if dt < sysdate - 20
        then
          dbms_output.put_line('to drop partition: '||x.table_name||'.'||x.partition_name);
          execute immediate 'alter table '||x.table_name||' drop partition '|| x.partition_name||' update indexes ';
        end if;
       end loop;
    end;
/

to drop partition: TBL_TEST1.SYS_P277
to drop partition: TBL_TEST1.SYS_P221
to drop partition: TBL_TEST2.SYS_P231
to drop partition: TBL_TEST2.SYS_P281
to drop partition: TBL_TEST3.SYS_P226
to drop partition: TBL_TEST3..SYS_P242

PL/SQL procedure successfully completed.

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

SITUATION

An interval partition table structure is just created, then try to import the data from exported dump file. It causes ORA-14300 error. The same error occurs when insert records from another online backup table:

SQL> insert into tbl_test select * from tbl_test_old;
insert into tbl_test select * from tbl_test_old;
*
ERROR at line 1:
ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

CAUSE

There are NULL values in partition key column.

SOLUTION

Update NULL values of  partition key column with non-null values, or remove those invalid records from source data.

Please note there are possible 1048575 partitions or sub-partitions for the partition key:

$ oerr ora 14300
14300, 00000, "partitioning key maps to a partition outside maximum permitted number of partitions"
// *Cause: The row inserted had a partitioning key that maps to a partition number greater than 1048575
// *Action Ensure that the partitioning key falls within 1048575 partitions or subpartitions.