[oracle@mon-svlin005156 scripts]$ cat create_iTACMES-CDB-PDB_Linux_Jan2025.sh
#!/bin/bash

# Check if the correct number of arguments are provided
if [ "$#" -ne 2 ]; then
    echo "Usage: $0 <CDB_SID> <PDB_SID>"
    exit 1
fi

# Assign arguments to variables
CDB_SID=$1
PDB_SID=$2

# Set Oracle environment variables
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/21c/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

export ORA_HOME_DBS=$ORACLE_HOME/dbs
export ORA_PWD_FILE=$ORA_HOME_DBS/orapw$ORACLE_SID.ora

export PWD_SYS=WelcomeDBA1!
export PWD_SYSTEM=WelcomeDBA2!
export PWD_PDBADMIN=WelcomeDBA3!


# Define the log file path with timestamp
LOG_FILE="/u01/inst_stage/logs/dbca_install_${CDB_SID}_$(date +%Y%m%d_%H%M%S).log"

# Define the response file path
RESPONSE_FILE="/u01/inst_stage/scripts/dbca_response_${CDB_SID}.rsp"


# Define archive logs and redo logs destinations
ORA_ARCHIVE_DEST=/u02/oracle/fast_recovery_area/${CDB_SID}
ORA_REDO_DEST1=/u02/oracle/redologs/redoA
ORA_REDO_DEST2=/u02/oracle/redologs/redoB

# create adump, archive log and redo logs destinations
mkdir -p $ORACLE_BASE/admin/${CDB_SID}/adump >>$LOG_FILE 2>&1
mkdir -p $ORA_ARCHIVE_DEST >>$LOG_FILE 2>&1
mkdir -p $ORA_REDO_DEST1 >>$LOG_FILE 2>&1
mkdir -p $ORA_REDO_DEST2 >>$LOG_FILE 2>&1


# create orapwd
orapwd file=${ORA_PWD_FILE} password=${PWD_SYS} format=12 >>$LOG_FILE 2>&1

# create initialization parameter init<CDB_SID>.ora

cat <<EOF > $ORACLE_HOME/dbs/init${CDB_SID}.ora
db_name='${CDB_SID}'
sga_max_size=15G
sga_target=15G
pga_aggregate_limit=10G
pga_aggregate_target=5G
processes=1000
audit_file_dest='$ORACLE_BASE/admin/${CDB_SID}/adump'
audit_trail='none'
db_block_size=8192
db_domain=''
compatible='21.0.0'
db_create_file_dest='/u02/oracle/oradata'
db_create_online_log_dest_1='/u02/oracle/redologs/redoA'
db_create_online_log_dest_2='/u02/oracle/redologs/redoB'
db_recovery_file_dest='/u02/oracle/fast_recovery_area/${CDB_SID}'
db_recovery_file_dest_size=50G
diagnostic_dest='$ORACLE_BASE'
enable_pluggable_database=true
local_listener=''
log_archive_format='%t_%s_%r.arc'
log_archive_dest_1='LOCATION=/u02/oracle/fast_recovery_area/${CDB_SID}'
nls_language=AMERICAN
nls_territory=GERMANY
optimizer_index_caching=90
optimizer_index_cost_adj=25
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
EOF

# Create a response file for DBCA
cat <<EOF > $RESPONSE_FILE
[GENERAL]
RESPONSEFILE_VERSION = "21.0.0"
OPERATION_TYPE = "createDatabase"

[CREATEDATABASE]
GDBNAME = "${CDB_SID}"
SID = "${CDB_SID}"
TEMPLATENAME="New_Database.dbt"
CREATEASCONTAINERDATABASE = true
NUMBEROFPDBS = 1
PDBNAME = "${PDB_SID}"
PDBADMINPASSWORD = "${PWD_PDBADMIN}"
SYSPASSWORD = "${PWD_SYS}"
SYSTEMPASSWORD = "${PWD_SYSTEM}"
USEOMF= true
DATAFILEDESTINATION = "/u02/oracle/oradata"
RECOVERYAREADESTINATION = "/u02/oracle/fast_recovery_area"
RECOVERYAREASIZE=50GB
PDBOPTIONS=JSERVER:false,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,SAMPLE_SCHEMA:false,DV:false
DBOPTIONS=JSERVER:false,ORACLE_TEXT:false,IMEDIA:false,CWMLITE:false,SPATIAL:false,OMS:false,SAMPLE_SCHEMA:false,DV:false
STORAGETYPE = "FS"
CHARACTERSET = "AL32UTF8"
NATIONALCHARACTERSET = "AL16UTF16"
REGISTERWITHDIRSERVICE=false
SKIPLISTENERREGISTRATION=true
ENABLEARCHIVE=false
memoryPercentage=40
databaseType=MULTIPURPOSE
AUTOMATICMEMORYMANAGEMENT=false
TOTALMEMORY=0
EMCONFIGURATION = "NONE"
EOF

# Run DBCA with the response file
d"$ORACLE_HOME/bin/dbca" -silent -createDatabase -responseFile $RESPONSE_FILE  >> $LOG_FILE  2>&1


# Check if the database creation was successful
if [ $? -eq 0 ]; then
    echo "Database creation successful. Log file: $LOG_FILE"
else
    echo "Database creation failed. Check the log file: $LOG_FILE"
fi

# Start the listener
lsnrctl start

# Start the database and open the PDB
sqlplus / as sysdba <<EOF
ALTER PLUGGABLE DATABASE ${PDB_SID} OPEN;
EXIT;
EOF

echo "Oracle 21c database creation completed for CDB SID: ${CDB_SID} and PDB SID: ${PDB_SID}."
echo
echo "Now Installing MES/MOM Database Structure"
echo

# Set Oracle environment variables
# export ORACLE_HOME=/u01/app/oracle/product/21c/dbhome_1
# export ORACLE_SID=your_cdb_sid

export SQL_SCRIPT=/u01/inst_stage/scripts/installMES-MOM_Structure_Jan2025.sql

# Connect to the PDB and run the SQL script
# $ORACLE_HOME/bin/sqlplus /nolog <<EOF
# CONNECT sys/your_sys_password@//localhost:1521/your_cdb_sid AS SYSDBA

ALTER SESSION SET CONTAINER=${PDB_SID};
@${SQL_SCRIPT}
EXIT;
EOF

# Check the status of the SQL script execution
if [ $? -eq 0 ]; then
  echo "SQL script ${SQL_SCRIPT} executed successfully in PDB $PDB_ID."
else
  echo "Failed to execute SQL script ${SQL_SCRIPT} in PDB $PDB_ID."
fi
[oracle@mon-svlin005156 scripts]$