Oracle and systemd

# http://docs.oracle.com/database/121/index.htm
# https://172.16.148.137:5500/em/login
# start db:
[oracle@redhat7 ~]$ sqlplus / AS SYSDBA
STARTUP
# start TNS listener
[oracle@redhat7 ~]$ lsnrctl start
# TNS listener status
[oracle@redhat7 ~]$ lsnrctl status
cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# stop TNS listener
[oracle@redhat7 ~]$ lsnrctl stop
# TNS listener status
ps -ef | grep [t]nslsnr
# oracle + listener startsqlplus system/Yxx32145 AS SYSDBA
# for this you need the apropiate environment variables in /home/oracle/.bash_profile e.g.:
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/usr/lib64; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
# then allow dbstart in /etc/oratab to start a specific instance:
[root@redhat7 ~]# sed -i s/"N$"/"Y"/ /etc/oratab
[oracle@redhat7 ~]$ $ORACLE_HOME/bin/dbstart $ORACLE_HOME
# oracle status:
ps -ef | grep [o]racle
ps -ef | grep [o]ra_
# oracle status from sqlplus:
[oracle@redhat7 ~]$ sqlplus / AS SYSDBA
select INSTANCE_NAME, STARTUP_TIME, STATUS, ARCHIVER, "THREAD#", LOGINS, INSTANCE_ROLE, ACTIVE_STATE from v$instance;
select SPID as "Process ID" , STID as "Thread ID", PNAME as "Process Name", EXECUTION_TYPE as "Process Type" from v$process order by 4,1,2;
# stop db:
[oracle@redhat7 ~]$ sqlplus / AS SYSDBA
SHUTDOWN IMMEDIATE
# or allow dbstart in /etc/oratab to start a specific instance (stop db + listener):
# see http://docs.oracle.com/database/121/UNXAR/strt_stp.htm#UNXAR417
# see https://oracle-base.com/articles/linux/automating-database-startup-and-shutdown-on-linux
[root@redhat7 ~]# sed -i s/"N$"/"Y"/ /etc/oratab
[oracle@redhat7 ~]$ $ORACLE_HOME/bin/dbshut $ORACLE_HOME
tailf $ORACLE_HOME/startup.log
# list options existing in a response file:
grep -v -P "^#|=$" KIT/Oracle/db.rsp | grep -v "^$"
# Log messages are written to:
/u01/app/oracle/diag/rdbms/orcl/orcl/trace/alert_orcl.log
/u01/app/oracle/diag/rdbms/orcl/orcl/alert/log.xml
find /u01 -name log.xml

# when after normal shutdown oracle processes are still running do a shutdown abort from sqlplus

# show oracle pid (for systemd):
[oracle@redhat7 ~]$ output=$(sqlplus -S / AS SYSDBA <<EOF
select SPID from v\$process where PNAME = 'PMON';sysman
EOF
);echo "$output" | grep -P "\d+"
# or show this way:
ps -e | grep [p]mon_orcl | awk '{print $1;}'

# change $ORACLE_HOME/bin/dbstart in order to show PMON's PID:
	OS_PID=$(sqlplus -S / AS SYSDBA <<EOF
select SPID from v\$process where PNAME = 'PMON';
EOF
)
	OS_PID=$(echo "$OS_PID" | grep -P "\d+")
	echo "$0: ${INST} \"${ORACLE_SID}\" warm started (PID $OS_PID)."
	if [ -d /run/oracle ]; then
		echo "$OS_PID" > /run/oracle/oracle.pid
		echo "created /run/oracle/oracle.pid"
	fi
# or change like this:
	OS_PID=$(ps -e | grep [p]mon_orcl | awk '{print $1;}')
	echo "$0: ${INST} \"${ORACLE_SID}\" warm started (PID $OS_PID)."
	if [ -d /run/oracle ]; then
		echo "$OS_PID" > /run/oracle/oracle.pid
		echo "created /run/oracle/oracle.pid"
	fi
# in order to use dbstart and dbshut with systemd you'll have to also change them 
# so that awk, cat, cut, grep, touch, chmod commands 
# are used with their absolute path (e.g. /bin/cat)

# systemd oracle.service
[Unit]
Description=Oracle 12c
After=local-fs.target
Wants=local-fs.target
[Service]
Type=forking
User=oracle
Group=oinstall
RuntimeDirectory=oracle
PIDFile=/run/oracle/oracle.pid
Restart=on-failure
RestartSec=3
TimeoutSec=0
ExecStart=/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbstart /u01/app/oracle/product/12.1.0/dbhome_1
ExecStop=/u01/app/oracle/product/12.1.0/dbhome_1/bin/dbshut /u01/app/oracle/product/12.1.0/dbhome_1
[Install]
WantedBy=multi-user.target

# all users of the database visible to the current user
select username from all_users order by username;

# tables for schema/user
SELECT DISTINCT OBJECT_NAME FROM ALL_OBJECTS WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'HR';

# change user's password:
ALTER USER sys IDENTIFIED BY "Yxx32145";
ALTER USER system IDENTIFIED BY "Yxx32145";
sqlplus system/Yxx32145 AS SYSDBA

# Enterprise Manager
# http://www.dba-oracle.com/t_11g_connect_sysdba_ora_01031.htm
sys - allowed to connect AS SYSDBA
system - not allowed to connect AS SYSDBA

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.