Scripts – Maintaining Database

Shell scripts

Shellscripts.php

PREREQUISITE for the below exercise

Create as root user.

[root@linux1 gopi] #mkdir /u01
[root@linux1 gopi] #chown -R oracle:oinstall /u01

1. To start the database.

[oracle@linux1 gopi]$ cat startDB
export ORACLE_SID=$1
$ORACLE_HOME/bin/sqlplus /nolog << EOF connect / as sysdba startup EOF should be run like ./startDB prim where prim is the dbname

2. To stop the database

[oracle@linux1 gopi]$ cat shutDB
export ORACLE_SID=$1
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF connect / as sysdba shutdown immediate EOF should be run like ./shutDB prim where prim is the dbname

3. To drop the database.

[oracle@linux1 gopi]$ cat dropDB
export ORACLE_SID=$1
$ORACLE_HOME/bin/sqlplus -s /nolog << EOF connect / as sysdba shut abort startup mount exclusive restrict drop database; EOF should be run like ./dropDB prim where prim is the dbname

4. To create the database

should be run like

./createDB prim

where prim is the dbname
[oracle@linux1 gopi]$ cat createDB

export ORACLE_HOME=/u01/app/oracle/product/12.2.0
export ORACLE_SID=$1
mkdir -p /u01/app/oracle/oradata/${ORACLE_SID}
rm -rf $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
touch $ORACLE_HOME/dbs/init${ORACLE_SID}.ora
rm -rf /tmp/CreateDB.sql
rm -rf /u01/app/oracle/oradata/${ORACLE_SID}/*
touch /tmp/CreateDB.sql

$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=Master_123

echo “conn / as sysdba “>>/tmp/CreateDB.sql
echo “shut abort “>>/tmp/CreateDB.sql
echo “startup nomount “>>/tmp/CreateDB.sql
echo “CREATE DATABASE $1 “>>/tmp/CreateDB.sql
echo ” USER SYS IDENTIFIED BY Master_123 “>>/tmp/CreateDB.sql
echo ” USER SYSTEM IDENTIFIED BY Master_123 “>>/tmp/CreateDB.sql
echo ” LOGFILE GROUP 1 (‘/u01/app/oracle/oradata/$1/redo01.log’) SIZE 100M, “>>/tmp/CreateDB.sql
echo ” GROUP 2 (‘/u01/app/oracle/oradata/$1/redo02.log’) SIZE 100M, “>>/tmp/CreateDB.sql
echo ” GROUP 3 (‘/u01/app/oracle/oradata/$1/redo03.log’) SIZE 100M “>>/tmp/CreateDB.sql
echo ” CHARACTER SET US7ASCII “>>/tmp/CreateDB.sql
echo ” NATIONAL CHARACTER SET AL16UTF16 “>>/tmp/CreateDB.sql
echo ” EXTENT MANAGEMENT LOCAL “>>/tmp/CreateDB.sql
echo ” DATAFILE ‘/u01/app/oracle/oradata/$1/system01.dbf’ SIZE 400M REUSE “>>/tmp/CreateDB.sql
echo ” SYSAUX DATAFILE ‘/u01/app/oracle/oradata/$1/sysaux01.dbf’ SIZE 400M REUSE “>>/tmp/CreateDB.sql
echo ” DEFAULT TABLESPACE users “>>/tmp/CreateDB.sql
echo ” DATAFILE ‘/u01/app/oracle/oradata/$1/users01.dbf’ “>>/tmp/CreateDB.sql
echo ” SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED “>>/tmp/CreateDB.sql
echo ” DEFAULT TEMPORARY TABLESPACE temp “>>/tmp/CreateDB.sql
echo ” TEMPFILE ‘/u01/app/oracle/oradata/$1/temp01.dbf’ “>>/tmp/CreateDB.sql
echo ” SIZE 20M REUSE “>>/tmp/CreateDB.sql
echo ” UNDO TABLESPACE UNDOTBS1 “>>/tmp/CreateDB.sql
echo ” DATAFILE ‘/u01/app/oracle/oradata/$1/undotbs01.dbf’ “>>/tmp/CreateDB.sql
echo ” SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED; “>>/tmp/CreateDB.sql
echo ” @?/rdbms/admin/catalog.sql “>>/tmp/CreateDB.sql
echo ” @?/rdbms/admin/catproc.sql “>>/tmp/CreateDB.sql
echo ” shutdown immediate “>>/tmp/CreateDB.sql
echo ” startup mount “>>/tmp/CreateDB.sql
echo ” alter database archivelog; “>>/tmp/CreateDB.sql
echo ” alter database open; “>>/tmp/CreateDB.sql
echo ” create spfile from pfile; “>>/tmp/CreateDB.sql
echo ” shutdown immediate “>>/tmp/CreateDB.sql
echo ” startup “>>/tmp/CreateDB.sql
echo ” alter system switch logfile; “>>/tmp/CreateDB.sql

echo “sga_target=800m”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “compatible=’12.2.0′”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “control_files=’/u01/app/oracle/oradata/$1/control01.ctl’,’/u01/app/oracle/oradata/$1/control02.ctl'”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “db_domain=’localdomain'”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “db_name=’$1′”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “diagnostic_dest=’/u01/app/oracle'”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “log_archive_dest=’/u01/app/oracle/oradata/archive/'”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “undo_tablespace=’UNDOTBS1′”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “db_unique_name=’primarydb'”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora
echo “remote_login_passwordfile=’EXCLUSIVE'”>>$ORACLE_HOME/dbs/init$ORACLE_SID.ora

$ORACLE_HOME/bin/sqlplus -s /nolog << EOF @/tmp/CreateDB.sql exit; EOF