DBサーバ構築、Oracle 26ai（６）2ノードRAC構築 2

#!/bin/sh

################## prdb1.sh edit begin #####################
echo "-------------------- CAUTION !! ------------------------"
echo "This shell script is for building the prdb database."
echo "If resources related to the prdb database exist in the ASM disk group, this script deletes all of them before starting the database creation."
echo "(Running this script by mistake when the official database is already complete would be disastrous.)"
echo "--------------------------------------------------------"
echo ""
read -p "Are you sure you want to start the script? (Y/n):" ans
case "$ans" in [Y]*) ;; *) exit ;; esac
echo ""

# Execution User Check
if [ "$(whoami)" != "oracle" ]; then
    echo "This script must be executed as the oracle user."
    exit
fi

# Environment variable check
if [ -z "${ORACLE_HOME}" -o ! -d "${ORACLE_HOME}" ];then
    echo "ORACLE_HOME is not set correctly. Please set ORACLE_HOME before running the script."
    exit
fi

# Remote Node Process Check
if [ $(ssh prsdb02 "ps -ef | grep [o]ra_ | grep -c prdb") -gt 0 ];then
    echo "A database background process is running on the remote node."
    echo "Before executing the shell script, stop the database background processes as the oracle user."
    echo ""
    echo "command ex.) "
    echo "-------------------------------------"
    echo "srvctl stop database -db prdb"
    echo "exit"
    echo "-------------------------------------"
    echo ""
    exit
fi

# Local Node Process Check
if [ $( ps -ef | grep [o]ra_ | grep -c prdb ) -gt 0 ];then
    echo "A database background process is running on the local node."
    echo "Before executing the shell script, stop the database background processes as the oracle user."
    echo ""
    echo "command ex.) "
    echo "-------------------------------------"
    echo "export ORACLE_SID=prdb1;sqlplus / as sysdba"
    echo "shutdown abort"
    echo "exit"
    echo "-------------------------------------"
    echo ""
    exit
fi

# Cluster Resource Deletion
OLD_LANG=${LANG};export LANG=C

if [ $( ${ORACLE_HOME}/bin/srvctl status database -db prdb | grep -c "^Instance prdb" ) -gt 0 ];then
    srvctl remove database -db prdb -force
fi

export LANG=${OLD_LANG}

# PRDB Delete existing ASM directoryi
if [ $( ${ORACLE_HOME}/bin/asmcmd --privilege sysdba ls +DG11/ | grep -c PRDB ) -gt 0 ];then
    ${ORACLE_HOME}/bin/asmcmd --privilege sysdba rm -rf +DG11/PRDB
fi

if [ $( ${ORACLE_HOME}/bin/asmcmd --privilege sysdba ls +DG21/ | grep -c PRDB ) -gt 0 ];then
    ${ORACLE_HOME}/bin/asmcmd --privilege sysdba rm -rf +DG21/PRDB
fi

# Delete existing directory on the remote node
ssh prsdb02 "rm -Rf /opt/app/oracle/admin/prdb/"
ssh prsdb02 "rm -Rf /opt/app/oracle/diag/rdbms/prdb/"

# Delete existing directory on the local node
rm -Rf /opt/app/oracle/admin/prdb/[d,h,p,w]*
rm -Rf /opt/app/oracle/diag/rdbms/prdb/

# Restore init.ora
sed -i '/cluster_database=true/d' /opt/app/oracle/admin/prdb/scripts/init.ora

# Script backup
zip /home/oracle/create_prdb.zip ./*.*

################## prdb1.sh edit end #######################


DB_HOME=$ORACLE_HOME
ASM_HOME=/opt/app/26.0.0/grid
ORACLE_HOME=$ASM_HOME; export ORACLE_HOME
ORACLE_SID=+ASM1; export ORACLE_SID
PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
/opt/app/26.0.0/grid/bin/sqlplus /nolog @/opt/app/oracle/admin/prdb/scripts/mkDir.sql
ORACLE_HOME=$DB_HOME; export ORACLE_HOME
OLD_UMASK=`umask`
umask 0027
mkdir -p /opt/app/oracle
mkdir -p /opt/app/oracle/admin/prdb/dpdump
mkdir -p /opt/app/oracle/admin/prdb/hdump
mkdir -p /opt/app/oracle/admin/prdb/pfile
mkdir -p /opt/app/oracle/admin/prdb/scripts
mkdir -p /opt/app/oracle/audit
umask ${OLD_UMASK}

### prdb1.sh add begin ###
ssh prsdb02 "umask 0027;mkdir -p /opt/app/oracle/audit"
ssh prsdb02 "umask 0027;mkdir -p /opt/app/oracle/admin"
scp -rp /opt/app/oracle/admin/prdb prsdb02:/opt/app/oracle/admin/
### prdb1.sh add end   ###

PERL5LIB=$ORACLE_HOME/rdbms/admin:$PERL5LIB; export PERL5LIB
ORACLE_SID=prdb1; export ORACLE_SID
PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH; export PATH
/opt/app/oracle/product/26.0.0/dbhome_1/bin/sqlplus /nolog @/opt/app/oracle/admin/prdb/scripts/prdb1.sql

SET VERIFY OFF
connect / as SYSDBA;
-- mkDir.sql add begin
alter diskgroup DG11 add directory '+DG11/PRDB';
alter diskgroup DG11 add directory '+DG11/PRDB/prpdb';
-- mkDir.sql add end
alter diskgroup DG11 add directory '+DG11/PRDB/ctl';
alter diskgroup DG11 add directory '+DG11/PRDB/log';
alter diskgroup DG11 add directory '+DG11/PRDB/tmp';
alter diskgroup DG21 add directory '+DG21/PRDB';
alter diskgroup DG21 add directory '+DG21/PRDB/dbf';
alter diskgroup DG21 add directory '+DG21/PRDB/pdbseed';
alter diskgroup DG21 add directory '+DG21/PRDB/prpdb';
alter diskgroup DG21 add directory '+DG21/PRDB/rbs';
exit;

set verify off

-- prdb1.sql edit begin
define sysPassword = 'oracle';
define systemPassword = '&&sysPassword';
define dbsnmpPassword = '&&sysPassword';
define pdbAdminPassword = '&&sysPassword';
-- prdb1.sql edit end

host /opt/app/oracle/product/26.0.0/dbhome_1/bin/srvctl add database -d prdb -pwfile +DG11/PRDB/orapwprdb -o /opt/app/oracle/product/26.0.0/dbhome_1 -n prdb -a "DG11,DG21"
host /opt/app/oracle/product/26.0.0/dbhome_1/bin/srvctl add instance -d prdb -i prdb1 -n prsdb01
host /opt/app/oracle/product/26.0.0/dbhome_1/bin/srvctl add instance -d prdb -i prdb2 -n prsdb02
host /opt/app/oracle/product/26.0.0/dbhome_1/bin/srvctl disable database -d prdb
host  /opt/app/oracle/product/26.0.0/dbhome_1/bin/orapwd file=+DG11/PRDB/orapwprdb force=y format=12 dbuniquename=prdb password=&&sysPassword

@/opt/app/oracle/admin/prdb/scripts/CreateDB.sql
@/opt/app/oracle/admin/prdb/scripts/CreateDBFiles.sql
@/opt/app/oracle/admin/prdb/scripts/CreateDBCatalog.sql
@/opt/app/oracle/admin/prdb/scripts/CreateClustDBViews.sql
@/opt/app/oracle/admin/prdb/scripts/lockAccount.sql
@/opt/app/oracle/admin/prdb/scripts/postDBCreation.sql
@/opt/app/oracle/admin/prdb/scripts/PDBCreation.sql
@/opt/app/oracle/admin/prdb/scripts/plug_prpdb.sql
@/opt/app/oracle/admin/prdb/scripts/postPDBCreation_prpdb.sql

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /opt/app/oracle/admin/prdb/scripts/CreateDB.log append
startup nomount pfile="/opt/app/oracle/admin/prdb/scripts/init.ora";
CREATE DATABASE "prdb"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '+DG21/PRDB/dbf/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '+DG21/PRDB/dbf/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DG11/PRDB/tmp/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE  '+DG21/PRDB/rbs/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE GROUP 11 ('+DG11/PRDB/log/redo11_1.log','+DG11/PRDB/log/redo11_2.log') SIZE 256M,
GROUP 12 ('+DG11/PRDB/log/redo12_1.log','+DG11/PRDB/log/redo12_2.log') SIZE 256M,
GROUP 13 ('+DG11/PRDB/log/redo13_1.log','+DG11/PRDB/log/redo13_2.log') SIZE 256M
USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM IDENTIFIED BY "&&systemPassword"
enable pluggable database
seed file_name_convert=('+DG21/PRDB/dbf/system01.dbf','+DG21/PRDB/pdbseed/system01.dbf','+DG21/PRDB/dbf/sysaux01.dbf','+DG21/PRDB/pdbseed/sysaux01.dbf','+DG11/PRDB/tmp/temp01.dbf','+DG21/PRDB/pdbseed/temp01.dbf','+DG21/PRDB/rbs/undotbs01.dbf','+DG21/PRDB/pdbseed/undotbs01.dbf') LOCAL UNDO ON;
spool off

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /opt/app/oracle/admin/prdb/scripts/CreateDBFiles.log append
CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE  '+DG21/PRDB/rbs/undotbs02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED;
CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '+DG21/PRDB/dbf/users01.dbf' SIZE 16M REUSE AUTOEXTEND ON NEXT  4M MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;
ALTER DATABASE DEFAULT TABLESPACE "USERS";

-- CreateDBFiles.sql add begin
ALTER DATABASE DATAFILE '+DG21/PRDB/dbf/sysaux01.dbf' RESIZE 2048M;
ALTER DATABASE DATAFILE '+DG21/PRDB/dbf/system01.dbf' RESIZE 1024M;
ALTER DATABASE DATAFILE '+DG21/PRDB/rbs/undotbs01.dbf' RESIZE 1024M;
ALTER DATABASE TEMPFILE '+DG11/PRDB/tmp/temp01.dbf' RESIZE 2048M;
-- CreateDBFiles.sql add end

spool off

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
alter user DBSNMP identified by "&&dbsnmpPassword" account unlock;
spool /opt/app/oracle/admin/prdb/scripts/postDBCreation.log append
host /opt/app/oracle/product/26.0.0/dbhome_1/OPatch/datapatch -skip_upgrade_check -db prdb1;
select group# from v$log where group# =21;
select group# from v$log where group# =22;
select group# from v$log where group# =23;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 21 ('+DG11/PRDB/log/redo21_1.log','+DG11/PRDB/log/redo21_2.log') SIZE 256M,
 GROUP 22 ('+DG11/PRDB/log/redo22_1.log','+DG11/PRDB/log/redo22_2.log') SIZE 256M,
 GROUP 23 ('+DG11/PRDB/log/redo23_1.log','+DG11/PRDB/log/redo23_2.log') SIZE 256M;
ALTER DATABASE ENABLE PUBLIC THREAD 2;
host echo cluster_database=true >>/opt/app/oracle/admin/prdb/scripts/init.ora;
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
create spfile='+DG11' FROM pfile='/opt/app/oracle/admin/prdb/scripts/init.ora';
connect "SYS"/"&&sysPassword" as SYSDBA
host /opt/app/oracle/product/26.0.0/dbhome_1/perl/bin/perl  /opt/app/oracle/product/26.0.0/dbhome_1/rdbms/admin/catcon.pl  -n  1  -l  /opt/app/oracle/admin/prdb/scripts  -v   -b  utlrp   -U  "SYS"/"&&sysPassword"  /opt/app/oracle/product/26.0.0/dbhome_1/rdbms/admin/utlrp.sql;
select comp_id, status from dba_registry;
shutdown immediate;
host /opt/app/oracle/product/26.0.0/dbhome_1/bin/srvctl enable database -d prdb;
host /opt/app/oracle/product/26.0.0/dbhome_1/bin/srvctl start database -d prdb;
connect "SYS"/"&&sysPassword" as SYSDBA
spool off
select instance from v$thread where instance like 'UNNAMED_INSTANCE%';

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
set echo on
spool /opt/app/oracle/admin/prdb/scripts/plugDatabase.log append
spool /opt/app/oracle/admin/prdb/scripts/plugDatabase.log append
select d.name||'|'||t.name from v$datafile d,V$TABLESPACE t where d.con_id=2 and d.ts#=t.ts# and d.con_id=t.con_id;
select d.name||'|'||t.name from v$tempfile d,V$TABLESPACE t where d.con_id=2 and d.ts#=t.ts# and d.con_id=t.con_id;
CREATE PLUGGABLE DATABASE "prpdb" ADMIN USER PDBADMIN IDENTIFIED BY "&&pdbadminPassword" ROLES=(CONNECT)  PARALLEL  file_name_convert=('+DG21/PRDB/pdbseed',
'+DG21/PRDB/prpdb')  STORAGE INHERIT;
select name from v$containers where upper(name) = 'PRPDB';

-- plug_prpdb.sql add begin
ALTER PLUGGABLE DATABASE "prpdb" open;
ALTER SESSION SET CONTAINER = "prpdb";
ALTER DATABASE DATAFILE '+DG21/PRDB/prpdb/sysaux01.dbf'  RESIZE 2048M;
ALTER DATABASE DATAFILE '+DG21/PRDB/prpdb/system01.dbf'  RESIZE 1024M;
ALTER DATABASE DATAFILE '+DG21/PRDB/prpdb/undotbs01.dbf' RESIZE 1024M;

CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE  '+DG21/PRDB/prpdb/undotbs02.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED;

ALTER SYSTEM SET undo_tablespace = 'UNDOTBS1' SID = 'prdb1';
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SID = 'prdb2';

ALTER SESSION SET CONTAINER = CDB$ROOT;
ALTER PLUGGABLE DATABASE "prpdb" close;
-- plug_prpdb.sql add end

alter pluggable database "prpdb" open  instances=all;
alter system register;
ALTER SESSION SET CONTAINER = "prpdb";
select con_id from v$pdbs where con_id > 1 and upper(name)=upper('prpdb') ;
SELECT bigfile FROM sys.cdb_tablespaces WHERE tablespace_name='TEMP' AND CON_ID=0;
CREATE SMALLFILE TEMPORARY TABLESPACE TEMP_NON_ENC TEMPFILE '+DG21/PRDB/prpdb/temp01_NON_ENC.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT  64M MAXSIZE UNLIMITED;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP_NON_ENC";
alter session set container=cdb$root;
ALTER PLUGGABLE DATABASE "prpdb" CLOSE IMMEDIATE  instances=all;
alter pluggable database "prpdb" open  instances=all;
alter system register;
ALTER SESSION SET CONTAINER = "prpdb";
drop tablespace TEMP including contents and datafiles;

-- plug_prpdb.sql edit begin
CREATE SMALLFILE TEMPORARY TABLESPACE TEMP TEMPFILE '+DG11/PRDB/prpdb/temp01.dbf' SIZE 2048M REUSE AUTOEXTEND ON NEXT  16M MAXSIZE UNLIMITED;
-- plug_prpdb.sql edit end

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE "TEMP";
alter session set container=cdb$root;
ALTER PLUGGABLE DATABASE "prpdb" CLOSE IMMEDIATE  instances=all;
alter pluggable database "prpdb" open  instances=all;
alter system register;
ALTER SESSION SET CONTAINER = "prpdb";
drop tablespace TEMP_NON_ENC including contents and datafiles;

SET VERIFY OFF
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="prpdb";
set echo on
spool /opt/app/oracle/admin/prdb/scripts/postPDBCreation.log append

-- postPDBCreation_prpdb.sql begin
CREATE SMALLFILE TABLESPACE "USERS" LOGGING  DATAFILE  '+DG21/PRDB/prpdb/users01.dbf' SIZE 16M REUSE AUTOEXTEND ON NEXT  4M MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;

CREATE SMALLFILE TABLESPACE USERTBL01 LOGGING DATAFILE '+DG21/PRDB/prpdb/usertbl01.dbf' SIZE 10G REUSE AUTOEXTEND ON NEXT 16M MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;

CREATE SMALLFILE TABLESPACE USERIDX01 LOGGING DATAFILE '+DG21/PRDB/prpdb/useridx01.dbf' SIZE 5G AUTOEXTEND ON NEXT 16M  MAXSIZE UNLIMITED  EXTENT MANAGEMENT LOCAL  SEGMENT SPACE MANAGEMENT  AUTO;
-- postPDBCreation_prpdb.sql edit end

ALTER DATABASE DEFAULT TABLESPACE "USERS";
host /opt/app/oracle/product/26.0.0/dbhome_1/OPatch/datapatch -skip_upgrade_check -db prdb1 -pdbs prpdb;
connect "SYS"/"&&sysPassword" as SYSDBA
select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="prpdb";
set echo on
spool /opt/app/oracle/admin/prdb/scripts/postPDBCreation.log append
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="prpdb";
set echo on
spool /opt/app/oracle/admin/prdb/scripts/postPDBCreation.log append
select TABLESPACE_NAME from cdb_tablespaces a,dba_pdbs b where a.con_id=b.con_id and UPPER(b.pdb_name)=UPPER('prpdb');
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="prpdb";
set echo on
spool /opt/app/oracle/admin/prdb/scripts/postPDBCreation.log append
select TABLESPACE_NAME from cdb_tablespaces a,dba_pdbs b where a.con_id=b.con_id and UPPER(b.pdb_name)=UPPER('prpdb');
connect "SYS"/"&&sysPassword" as SYSDBA
alter session set container="prpdb";
set echo on
spool /opt/app/oracle/admin/prdb/scripts/postPDBCreation.log append
Select count(*) from dba_registry where comp_id = 'DV' and status='VALID';
show con_name;
alter session set container="prpdb";
select count(a.username) from cdb_users a, v$pdbs b where a.con_id=b.con_id and a.username=upper('PDBADMIN') and upper(b.name)=upper('prpdb');
show con_name;
alter session set container="CDB$ROOT";
alter session set container=CDB$ROOT;

-- postPDBCreation_prpdb.sql begin
spool /opt/app/oracle/admin/prdb/scripts/DBINFO.log append
set lines 120 pages 9999 trimspool on

-- CDB info
show con_name;

-- control file info
col NAME format a40
select NAME from V$CONTROLFILE;

-- spfile info
col SPFILE format a60
select VALUE SPFILE from V$PARAMETER
where NAME = 'spfile';

-- password file info
col FILE_NAME format a40
select FILE_NAME from V$PASSWORDFILE_INFO;

-- redo log info
col STATUS format a10
col STATUS format a10
col SIZE_MB format 99999
col MEMBER format a30

select a.GROUP#,a.THREAD#,a.SEQUENCE#,a.STATUS
    ,a.BYTES / 1024 / 1024 SIZE_MB,b.MEMBER
from V$LOG a,V$LOGFILE b
where a.GROUP# = b.GROUP#
order by THREAD#,GROUP#,MEMBER;

-- datafile info
col TS_NAME format a10
col DF_NAME format a40
col CID format 999
col NEXT_MB  format 9999

select
    TABLESPACE_NAME TS_NAME,FILE_NAME DF_NAME,
    BYTES / 1024 / 1024 SIZE_MB,
    INCREMENT_BY * 8 / 1024 NEXT_MB
from DBA_DATA_FILES
union
select
    TABLESPACE_NAME TS_NAME,FILE_NAME DF_NAME,
    BYTES / 1024 / 1024 SIZE_MB,
    INCREMENT_BY * 8 / 1024 NEXT_MB
from DBA_TEMP_FILES
order by 1;


alter session set container = prpdb;

-- PDB info
show con_name;

-- undo parameter info (from memory)
col NAME format a16
col VALUE format a30
select INST_ID,NAME,VALUE from GV$PARAMETER
where NAME = 'undo_tablespace'
order by INST_ID;

-- undo parameter info (from spfile)
col SID format a10
select INST_ID,SID,NAME,VALUE
from GV$SPPARAMETER
where NAME = 'undo_tablespace'
order by INST_ID;

-- datafile info
select
    TABLESPACE_NAME TS_NAME,FILE_NAME DF_NAME,
    BYTES / 1024 / 1024 SIZE_MB,
    INCREMENT_BY * 8 / 1024 NEXT_MB
from DBA_DATA_FILES
union
select
    TABLESPACE_NAME TS_NAME,FILE_NAME DF_NAME,
    BYTES / 1024 / 1024 SIZE_MB,
    INCREMENT_BY * 8 / 1024 NEXT_MB
from DBA_TEMP_FILES
order by 1;

-- postPDBCreation_prpdb.sql end

exit;