Here is the full script which will get few input and build the oracle database and configure the listener,tnsnames.ora.pfile/spfile and convert the database to archive log
#########################################################################################################
#purpose : Script used to build oracle DB,pass requested info #
#Input needed :DB_NAME,ORACLE_HOME,PORT#,DB CHARSET,sys,system,PDB pws,Redlog size in MB #
#script will do: create DB,convert archive mode,setup listener& tns and start,add /etc/oratab,BCT #
#Developed by :Bala P 3March2025 v2 #
#########################################################################################################
#!/bin/sh
# Prompt for database name and convert to uppercase
echo "Enter the database name:"
read DB_NAME
DB_NAME=$(echo $DB_NAME | tr '[:lower:]' '[:upper:]')
# Prompt the user for port number
read -p "Enter the port number: " PORT_NUMBER
# Prompt the user for Redolog size in MB
read -p "Enter the Redolog size in MB: " RLOG
# Prompt for Oracle DB Home
echo "Enter the Oracle DB Home full Path:"
read ORACLE_HOME
# Check if sqlplus /oracle_home status
if [ ! -x "$ORACLE_HOME/bin/sqlplus" ]; then
echo " sqlplus not found at $ORACLE_HOME/bin. Please check ORACLE_HOME."
exit 1
else
echo "Oracle_Home FOUND ,proceeding with next steps"
fi
echo -e "\n \n"
#Prompt for DB CHARACTER SET
echo "Enter the DB CHARACTER SET"
read charset
# Prompt for passwords
echo "Enter new password for SYS:"
read -s sysPassword
echo "Enter new password for SYSTEM:"
read -s systemPassword
echo "Enter new password for PDBADMIN:"
read -s pdbAdminPassword
export ORACLE_HOME=$ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=$DB_NAME
# Create necessary directories
OLD_UMASK=`umask`
umask 0027
mkdir -p /oracle/${DB_NAME}
mkdir -p $ORACLE_HOME/dbs
mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/adump
mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/dpdump
mkdir -p /oracle/${DB_NAME}/admin/${DB_NAME}/pfile
mkdir -p /oracle/${DB_NAME}/audit
mkdir -p /oracle/${DB_NAME}/cfgtoollogs/dbca/${DB_NAME}
mkdir -p /oracle/${DB_NAME}/control1
mkdir -p /oracle/${DB_NAME}/control2
mkdir -p /oracle/${DB_NAME}/control3
mkdir -p /oracle/${DB_NAME}/mirrlogA
mkdir -p /oracle/${DB_NAME}/mirrlogB
mkdir -p /oracle/${DB_NAME}/origlogA
mkdir -p /oracle/${DB_NAME}/origlogB
mkdir -p /oracle/${DB_NAME}/data1
mkdir -p /oracle/${DB_NAME}/SystemTBS
mkdir -p /oracle/${DB_NAME}/UndoTBS
mkdir -p /oracle/${DB_NAME}/tempTBS
mkdir -p /oracle/${DB_NAME}/admin/scripts
umask ${OLD_UMASK}
# Create init<DB_NAME>.ora file
rm -f /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora
cat <<EOL > /oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora
${DB_NAME}.__oracle_base='/oracle/${DB_NAME}'#ORACLE_BASE set from environment
${DB_NAME}.__db_cache_size=1451700
${DB_NAME}.__java_pool_size=16686
${DB_NAME}.__large_pool_size=16686
${DB_NAME}.__sga_target=1067918334
${DB_NAME}.__shared_io_pool_size=216920
${DB_NAME}.__shared_pool_size=2552992
${DB_NAME}.__data_transfer_cache_size=0
${DB_NAME}.__streams_pool_size=0
*.audit_file_dest='/oracle/${DB_NAME}/admin/${DB_NAME}/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_file_record_keep_time=30
*.control_files='/oracle/${DB_NAME}/control1/control01.ctl','/oracle/${DB_NAME}/control2/control02.ctl','/oracle/${DB_NAME}/control3/control03.ctl'
*.db_block_size=8192
*.db_domain='abc.domain.com'
*.db_files=200
*.db_name='${DB_NAME}'
*.diagnostic_dest='/oracle/${DB_NAME}'
*.filesystemio_options='SETALL'
*.log_archive_dest='/oracle/${DB_NAME}/oraarch/${DB_NAME}arch'
*.log_archive_format='%t_%s_%r.arc'
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20000'
*.open_cursors=3000
*.parallel_execution_message_size=16384
*.parallel_max_servers=20
*.parallel_threads_per_cpu=1
*.pga_aggregate_target=675m
*.processes=500
*.replication_dependency_tracking=FALSE
*.sessions=1000
*.sga_target=1g
*.undo_management='AUTO'
*.undo_retention=21600
*.undo_tablespace='UNDOTBS'
_disable_directory_link_check=TRUE
CONTROL_MANAGEMENT_PACK_ACCESS = "NONE"
EOL
# Create Oracle password file
rm -f $ORACLE_HOME/dbs/orapw${DB_NAME}
$ORACLE_HOME/bin/orapwd file=$ORACLE_HOME/dbs/orapw${DB_NAME} force=y format=12
# Run SQL scripts for DB creation and configuration
export ORACLE_HOME=$ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=$DB_NAME
echo -e "\n \n creating the DB...."
sqlplus /nolog <<EOF
CONNECT SYS/${sysPassword} AS SYSDBA
conn / as sysdba
SET VERIFY OFF
SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDB.log
STARTUP NOMOUNT PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';
CREATE DATABASE "${DB_NAME}"
MAXINSTANCES 8
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1000
DATAFILE '/oracle/${DB_NAME}/SystemTBS/system01.dbf' SIZE 2072M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/oracle/${DB_NAME}/SystemTBS/sysaux01.dbf' SIZE 2192M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/oracle/${DB_NAME}/tempTBS/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS" DATAFILE '/oracle/${DB_NAME}/UndoTBS/undotbs01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET "${charset}"
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
GROUP 1(
'/oracle/${DB_NAME}/origlogA/redo_g1m1.log',
'/oracle/${DB_NAME}/mirrlogA/redo_g1m2.log'
) SIZE ${RLOG}M BLOCKSIZE 512,
GROUP 2 (
'/oracle/${DB_NAME}/origlogB/redo_g2m1.log',
'/oracle/${DB_NAME}/mirrlogB/redo_g2m2.log'
) SIZE ${RLOG}M BLOCKSIZE 512,
GROUP 3 (
'/oracle/${DB_NAME}/origlogB/redo_g3m1.log',
'/oracle/${DB_NAME}/mirrlogB/redo_g3m2.log'
) SIZE ${RLOG}M BLOCKSIZE 512,
GROUP 4 (
'/oracle/${DB_NAME}/origlogA/redo_g4m1.log',
'/oracle/${DB_NAME}/mirrlogA/redo_g4m2.log'
) SIZE ${RLOG}M BLOCKSIZE 512
USER SYS IDENTIFIED BY "${sysPassword}" USER SYSTEM IDENTIFIED BY "${systemPassword}";
SPOOL OFF
----creating the needed tablespaces--------
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBFiles.log
CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/oracle/${DB_NAME}/data1/users01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";
SPOOL OFF
------loading oracle binaries--------------
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/CreateDBCatalog.log
@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
@$ORACLE_HOME/rdbms/admin/owminst.plb;
CONNECT SYSTEM/${systemPassword}
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
CONNECT SYS/${sysPassword} AS SYSDBA
@$ORACLE_HOME/sqlplus/admin/pupdel.sql;
CONNECT SYSTEM/${systemPassword};
SPOOL /oracle/${DB_NAME}/admin/scripts/sqlPlusHelp.log
@$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/JServer.log
@$ORACLE_HOME/javavm/install/initjvm.sql;
@$ORACLE_HOME/xdk/admin/initxml.sql;
@$ORACLE_HOME/xdk/admin/xmlja.sql;
@$ORACLE_HOME/rdbms/admin/catjava.sql;
@$ORACLE_HOME/rdbms/admin/catxdbj.sql;
SPOOL OFF
---checked above this
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/context.log
@$ORACLE_HOME/ctx/admin/catctx.sql Xbkfsdcdf1ggh_123 SYSAUX TEMP LOCK; --no path
ALTER USER CTXSYS ACCOUNT UNLOCK IDENTIFIED BY "CTXSYS";
CONNECT CTXSYS/CTXSYS
@$ORACLE_HOME/ctx/admin/defaults/dr0defin.sql "AMERICAN";
CONNECT SYS/${sysPassword} AS SYSDBA
conn / as sysdba
ALTER USER CTXSYS PASSWORD EXPIRE ACCOUNT LOCK;
@$ORACLE_HOME/rdbms/admin/dbmsxdbt.sql;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/ordinst.log
@$ORACLE_HOME/ord/admin/ordinst.sql SYSAUX SYSAUX;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/interMedia.log
@$ORACLE_HOME/ord/im/admin/iminst.sql;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/cwmlite.log
@$ORACLE_HOME/olap/admin/olap.sql SYSAUX TEMP;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/spatial.log
@$ORACLE_HOME/md/admin/mdinst.sql;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/apex.log APPEND
@$ORACLE_HOME/apex/catapx.sql Xbkfsdcdf1ggh_123 SYSAUX SYSAUX TEMP /i/ NONE;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/postDBCreation.log
host $ORACLE_HOME/OPatch/datapatch -skip_upgrade_check -db ${DB_NAME};
CREATE SPFILE='$ORACLE_HOME/dbs/spfile${DB_NAME}.ora' FROM PFILE='/oracle/${DB_NAME}/admin/scripts/init${DB_NAME}.ora';
SELECT 'utlrp_begin: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
@$ORACLE_HOME/rdbms/admin/utlrp.sql;
SELECT 'utlrp_end: ' || TO_CHAR(SYSDATE, 'HH:MI:SS') FROM DUAL;
SELECT comp_id, status FROM dba_registry;
SHUTDOWN IMMEDIATE;
STARTUP;
SPOOL OFF
conn / as sysdba
SPOOL /oracle/${DB_NAME}/admin/scripts/lockAccount.log
BEGIN
FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
'SYS','SYSTEM') )
LOOP
IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
DBMS_OUTPUT.PUT_LINE('Locking and Expiring: ' || item.USERNAME);
EXECUTE IMMEDIATE 'ALTER USER ' ||
SYS.DBMS_ASSERT.ENQUOTE_NAME(
SYS.DBMS_ASSERT.SCHEMA_NAME(
item.USERNAME),FALSE) || ' PASSWORD EXPIRE ACCOUNT LOCK' ;
ELSE
DBMS_OUTPUT.PUT_LINE('Locking: ' || item.USERNAME);
EXECUTE IMMEDIATE 'ALTER USER ' ||
SYS.DBMS_ASSERT.ENQUOTE_NAME(
SYS.DBMS_ASSERT.SCHEMA_NAME(
item.USERNAME),FALSE) || ' ACCOUNT LOCK' ;
END IF;
END LOOP;
END;
/
SPOOL OFF
EOF
# Set ORACLE_HOME
export ORACLE_HOME=$ORACLE_HOME
# Get the current hostname
SERVER_NAME=$(hostname)
# Define paths
TNS_PATH="$ORACLE_HOME/network/admin/tnsnames.ora"
LISTENER_PATH="$ORACLE_HOME/network/admin/listener.ora"
TNS_BACKUP_PATH="${TNS_PATH}_backup_$(date +%Y%m%d_%H%M%S)"
LISTENER_BACKUP_PATH="${LISTENER_PATH}_backup_$(date +%Y%m%d_%H%M%S)"
# Backup tnsnames.ora
if [ -f "$TNS_PATH" ]; then
cp "$TNS_PATH" "$TNS_BACKUP_PATH"
echo "Backup of tnsnames.ora created at $TNS_BACKUP_PATH"
fi
# Append to tnsnames.ora
cat <<EOF >> "$TNS_PATH"
${DB_NAME}.WORLD=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = ${SERVER_NAME})
(PORT = ${PORT_NUMBER})
)
)
(CONNECT_DATA =
(SID = ${DB_NAME})
(GLOBAL_NAME = ${DB_NAME}.WORLD)
)
)
#for PROD
RMANCATPRD.WORLD=
(DESCRIPTION =
(SDU = 32768)
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = <catlog_server>)
(PORT = 1591)
)
)
(CONNECT_DATA =
(SID = KRMANCATPRD)
(GLOBAL_NAME = RMANCATPRD.WORLD)
)
)
#prod end
EOF
echo "New TNS entry for ${DB_NAME} added to $TNS_PATH"
# Backup listener.ora
if [ -f "$LISTENER_PATH" ]; then
cp "$LISTENER_PATH" "$LISTENER_BACKUP_PATH"
echo "Backup of listener.ora created at $LISTENER_BACKUP_PATH"
fi
# Create listener.ora
cat <<EOF > "$LISTENER_PATH"
LISTENER_${DB_NAME} =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = IPC)
(KEY = ${DB_NAME}.WORLD)
)
(ADDRESS =
(PROTOCOL = IPC)
(KEY = ${DB_NAME})
)
(ADDRESS =
(PROTOCOL = TCP)
(HOST = ${SERVER_NAME})
(PORT = ${PORT_NUMBER})
)
)
STARTUP_WAIT_TIME_LISTENER_${DB_NAME} = 0
CONNECT_TIMEOUT_LISTENER_${DB_NAME} = 10
TRACE_LEVEL_LISTENER_${DB_NAME} = OFF
SID_LIST_LISTENER_${DB_NAME} =
(SID_LIST =
(SID_DESC =
(SID_NAME = ${DB_NAME})
(ORACLE_HOME = $ORACLE_HOME)
)
)
EOF
echo "New listener entry for ${DB_NAME} added to $LISTENER_PATH"
# Define paths for sqlnet.ora
SQLNET_PATH="$ORACLE_HOME/network/admin/sqlnet.ora"
SQLNET_BACKUP_PATH="${SQLNET_PATH}_backup_$(date +%Y%m%d_%H%M%S)"
# Backup sqlnet.ora
if [ -f "$SQLNET_PATH" ]; then
cp "$SQLNET_PATH" "$SQLNET_BACKUP_PATH"
echo "Backup of sqlnet.ora created at $SQLNET_BACKUP_PATH"
fi
# Append to sqlnet.ora
cat <<EOF >> "$SQLNET_PATH"
AUTOMATIC_IPC = ON
TRACE_LEVEL_CLIENT = OFF
NAMES.DEFAULT_DOMAIN = WORLD
NAME.DEFAULT_ZONE = WORLD
SQLNET.EXPIRE_TIME = 0
TCP.NODELAY=YES
EOF
#start the listener
export ORACLE_HOME=$ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=$DB_NAME
lsnrctl start LISTENER_${DB_NAME}
TNSPING_LOG="tnsping_${DB_NAME}.log"
# Run tnsping and log the output
tnsping ${DB_NAME}.WORLD > "$TNSPING_LOG" 2>&1
# Check tnsping result
if grep -q "OK" "$TNSPING_LOG"; then
echo "TNSPING successful: This is good"
else
echo "TNSPING failed: Please check the configuration"
fi
# Check logs for errors or warnings
LOG_FILES="/oracle/${DB_NAME}/admin/scripts/*.log"
OUTPUT_FILE="/oracle/${DB_NAME}/admin/scripts/error_check.log"
ERRORS_FOUND=false
> "$OUTPUT_FILE" # Clear previous output
for file in $LOG_FILES
do
if grep -iq "ORA-" "$file" ; then
ERRORS_FOUND=true
ERR_FILE="${file}_err"
# Extract relevant lines and save to .log_err file
grep -iE "ORA-" "$file" > "$ERR_FILE"
echo "Issues found in: $(basename "$file") $(basename "$ERR_FILE")" >> "$OUTPUT_FILE"
fi
done
if [ "$ERRORS_FOUND" = false ]; then
echo "Everything is good with Db creation." > "$OUTPUT_FILE"
fi
#Backup /etc/oratab with timestamp
timestamp=$(date +%Y%m%d_%H%M%S)
cp /etc/oratab /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp
# Append entry to /etc/oratab
echo "${DB_NAME}:${ORACLE_HOME}:Y" >> /etc/oratab
echo "Entry added to /etc/oratab and backup created as /oracle/$DB_NAME/admin/scripts/oratab_bak_$timestamp"
# Set environment variables arch mode conversion
export ORACLE_HOME=$ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=$DB_NAME
# Check if DB is already in ARCHIVELOG mode
archive_mode=$(sqlplus -s / as sysdba <<'EOF'
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SELECT log_mode FROM v$database;
EXIT
EOF
)
archive_mode=$(echo "$archive_mode" | xargs) # Trim whitespace
if [ "$archive_mode" == "ARCHIVELOG" ]; then
echo "Database is already in ARCHIVELOG mode. Skipping conversion."
else
echo "Converting database to ARCHIVELOG mode..."
sqlplus / as sysdba <<EOF
SPOOL convert_db_archive_mode_bct.log
SHOW PARAMETER spfile;
CREATE PFILE='$ORACLE_HOME/pfile_before_arch.ora' FROM SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ARCHIVE LOG LIST;
alter database enable block change tracking using file '/oracle/${DB_NAME}/SystemTBS/${DB_NAME}_block_change.dbf';
SPOOL OFF
EXIT
EOF
fi
echo "exporting Db variables final check"
export ORACLE_HOME=$ORACLE_HOME
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=$DB_NAME
EMAIL="BALAmani@abc.com"
# Run SQL to get DB name and open mode
DB_STATUS=$(sqlplus -s / as sysdba <<'EOF'
SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
SELECT open_mode FROM v$database;
EXIT;
EOF
)
# Check if SQL*Plus ran successfully
if [[ $? -ne 0 ]]; then
echo "SQL*Plus execution failed."
exit 1
fi
# Trim whitespace
DB_STATUS=$(echo "$DB_STATUS" | xargs)
# Log the status
echo "Database status returned: $DB_STATUS"
# Check if status is "READ WRITE"
if [[ "$DB_STATUS" == "READ WRITE" ]]; then
echo "DB creation completed successfully." | mailx -s "DB $DB_NAME build completed, proceed with next steps" "$EMAIL"
else
echo "Database is not in READ WRITE mode. Current status: $DB_STATUS"
exit 1
fi
exit 0
