Automating Database Startup and Shutdown on Linux

12C

STEP 1:
Once the instance is created, edit the "/etc/oratab" file setting the restart flag for each instance to 'Y'.

ORCL:/u01/app/oracle/product/12.2.0.1/db_1:Y

STEP 2:
Create a file called "/etc/init.d/dbora" as the root user, containing the following code.
The path and instance name should be adjusted to match your installation and instance.

#!/bin/sh
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.
#
# Set ORA_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
#
# Set ORA_OWNER to the user id of the owner of the 
# Oracle database in ORA_HOME.

ORA_HOME=/u01/app/oracle/product/12.2.0.1/db_1
ORA_OWNER=oracle
export ORACLE_UNQNAME=ORCL

if [ ! -f $ORA_HOME/bin/dbstart ]
then
    echo "Oracle startup: cannot start"
    exit
fi

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        # Remove "&" if you don't want startup as a background process.
        su $ORA_OWNER -c "$ORA_HOME/bin/dbstart $ORA_HOME" &
        touch /var/lock/subsys/dbora
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su $ORA_OWNER -c "$ORA_HOME/bin/dbshut $ORA_HOME"
        rm -f /var/lock/subsys/dbora
        ;;
esac

STEP 3:
Use the chmod command to set the privileges to 750.

chmod 750 /etc/init.d/dbora

STEP 4:
Associate the "dbora" service with the appropriate run levels and set it to auto-start using the following command.

chkconfig --add dbora

The relevant instances should now startup/shutdown automatically at system startup/shutdown.

For other versions and more details, Please refer to the below reference URL:
https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux

Automatically start Oracle Database and EM on Linux 7 after server reboot

Environment: Oracle database 11.2.0.4.0, Linux 7

Oracle 11g includes 2 scripts which can be used to start or shut down Oracle databases on Linux. Both scripts are installed in $ORACLE_HOME/bin and are called dbstart and dbshut. However, these scripts are not executed automatically after you reboot your server. I will explain here how you can configure that.
First, you need to make sure that any database instances you want to autostart are set to “Y” in the /etc/oratab file.

Sudo as root user

GPMPERF:/u01/app/oracle/product/11.2.0/db_1:Y

NOTE :GPMPERF is the SID

The /etc/oratab file is normally created by running the root.sh script at the end of the installation. If you don’t have the file, you can always add it to your system by creating it manually (with user root!).
Next, we are going to create 2 scripts under /home/oracle/scripts: ora_start.sh and ora_stop.sh. These scripts will call dbstart and dbshut and will also allow us to add some more actions, for example the start of the Enterprise Manager database control or any other services you might have.
$ su – oracle
$ vi /home/oracle/scripts/ora_start.sh

#!/bin/bash

# script to start the Oracle database, listener and dbconsole

. ~/.bash_profile

# start the listener and the database
$ORACLE_HOME/bin/dbstart $ORACLE_HOME

# start the Enterprise Manager db console
$ORACLE_HOME/bin/emctl start dbconsole

exit 0

$ vi /home/oracle/scripts/ora_stop.sh

#!/bin/bash

# script to stop the Oracle database, listener and dbconsole

. ~/.bash_profile

# stop the Enterprise Manager db console
$ORACLE_HOME/bin/emctl stop dbconsole

# stop the listener and the database
$ORACLE_HOME/bin/dbshut $ORACLE_HOME

exit 0

You see that inside the scripts, we are calling the .bash_profile file of the user “oracle”. This is needed to set the ORACLE_HOME environment variable.
Next, give execute rights to the scripts:
$  chmod u+x ora_start.sh ora_stop.sh
You could now test these scripts to see if they correctly shut down and start up your Oracle database.
We will now create a wrapper script that can be used to schedule as a service.
With user root, create a file called “oracle” under /etc/init.d.
$ vi /etc/init.d/oracle

#!/bin/bash
# chkconfig: 345 99 10
# description: Oracle auto start-stop script.

# Set ORA_OWNER to the user id of the owner of the
# Oracle database in ORA_HOME.

ORA_OWNER=oracle
RETVAL=0

case "$1" in
    'start')
        # Start the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_start.sh"
        touch /var/lock/subsys/oracle
        ;;
    'stop')
        # Stop the Oracle databases:
        # The following command assumes that the oracle login
        # will not prompt the user for any values
        su - $ORA_OWNER -c "/home/oracle/scripts/ora_stop.sh"
        rm -f /var/lock/subsys/oracle
        ;;
    *)
        echo $"Usage: $0 {start|stop}"
        RETVAL=1
esac
exit $RETVAL

$ chmod 750 /etc/init.d/oracle

To create a service of this script, run the following command:

$ chkconfig --add oracle

Next, check the script by running “service oracle stop” or “service oracle start” from the command line.

After this, it’s time for the final test: reboot your server and check if your Oracle database is automatically started after the reboot.

Manually sample [Scott] Schema creation

1. Login as sys or system
2.Run "utlsampl.sql" located in ORACLE_HOME/rdbms/admin directory.

Global temporary Table

Oracle allows us to create temporary tables to hold data just for the duration of a session or for a particular transaction.



ON COMMIT DELETE ROWS [The ON COMMIT DELETE ROWS Clause  indicates that the data should be deleted at the end of the transaction ,or end of the session]
======================
Example :

CREATE GLOBAL TEMPORARY TABLE my_test_tab
(
ID NUMBER,
description varchar2(10)
)
on commit delete rows;

SQL> insert into my_test_tab
  2  values (1,'one');

1 row created.

SQL> select count(*) from my_test_tab;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete.

SQL> select count(*) from my_test_tab;

  COUNT(*)
----------
         0

ON COMMIT PRESERVE ROWS [this clause indicates that rows should persist beyond the end of the transaction.they will be removed at the end of the transaction]
========================

CREATE GLOBAL TEMPORARY TABLE test_tab
(
ID NUMBER,
description varchar2(10)
)on commit preserve rows;

SQL> insert into test_tab values(1,'one');

1 row created.


SQL> select count(*) from test_tab;

  COUNT(*)
----------
         1

SQL> commit;

Commit complete.

SQL> select count(*) from test_tab;

  COUNT(*)
----------
         1

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost ~]$ sqlplus rajiv/rajiv@testdb


[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 31 18:00:14 2016

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn rajiv/rajiv
Connected.
SQL> select count(*) from test_tab;

  COUNT(*)
----------
         0

Getting Disk I/O information

select d.name,t.name,f.phyrds,f.phywrts,f.readtim,f.writetim from
v$datafile d,
v$filestat f,
v$tablespace t
where f.file#=d.file#
and d.ts#=t.ts#

TABLESPACE SIZE DETAILS

BREAK ON REPORT
COMPUTE SUM OF tbsp_size ON REPORT
compute SUM OF used ON REPORT
compute SUM OF free ON REPORT
COL tbspname FORMAT a20 HEADING 'Tablespace Name'
COL tbsp_size FORMAT 999,999 HEADING 'SIZE|(MB)'
COL used FORMAT 999,999 HEADING 'Used|(MB)'
COL free FORMAT 999,999 HEADING 'Free|(MB)'
COL pct_used FORMAT 999 HEADING '%Used'
select df.tablespace_name tbspname,
sum(df.bytes)/1024/1024 tbsp_size,
nvl(sum(e.used_bytes)/1024/1024,0) used,
nvl(sum(f.free_bytes)/1024/1024,0) free,
nvl((sum(e.used_bytes)*100)/sum(df.bytes),0) pct_used
from dba_data_files df,
(select file_id ,sum (nvl(bytes,0)) used_bytes
from dba_extents group by file_id) e,
(select max(bytes) free_bytes,file_id
from dba_free_space group by file_id) f
where e.file_id(+)=df.file_id and
df.file_id=f.file_id(+)
group by df.tablespace_name
order by 5 DESC;


===================================================
select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
from
(select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)/1024/1024 as tbs_size
from dba_data_files group by tablespace_name
UNION
select tablespace_name, sum(bytes)/1024/1024 tbs_size
from dba_temp_files
group by tablespace_name ) b
where a.tablespace_name(+)=b.tablespace_name;

Basics queries on Oracle Block

Query to identify header file and  header block


SQL> select segment_name,header_file,header_block from dba_segments where segment_name='EMP';

SEGMENT_NAME                                                                      HEADER_FILE HEADER_BLOCK
--------------------------------------------------------------------------------- ----------- ------------

EMP                                                                                         4          138

Get the dump of the block you need

===================================

SQL> Alter system dump datafile 4 block 138;

System altered.

the above query will produce a block dump in default trace directory [UDUMP] of the db.below is the output.

*** 2015-06-01 08:24:44.708

Start dump data blocks tsn: 4 file#:4 minblk 138 maxblk 138

Block dump from cache:

Dump of buffer cache at level 4 for tsn=4, rdba=16777354

BH (0x3d3f55c4) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3d2ee000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3d3f5a64,0x51ab3cd0] lru: [0x3d3f5744,0x3d3f559c]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149710],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149710],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3d3f59e8) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3d2f8000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3ebfaab8,0x3d3f5640] lru: [0x3d3f5b68,0x3d3f59c0]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.1496ea],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.1496ea],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3ebfaa3c) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebba000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3ebfa9e4,0x3d3f5a64] lru: [0x3ebfaa14,0x3ebfabbc]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149697],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149697],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3ebfa968) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebb8000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x3ebfad34,0x3ebfaab8] lru: [0x3ebfa798,0x3ebfaae8]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149688],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149688],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

BH (0x3ebfacb8) file#: 4 rdba: 0x0100008a (4/138) class: 4 ba: 0x3ebc0000

  set: 3 pool 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 382,28

  dbwrid: 0 obj: 74741 objn: -1 tsn: 4 afn: 4 hint: f

  hash: [0x51ab3cd0,0x3ebfa9e4] lru: [0x3ebfae38,0x3ebfac90]

  ckptq: [NULL] fileq: [NULL] objq: [NULL]

  st: CR md: NULL tch: 1

  cr: [scn: 0x0.149679],[xid: 0x0.0.0],[uba: 0x0.0.0],[cls: 0x0.149679],[sfl: 0x0],[lc: 0x0.0]

  flags:

  cr pin refcnt: 0 sh pin refcnt: 0

Block dump from disk:

buffer tsn: 4 rdba: 0x0100008a (4/138)

scn: 0x0000.000db7b9 seq: 0x01 flg: 0x04 tail: 0xb7b92301

frmt: 0x02 chkval: 0x8872 type: 0x23=PAGETABLE SEGMENT HEADER

Hex dump of block: st=0, typ_found=1

Dump of memory from 0x00878200 to 0x0087A200

878200 0000A223 0100008A 000DB7B9 04010000  [#...............]

878210 00008872 00000000 00000000 00000000  [r...............]

878220 00000000 00000001 00000008 00000A9C  [................]

878230 00000000 00000004 00000008 0100008C  [................]

878240 00000000 00000000 00000000 00000004  [................]

878250 00000000 00000000 00000000 00000000  [................]

878260 00000004 00000008 0100008C 00000000  [................]

878270 00000000 00000000 00000004 01000088  [................]

878280 01000088 00000000 00000000 00000000  [................]

878290 00000000 00000000 00000000 00000000  [................]

        Repeat 3 times

8782D0 00000001 00002000 00000000 00001434  [..... ......4...]

8782E0 00000000 01000089 00000001 01000088  [................]

8782F0 01000089 00000000 00000000 00000000  [................]

878300 00000000 00000000 00000001 00000000  [................]

878310 000123F5 10000000 01000088 00000008  [.#..............]

878320 00000000 00000000 00000000 00000000  [................]

        Repeat 152 times

878CB0 01000088 0100008B 00000000 00000000  [................]

878CC0 00000000 00000000 00000000 00000000  [................]

        Repeat 151 times

879640 00000000 00000000 01000089 00000000  [................]

879650 00000000 00000000 00000000 00000000  [................]

        Repeat 185 times

87A1F0 00000000 00000000 00000000 B7B92301  [.............#..]

  Extent Control Header

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

  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 8     

                  last map  0x00000000  #maps: 0      offset: 2716  

      Highwater::  0x0100008c  ext#: 0      blk#: 4      ext size: 8     

  #blocks in seg. hdr's freelists: 0     

  #blocks below: 4     

  mapblk  0x00000000  offset: 0     

                   Unlocked

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

  Low HighWater Mark : 

      Highwater::  0x0100008c  ext#: 0      blk#: 4      ext size: 8     

  #blocks in seg. hdr's freelists: 0     

  #blocks below: 4     

  mapblk  0x00000000  offset: 0     

  Level 1 BMB for High HWM block: 0x01000088

  Level 1 BMB for Low HWM block: 0x01000088

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

  Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0      

  L2 Array start offset:  0x00001434

  First Level 3 BMB:  0x00000000

  L2 Hint for inserts:  0x01000089

  Last Level 1 BMB:  0x01000088

  Last Level II BMB:  0x01000089

  Last Level III BMB:  0x00000000

     Map Header:: next  0x00000000  #extents: 1    obj#: 74741  flag: 0x10000000

  Inc # 0 

  Extent Map

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

   0x01000088  length: 8     

  

  Auxillary Map

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

   Extent 0     :  L1 dba:  0x01000088 Data dba:  0x0100008b

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

  

   Second Level Bitmap block DBAs 

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

   DBA 1:   0x01000089

  

End dump data blocks tsn: 4 file#: 4 minblk 138 maxblk 138

Simple example to get the table name from the block dump information.

SQL> select name from sys.obj$ where obj#=74741;

NAME

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

EMP