Cài Oracle database 21c bằng SQL script không có gì mới cả
Từ Oracle 11g đã có cài từ sql script rồi
Các kĩ sư Oracle đã viết sql script cho quá trình cài đặt Oracle database có điều ít người để ý chuyện này
Như chúng tôi đã nói
Hiểu rõ tất cả những thứ trong quá trình cài đặt Oracle Database bạn sẽ trở thành Oracle Database Architecture, Oracle Database Solution, Oracle Database Engineer chứ không phải là Oracle Database Administrator hay Oracle DBA
Các bạn cần đọc hiểu các sql script này để tăng kiến thức, kĩ năng làm Oracle DBA và hơn thế nữa
Chi tiết kĩ thuật và cách làm sẽ nằm trong phần Oracle Challenge LAB
Các bạn mua khóa học này để học nhanh biết nhanh, tiết kiệm thời gian, tiền bạc, sức khỏe
Trên mạng có nhiều tài liệu về Oracle database nhưng làm theo thì hổng chạy đâu vì họ ghi vắn tắt, bỏ bước hoặc đơn giản chỉ là copy từ các chỗ khác về chỉnh sửa lại để quảng cáo cho trang web của họ mà thôi.
Connected to an idle instance.
SQL> spool /u01/app/oracle/admin/orcl/scripts/CloneRmanRestore.log append
SQL> startup mount pfile="/u01/app/oracle/admin/orcl/scripts/initorclTempOMF.ora";
ORACLE instance started.
Total System Global Area 1644166648 bytes
Fixed Size 9686520 bytes
Variable Size 956301312 bytes
Database Buffers 671088640 bytes
Redo Buffers 7090176 bytes
Database mounted.
SQL> execute dbms_backup_restore.resetCfileSection(dbms_backup_restore.RTYP_DFILE_COPY);
PL/SQL procedure successfully completed.
SQL> execute dbms_backup_restore.resetCfileSection(13);
PL/SQL procedure successfully completed.
SQL> host /home/oracle/database/bin/rman @/u01/app/oracle/admin/orcl/scripts/rmanRestoreDatafiles.sql &&sysPassword;
Recovery Manager: Release 21.0.0.0.0 - Production on Thu Sep 7 08:59:02 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target *
2>
3> CATALOG START WITH '/home/oracle/database/assistants/dbca/templates//Seed_Database.dfb' NOPROMPT ;
4>
5> RUN
6> {
7> set newname for datafile 1 to '/u01/app/oracle/oradata/ORCL/system01.dbf' ;
8> set newname for datafile 3 to '/u01/app/oracle/oradata/ORCL/sysaux01.dbf' ;
9> set newname for datafile 4 to '/u01/app/oracle/oradata/ORCL/undotbs01.dbf' ;
10> set newname for datafile 7 to '/u01/app/oracle/oradata/ORCL/users01.dbf' ;
11> restore datafile 1;
12> restore datafile 3;
13> restore datafile 4;
14> restore datafile 7;
15> switch datafile all;
16> }
17>
connected to target database: SEEDDATA (DBID=112743258, not open)
Starting implicit crosscheck backup at 07-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
Finished implicit crosscheck backup at 07-SEP-23
Starting implicit crosscheck copy at 07-SEP-23
using channel ORA_DISK_1
Finished implicit crosscheck copy at 07-SEP-23
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /home/oracle/database/assistants/dbca/templates//Seed_Database.dfb
List of Files Unknown to the Database
=====================================
File Name: /home/oracle/database/assistants/dbca/templates/Seed_Database.dfb
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /home/oracle/database/assistants/dbca/templates/Seed_Database.dfb
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 07-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/ORCL/system01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/database/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/home/oracle/database/assistants/dbca/templates/Seed_Database.dfb tag=TAG20210727T204350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 07-SEP-23
Starting restore at 07-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORCL/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/database/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/home/oracle/database/assistants/dbca/templates/Seed_Database.dfb tag=TAG20210727T204350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 07-SEP-23
Starting restore at 07-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORCL/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/database/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/home/oracle/database/assistants/dbca/templates/Seed_Database.dfb tag=TAG20210727T204350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-SEP-23
Starting restore at 07-SEP-23
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00007 to /u01/app/oracle/oradata/ORCL/users01.dbf
channel ORA_DISK_1: reading from backup piece /home/oracle/database/assistants/dbca/templates/Seed_Database.dfb
channel ORA_DISK_1: piece handle=/home/oracle/database/assistants/dbca/templates/Seed_Database.dfb tag=TAG20210727T204350
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 07-SEP-23
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1146906017 file name=/u01/app/oracle/oradata/ORCL/system01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=6 STAMP=1146906017 file name=/u01/app/oracle/oradata/ORCL/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=7 STAMP=1146906017 file name=/u01/app/oracle/oradata/ORCL/undotbs01.dbf
datafile 7 switched to datafile copy
input datafile copy RECID=8 STAMP=1146906017 file name=/u01/app/oracle/oradata/ORCL/users01.dbf
Recovery Manager complete.
SQL> column file0 NEW_VALUE file0;
SQL> select NAME file0 FROM V$DATAFILE where file# = 3;
FILE0
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/sysaux01.dbf
SQL> column file1 NEW_VALUE file1;
SQL> select NAME file1 FROM V$DATAFILE where file# = 1;
FILE1
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/system01.dbf
SQL> column file2 NEW_VALUE file2;
SQL> select NAME file2 FROM V$DATAFILE where file# = 4;
FILE2
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/undotbs01.dbf
SQL> column file3 NEW_VALUE file3;
SQL> select NAME file3 FROM V$DATAFILE where file# = 7;
FILE3
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/users01.dbf
SQL> spool off
SQL> @/u01/app/oracle/admin/orcl/scripts/cloneDBCreation.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/cloneDBCreation.log append
SQL> exec dbms_backup_restore.zerodbid(0);
PL/SQL procedure successfully completed.
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount pfile="/u01/app/oracle/admin/orcl/scripts/initorclTemp.ora";
ORACLE instance started.
Total System Global Area 1644166648 bytes
Fixed Size 9686520 bytes
Variable Size 956301312 bytes
Database Buffers 671088640 bytes
Redo Buffers 7090176 bytes
SQL> Create controlfile reuse set database "orcl"
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 1024
7 Datafile
8 '&&file0',
9 '&&file1',
10 '&&file2',
11 '&&file3'
12 LOGFILE GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') SIZE 200M,
13 GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') SIZE 200M,
14 GROUP 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') SIZE 200M RESETLOGS;
Control file created.
SQL> alter system enable restricted session;
System altered.
SQL> alter database "orcl" open resetlogs;
Database altered.
SQL> DECLARE
2 cursor cur_services is
3 select name from dba_services where name like 'seeddata%';
4 BEGIN
5 for i in cur_services loop
6 dbms_service.delete_service(i.name);
7 end loop;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> alter database rename global_name to "orcl";
Database altered.
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/ORCL/temp01.dbf' SIZE 20480K REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;
Tablespace altered.
SQL> select tablespace_name from dba_tablespaces where tablespace_name='USERS';
TABLESPACE_NAME
------------------------------
USERS
SQL> ALTER PROFILE default LIMIT PASSWORD_VERIFY_FUNCTION null;
Profile altered.
SQL> alter user sys account unlock identified by "&&sysPassword";
User altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter user system account unlock identified by "&&systemPassword";
User altered.
SQL> host /home/oracle/database/OPatch/datapatch -skip_upgrade_check -db orcl;
SQL Patching tool version 21.3.0.0.0 Production on Thu Sep 7 09:00:28 2023
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/sqlpatch_3306_2023_09_07_09_00_28/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
No release update patches installed
PDB CDB$ROOT:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
SQL Patching tool complete on Thu Sep 7 09:00:32 2023
SQL> @/u01/app/oracle/admin/orcl/scripts/plug_PDBSeed.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/plugDatabase.log append
SQL> spool /u01/app/oracle/admin/orcl/scripts/plugDatabase.log append
SQL> host mkdir -p /u01/app/oracle/oradata/ORCL/pdbseed;
SQL> host mkdir -p /u01/app/oracle/oradata/ORCL/pdbseed;
SQL> host mkdir -p /u01/app/oracle/oradata/ORCL/pdbseed;
SQL> host mkdir -p /u01/app/oracle/oradata/ORCL/pdbseed;
SQL> alter system set "_catalog_foreign_restore"=TRUE;
System altered.
SQL> alter system set "_restore_create_directory"=TRUE;
System altered.
SQL> host /home/oracle/database/bin/rman @/u01/app/oracle/admin/orcl/scripts/rmanPDBRestoreDatafiles.sql &&sysPassword;
Recovery Manager: Release 21.0.0.0.0 - Production on Thu Sep 7 09:00:33 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target *
2>
3> run
4> {
5> set command id to 'PDB$SEED';
6> RESTORE FOREIGN DATAFILE 2 FORMAT '/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf',4 FORMAT '/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf',9 FORMAT '/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf' FROM BACKUPSET '/home/oracle/database/assistants/dbca/templates/pdbseed.dfb' ;
7> }
8> exit;
connected to target database: ORCL (DBID=1674686758)
executing command: SET COMMAND ID
Starting restore at 07-SEP-23
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=137 device type=DISK
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring foreign file 00002
channel ORA_DISK_1: restoring foreign file 00004
channel ORA_DISK_1: restoring foreign file 00009
channel ORA_DISK_1: reading from backup piece /home/oracle/database/assistants/dbca/templates/pdbseed.dfb
channel ORA_DISK_1: restoring foreign file 2 to /u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
channel ORA_DISK_1: restoring foreign file 4 to /u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring foreign file 9 to /u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
channel ORA_DISK_1: foreign piece handle=/home/oracle/database/assistants/dbca/templates/pdbseed.dfb
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:16
Finished restore at 07-SEP-23
Recovery Manager complete.
SQL> column pdbfile0 NEW_VALUE pdbfile0;
SQL> select dc.name pdbfile0 from v$datafile_copy dc, v$rman_status rs where dc.name is not null and dc.status = 'A' and file# = 2 and rs.recid = dc.rman_status_recid and rs.stamp =dc.rman_status_stamp and rs.command_id = 'PDB$SEED';
PDBFILE0
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf
SQL> column pdbfile1 NEW_VALUE pdbfile1;
SQL> select dc.name pdbfile1 from v$datafile_copy dc, v$rman_status rs where dc.name is not null and dc.status = 'A' and file# = 4 and rs.recid = dc.rman_status_recid and rs.stamp =dc.rman_status_stamp and rs.command_id = 'PDB$SEED';
PDBFILE1
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf
SQL> column pdbfile2 NEW_VALUE pdbfile2;
SQL> select dc.name pdbfile2 from v$datafile_copy dc, v$rman_status rs where dc.name is not null and dc.status = 'A' and file# = 9 and rs.recid = dc.rman_status_recid and rs.stamp =dc.rman_status_stamp and rs.command_id = 'PDB$SEED';
PDBFILE2
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf
SQL> host /home/oracle/database/bin/rman @/u01/app/oracle/admin/orcl/scripts/rmanPDBCleanUpDatafiles.sql &&sysPassword;
Recovery Manager: Release 21.0.0.0.0 - Production on Thu Sep 7 09:00:50 2023
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target *
2>
3> crosscheck copy;
4> delete expired copy
5> exit;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00558: error encountered while parsing input commands
RMAN-01009: syntax error: found "exit": expecting one of: "backed, completed, device, for, guid, like, of, tag, ;"
RMAN-01007: at line 5 column 1 file: /u01/app/oracle/admin/orcl/scripts/rmanPDBCleanUpDatafiles.sql
Recovery Manager complete.
SQL> alter system set "_catalog_foreign_restore"=FALSE;
System altered.
SQL> alter system set "_restore_create_directory"=FALSE;
System altered.
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> CREATE PLUGGABLE DATABASE "PDB$SEED" AS CLONE USING '/home/oracle/database/assistants/dbca/templates/pdbseed.xml' source_file_name_convert = ('/oradata/SEEDDATA/pdbseed/system01.dbf','&&pdbfile0',
2 '/oradata/SEEDDATA/pdbseed/sysaux01.dbf','&&pdbfile1',
3 '/oradata/SEEDDATA/pdbseed/undotbs01.dbf','&&pdbfile2',
4 '/oradata/SEEDDATA/pdbseed/temp01.dbf','/u01/app/oracle/oradata/ORCL/pdbseed/temp012023-09-06_13-27-11-019-PM.dbf') NOCOPY STORAGE ( MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED);
Pluggable database created.
SQL> select name from v$containers where upper(name) = 'PDB$SEED';
NAME
--------------------------------------------------------------------------------
PDB$SEED
SQL> alter session set "_oracle_script"=true;
Session altered.
SQL> alter pluggable database "PDB$SEED" open restricted;
Pluggable database altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup restrict pfile="/u01/app/oracle/admin/orcl/scripts/initorclTemp.ora";
ORACLE instance started.
Total System Global Area 1644166648 bytes
Fixed Size 9686520 bytes
Variable Size 956301312 bytes
Database Buffers 671088640 bytes
Redo Buffers 7090176 bytes
Database mounted.
Database opened.
SQL> select sid, program, serial#, username from v$session;
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
1
oracle@alma8 (PSP0)
38299
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
2
oracle@alma8 (DIA0)
23907
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
3
oracle@alma8 (VOSD)
16718
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
4
oracle@alma8 (DBRM)
43156
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
5
oracle@alma8 (PXMN)
52129
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
6
oracle@alma8 (SMON)
38575
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
7
oracle@alma8 (SCMN)
54700
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
8
oracle@alma8 (BG00)
43349
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
9
oracle@alma8 (BG01)
60136
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
10
oracle@alma8 (BG01)
47725
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
11
oracle@alma8 (W001)
62730
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
13
oracle@alma8 (TT02)
385
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
127
oracle@alma8 (VKTM)
52783
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
128
oracle@alma8 (GEN1)
46117
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
129
oracle@alma8 (DIAG)
55387
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
130
oracle@alma8 (VKRM)
24492
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
131
oracle@alma8 (DBW0)
54855
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
132
oracle@alma8 (SMCO)
49661
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
133
oracle@alma8 (LG00)
34562
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
134
oracle@alma8 (BG00)
54491
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
135
oracle@alma8 (SCMN)
20258
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
136
oracle@alma8 (BG01)
40649
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
137
sqlplus@alma8 (TNS V1-V3)
37343
SYS
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
139
oracle@alma8 (M000)
27310
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
253
oracle@alma8 (PMON)
38867
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
254
oracle@alma8 (GEN0)
65299
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
255
oracle@alma8 (SCMN)
27493
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
256
oracle@alma8 (OFSD)
24436
SYS
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
257
oracle@alma8 (SVCB)
54075
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
258
oracle@alma8 (LGWR)
56821
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
259
oracle@alma8 (RECO)
58408
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
260
oracle@alma8 (MMON)
53270
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
261
oracle@alma8 (W000)
57116
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
263
oracle@alma8 (LG01)
2353
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
264
oracle@alma8 (TT00)
48665
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
265
oracle@alma8 (RCBG)
50466
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
379
oracle@alma8 (TT01)
19486
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
380
oracle@alma8 (CLMN)
23577
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
381
oracle@alma8 (MMAN)
35109
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
382
oracle@alma8 (GEN2)
32530
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
383
oracle@alma8 (SCMN)
34387
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
384
oracle@alma8 (PMAN)
29710
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
385
oracle@alma8 (CKPT)
40017
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
386
oracle@alma8 (LREG)
54721
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
387
oracle@alma8 (MMNL)
63908
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
388
oracle@alma8 (BG00)
36349
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
389
oracle@alma8 (TMON)
13357
SID
----------
PROGRAM
--------------------------------------------------------------------------------
SERIAL#
----------
USERNAME
--------------------------------------------------------------------------------
391
oracle@alma8 (CJQ0)
19526
48 rows selected.
SQL> alter database character set INTERNAL_CONVERT AL32UTF8;
Database altered.
SQL> alter database national character set INTERNAL_CONVERT AL16UTF16;
Database altered.
SQL> alter system disable restricted session;
System altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter pluggable database PDB$SEED CLOSE IMMEDIATE;
Pluggable database altered.
SQL> alter pluggable database PDB$SEED OPEN RESTRICTED;
Pluggable database altered.
SQL> DECLARE
2 cdb_root_timezone VARCHAR(6);
3 alter_statement VARCHAR(80);
4 BEGIN
5 select dbtimezone into cdb_root_timezone from dual;
6 execute immediate 'alter session set container=PDB$SEED';
7 alter_statement:='alter pluggable database pdb$seed set time_zone='''|| cdb_root_timezone ||'''';
8 execute immediate alter_statement;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> alter pluggable database PDB$SEED CLOSE IMMEDIATE;
Pluggable database altered.
SQL> alter pluggable database PDB$SEED OPEN restricted;
Pluggable database altered.
SQL> @/u01/app/oracle/admin/orcl/scripts/postScripts.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/postScripts.log append
SQL> disconnect;
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> connect "SYSTEM"/"&&systemPassword"
Connected.
SQL> disconnect;
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set "_oracle_script"=TRUE;
Session altered.
SQL> execute dbms_datapump_utl.create_default_dir;
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> alter session set "_oracle_script"=FALSE;
Session altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> host /home/oracle/database/perl/bin/perl /home/oracle/database/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/orcl/scripts -v -b ordlib -U "SYS"/"&&sysPassword" /home/oracle/database/ord/im/admin/ordlib.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/orcl/scripts/ordlib_catcon_4088.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/orcl/scripts/ordlib*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/orcl/scripts/ordlib_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
catcon::catconInit2: DBMS version: 21.0.0.0.0.
catcon::catconInit2: started SQL*Plus processes.
catcon::catconInit2: initialization completed successfully (2023-09-07 09:03:42)
catcon::catconExec: start executing scripts/SQL statements
catcon::catconExec_int: finished examining scripts/SQL statements to be executed.
catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB
catcon::log_script_execution: executing "@/home/oracle/database/ord/im/admin/ordlib.sql" in container CDB$ROOT using process 0
catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs
catcon::log_script_execution: executing "@/home/oracle/database/ord/im/admin/ordlib.sql" in container PDB$SEED using process 0
catcon::catconExec: finished executing scripts/SQL statements
catcon::catconWrapUp: (PID=4088) about to free up all resources
catcon::catconWrapUp: done
catcon.pl: completed successfully
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> create or replace directory XMLDIR as '/home/oracle/database/rdbms/xml';
Directory created.
SQL> create or replace directory XSDDIR as '/home/oracle/database/rdbms/xml/schema';
Directory created.
SQL> @/home/oracle/database/rdbms/admin/utlfixdirs.sql;
SQL> Rem
SQL> Rem $Header: rdbms/admin/utlfixdirs.sql /main/4 2020/07/20 02:48:48 dgoddard Exp $
SQL> Rem
SQL> Rem utlfixdirs.sql
SQL> Rem
SQL> Rem Copyright (c) 2017, 2018, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlfixdirs.sql - UTiLity FIXup DIRectorieS
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem
SQL> Rem This script looks at all of the DIRECTORY objects in the database
SQL> Rem and, if the path name contains the ORACLE_HOME or ORACLE_BASE
SQL> Rem directory path name, but those path names don't reflect the
SQL> Rem current ORACLE_HOME or ORACLE_BASE environment variable, then the
SQL> Rem directories are recreated with the values of the current ORACLE_HOME
SQL> Rem and ORACLE_BASE environment variables. If the original
SQL> Rem ORACLE_BASE directory path name is the same as the original
SQL> Rem ORACLE_HOME directory path name, then all of the directories will
SQL> Rem be recreated using the new ORACLE_HOME environment variable path,
SQL> Rem except for ORACLE_BASE, which will be recreated with the new value
SQL> Rem of the ORACLE_BASE environment variable.
SQL> Rem
SQL> Rem In a CDB, this script should be run first in PDBs, prior to running
SQL> Rem it in CDB$ROOT (e.g., using the -r option to catcon.pl). Once
SQL> Rem the ORACLE_HOME and ORACLE_BASE path names have been changed in
SQL> Rem CDB$ROOT, they are automatically changed in all PDBs, since those
SQL> Rem directories are shared as METADATA LINK. So non-Oracle-maintained
SQL> Rem directories in PDBs must be changed before fixing the the directories
SQL> Rem in CDB$ROOT, All Oracke-Maintained directory objects have METADATA
SQL> Rem LINK sharing, so they can only be changed in the CDB$ROOT, and,
SQL> Rem once changed, are reflected in all PDBs, including PDB$SEED.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run AS SYSDBA
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/utlfixdirs.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/utlfixdirs.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem SQL_CALLING_FILE: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem raeburns 05/29/18 - Bug 27807389: Check for same BASE and HOME path
SQL> Rem raeburns 01/20/18 - Bug 27456560: Revise for PDB fixup
SQL> Rem raeburns 11/27/17 - Bug 24494319: OH directory fixup
SQL> Rem raeburns 03/24/17 - Created
SQL> Rem
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2
3 con_id NUMBER := sys_context('USERENV', 'CON_ID');
4 db_open_mode sys.v$database.open_mode%type;
5
6 original_home sys.dir$.os_path%type; -- Use length of column in dir$
7 original_base sys.dir$.os_path%type;
8 current_home sys.dir$.os_path%type;
9 current_base sys.dir$.os_path%type;
10
11 comp_original_home sys.dir$.os_path%type; -- with \ converted to /
12 comp_original_base sys.dir$.os_path%type; -- for comparisons
13 comp_current_home sys.dir$.os_path%type;
14 comp_current_base sys.dir$.os_path%type;
15
16 original_home_end NUMBER; -- subdirectory path start location
17 original_base_end NUMBER; -- subdirectory path start location
18
19 -- Cursor to loop through the directories
20 CURSOR c1 IS
21 SELECT d.directory_name, d.directory_path, o.sharing,
22 replace(d.directory_path,'\','/') AS comp_directory_path
23 FROM dba_directories d, dba_objects o
24 WHERE d.directory_name = o.object_name AND
25 o.object_type = 'DIRECTORY'
26 ORDER BY directory_name;
27
28 BEGIN
29
30 ---------------------------------------------------------------------------
31 -- Get ORACLE_HOME and ORACLE_BASE environment variables
32 -- Convert backslashes to forward slashes for comparison values
33 ---------------------------------------------------------------------------
34 SYS.dbms_system.get_env('ORACLE_HOME', current_home);
35 comp_current_home := replace(current_home,'\','/');
36 SYS.dbms_system.get_env('ORACLE_BASE', current_base);
37 comp_current_base := replace(current_base,'\','/');
38
39 -- Get ORACLE_HOME and ORACLE_BASE directory path names
40 -- Store comp_ variable with no backslashes
41 SELECT directory_path, replace(directory_path,'\','/')
42 INTO original_home, comp_original_home
43 FROM dba_directories
44 WHERE directory_name = 'ORACLE_HOME';
45 original_home_end := length(original_home) + 1;
46 SELECT directory_path, replace(directory_path,'\','/')
47 INTO original_base, comp_original_base
48 FROM dba_directories
49 WHERE directory_name = 'ORACLE_BASE';
50 original_base_end := length(original_base) + 1;
51
52 ---------------------------------------------------------------------------
53 -- Display container name and check open mode
54 ---------------------------------------------------------------------------
55 dbms_output.put_line('
56 Container: ' || SYS_CONTEXT('USERENV', 'CON_NAME'));
57 SELECT open_mode INTO db_open_mode FROM sys.v$database;
58 IF db_open_mode != 'READ WRITE' THEN
59 dbms_output.put_line('
60 Cannot update directory objects; container is not open for READ WRITE.');
61 RETURN;
62 END IF;
63
64 ---------------------------------------------------------------------------
65 -- Check to see if the original ORACLE_HOME and ORACLE_BASE directories
66 -- were the same. If so, replace ORACLE_BASE with new environment variable
67 -- only for non-cdb and CDB$ROOT. ORACLE_BASE is always Oracle-Maintained.
68 ---------------------------------------------------------------------------
69
70 IF comp_original_home = comp_original_base THEN
71 IF comp_current_base IS NOT NULL AND con_id <=1 THEN
72 dbms_output.put_line('
73 Current ORACLE_BASE: ' || current_base);
74 dbms_output.put_line('Original ORACLE_BASE: ' ||
75 original_base || '
76 ');
77 dbms_output.put_line('...OLD: ' || original_base);
78 EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ORACLE_BASE AS ' ||
79 sys.dbms_assert.enquote_literal(current_base);
80 dbms_output.put_line('...NEW: ' || current_base);
81 END IF;
82 END IF;
83
84 ---------------------------------------------------------------------------
85 -- Process directories using ORACLE_HOME
86 -- Do ORACLE_HOME before ORACLE_BASE, since OFA recommends ORACLE_HOME
87 -- be a subdirectory of ORACLE_BASE.
88 ---------------------------------------------------------------------------
89 dbms_output.put_line('
90 Current ORACLE_HOME: ' || current_home);
91 dbms_output.put_line('Original ORACLE_HOME: ' || original_home || '
92 ');
93 -- Only scan the directory objects if the current ORACLE_HOME environment
94 -- variable value is not equal to the original ORACLE_HOME directory
95 -- path name. Do not perform the scan if either value is NULL, or if the
96 -- orginal ORACLE_HOME directory path name is '/', a default value.
97 IF (comp_original_home <> '/' AND
98 comp_original_home IS NOT NULL AND
99 comp_current_home IS NOT NULL AND
100 comp_current_home <> comp_original_home) THEN
101 FOR dir IN c1 LOOP
102 IF (instr(dir.comp_directory_path,comp_original_home) > 0) THEN
103 -- If in a PDB, only fix directories with SHARING=NONE
104 -- METADATA LINK directories cannot be changed; they will be
105 -- changed when this script is run in CDB$ROOT.
106 IF (con_id <=1) OR ((con_id > 1) AND (dir.sharing = 'NONE')) THEN
107 dbms_output.put_line(dir.directory_name);
108 dbms_output.put_line('...OLD: ' || dir.directory_path);
109 -- concantenate new HOME with remainder of original path
110 dir.directory_path := current_home ||
111 substr(dir.directory_path,original_home_end);
112 EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' ||
113 sys.dbms_assert.simple_sql_name(dir.directory_name) || ' AS ' ||
114 sys.dbms_assert.enquote_literal(dir.directory_path);
115 dbms_output.put_line('...NEW: ' || dir.directory_path);
116 END IF;
117 END IF;
118 END LOOP;
119 END IF;
120
121 ---------------------------------------------------------------------------
122 -- Process directories using ORACLE_BASE if it was different than
123 -- ORACLE_HOME
124 ---------------------------------------------------------------------------
125 IF (comp_original_base <> '/' AND
126 comp_original_base IS NOT NULL AND
127 comp_current_base IS NOT NULL AND
128 comp_original_home <> comp_original_base AND
129 comp_current_base <> comp_original_base) THEN
130 dbms_output.put_line('
131 Current ORACLE_BASE: ' || current_base);
132 dbms_output.put_line('Original ORACLE_BASE: ' || original_base || '
133 ');
134 -- Scan directory objects only if the orginal ORACLE_BASE directory
135 -- path name is not equal to the original ORACLE_HOME directory path name.
136 -- (in which case, the path names have already been converted as
137 -- ORACLE_HOME path names in the ORACLE_HOME directory processing above).
138 -- Only scan the directory objects if the current ORACLE_BASE environment
139 -- variable value is not equal to the original ORACLE_BASE directory
140 -- path name. Do not perform the scan if either value is NULL, or if the
141 -- original ORACLE_BASE directory path name is '/', a default value.
142
143 FOR dir IN c1 LOOP
144 IF (instr(dir.comp_directory_path,comp_original_base) > 0) THEN
145 -- If in a PDB, only fix directories with SHARING=NONE
146 -- METADATA LINK directories cannot be changed; they will be
147 -- changed when this script is run in CDB$ROOT.
148 IF (con_id <=1) OR ((con_id > 1) AND (dir.sharing = 'NONE')) THEN
149 dbms_output.put_line(dir.directory_name);
150 dbms_output.put_line('...OLD: ' || dir.directory_path);
151 -- concantenate new BASE with remainder of original path
152 dir.directory_path := current_base ||
153 substr(dir.directory_path,original_base_end);
154 EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY ' ||
155 sys.dbms_assert.simple_sql_name(dir.directory_name) || ' AS ' ||
156 sys.dbms_assert.enquote_literal(dir.directory_path);
157 dbms_output.put_line('...NEW: ' || dir.directory_path);
158 END IF;
159 END IF;
160 END LOOP;
161 END IF;
162 END;
163 /
Container: CDB$ROOT
Current ORACLE_BASE: /u01/app/oracle
Original ORACLE_BASE: /ade/b/3738518557/oracle
...OLD: /ade/b/3738518557/oracle
...NEW: /u01/app/oracle
Current ORACLE_HOME: /home/oracle/database
Original ORACLE_HOME: /ade/b/3738518557/oracle
DBMS_OPTIM_ADMINDIR
...OLD: /ade/b/3738518557/oracle/rdbms/admin
...NEW: /home/oracle/database/rdbms/admin
DBMS_OPTIM_LOGDIR
...OLD: /ade/b/3738518557/oracle/cfgtoollogs
...NEW: /home/oracle/database/cfgtoollogs
ORACLE_HOME
...OLD: /ade/b/3738518557/oracle
...NEW: /home/oracle/database
ORACLE_OCM_CONFIG_DIR
...OLD: /ade/b/3738518557/oracle/ccr/state
...NEW: /home/oracle/database/ccr/state
ORACLE_OCM_CONFIG_DIR2
...OLD: /ade/b/3738518557/oracle/ccr/state
...NEW: /home/oracle/database/ccr/state
SDO_DIR_ADMIN
...OLD: /ade/b/3738518557/oracle/md/admin
...NEW: /home/oracle/database/md/admin
PL/SQL procedure successfully completed.
SQL>
SQL> SET SERVEROUTPUT OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> @/home/oracle/database/rdbms/admin/execocm.sql;
SQL> Rem
SQL> Rem $Header: rdbms/admin/execocm.sql /main/1 2020/06/23 23:47:10 ckalivar Exp $
SQL> Rem
SQL> Rem execocm.sql
SQL> Rem
SQL> Rem Copyright (c) 2006, 2020, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem execocm.sql - EXECute Oracle Configuration Manager job.
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script submits and runs the database configuration collection
SQL> Rem job as part of database creation.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Create directory object for use by the job to create the configuration
SQL> Rem file at.
SQL> Rem This script should be run while connected as "SYS".
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem verangan 09/10/18 - fix for bug 28134318
SQL> Rem dkuhn 09/03/15 - XbranchMerge dkuhn_pdbcheck from main
SQL> Rem dkuhn 08/11/15 - XbranchMerge dkuhn_bug-21456791 from main
SQL> Rem dkuhn 06/29/15 - XbranchMerge dkuhn_fixgrant from main
SQL> Rem dkuhn 09/01/15 - Don't execute code if connected to PDB
SQL> Rem dkuhn 08/05/15 - bug 21456791: Add grant
SQL> Rem dkuhn 06/17/15 - Fix grant
SQL> Rem dkuhn 10/23/14 - bug 19840940: Don't run collect job immediately
SQL> Rem dkuhn 09/16/14 - fixtrans
SQL> Rem dkuhn 09/08/14 - add pdb check
SQL> Rem dkuhn 05/14/14 - Fix error checking
SQL> Rem jsutton 10/10/13 - make sure oracle_ocm exists
SQL> Rem jsutton 10/08/13 - add privs
SQL> Rem jsutton 09/24/12 - use enquote_literal for acl name
SQL> Rem jsutton 01/18/12 - grant restricted session
SQL> Rem ckalivar 01/09/12 - bug 11069555: add DBMS_SQL execute permissions to
SQL> Rem ORACLE_OCM user, incase public user dont have it
SQL> Rem jsutton 09/21/11 - check if in upgrade mode, skip acl stuff if so
SQL> Rem jsutton 09/19/11 - add grants
SQL> Rem jsutton 08/15/11 - check view validity
SQL> Rem jsutton 07/11/11 - Fix for upgrade path
SQL> Rem jsutton 07/06/11 - Ensure ACL set up for access to UTL_INADDR
SQL> Rem jsutton 07/20/09 - Add priv grants for utl_inaddr
SQL> Rem glavash 08/20/08 - grant required prives to user
SQL> Rem dkapoor 07/31/07 - remove stats job
SQL> Rem dkapoor 05/04/07 - stop old job
SQL> Rem dkapoor 01/04/07 - drop job before creating one
SQL> Rem dkapoor 09/20/06 - give priv only if not given to public
SQL> Rem dkapoor 09/13/06 - grant execute on dbms_scheduler
SQL> Rem dkapoor 07/26/06 - do not use define
SQL> Rem dkapoor 07/21/06 - use create_replace_dir
SQL> Rem dkapoor 06/06/06 - move directory creation after installing the
SQL> Rem packages
SQL> Rem dkapoor 05/23/06 - Created
SQL> Rem
SQL>
SQL> -- If connected to a CDB database, then _oracle_script should be set to TRUE
SQL> DECLARE
2 l_is_cdb VARCHAR2(5) := 'NO';
3 BEGIN
4 execute immediate 'SELECT UPPER(CDB) FROM V$DATABASE' into l_is_cdb;
5 IF l_is_cdb = 'YES' THEN
6 execute immediate 'ALTER SESSION SET "_oracle_script" = TRUE';
7 END IF;
8 EXCEPTION
9 WHEN OTHERS THEN null;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_vers v$instance.version%TYPE;
3 BEGIN
4 SELECT LPAD(version,10,'0') INTO l_vers FROM v$instance;
5 IF l_vers >= '12.1.0.0.0' THEN
6 -- This privilege is necessary in 12c when making calls to
7 -- ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj. The create_replace_dir_obj
8 -- procedure should only be called from within execocm.sql.
9 execute immediate 'GRANT INHERIT PRIVILEGES ON USER SYS TO ORACLE_OCM';
10 END IF;
11 END;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_vers v$instance.version%TYPE;
3 l_dirobj_priv_cnt NUMBER;
4 l_user_cnt NUMBER;
5 l_pkg_cnt NUMBER;
6 l_priv_cnt NUMBER;
7 l_comp_cnt NUMBER;
8 l_acl_count NUMBER;
9 l_acl_priv NUMBER;
10 l_acl_name VARCHAR2(4000);
11 l_stat VARCHAR2(4000);
12 l_is_cdb VARCHAR2(4) := 'NO';
13 l_con_id NUMBER;
14 BEGIN
15 -- The following code was added to ensure OCM code is not executed while connected
16 -- to a PDB database. The associated bug number is 19792374.
17 -- This next select returns information required to determine if connected to a PDB or not.
18 BEGIN
19 execute immediate 'SELECT UPPER(CDB), SYS_CONTEXT(''USERENV'', ''CON_ID'') FROM V$DATABASE' into l_is_cdb, l_con_id;
20 EXCEPTION
21 WHEN OTHERS THEN
22 null;
23 END;
24 -- The pseudo logic is do nothing if connected to a PDB, all other scenarios run the code.
25 -- YES and con_id = 1, means connected to root container.
26 -- YES and con_id > 1, means connected to a PDB.
27 -- NO or NULL means connected to a normal (non-container/PDB) database.
28 IF l_is_cdb = 'YES' and l_con_id > 1 THEN
29 -- Inside PDB, do nothing.
30 NULL;
31 ELSE
32 -- If not connected to a PDB, then execute the code.
33
34 BEGIN
35
36 select count(*) into l_user_cnt from dba_users where username = 'ORACLE_OCM';
37 IF l_user_cnt <> 0 THEN
38
39 select count(*) into l_priv_cnt from dba_tab_privs where
40 GRANTEE ='ORACLE_OCM' and TABLE_NAME='UTL_FILE' and
41 upper(PRIVILEGE) = 'EXECUTE';
42 IF l_priv_cnt = 0 THEN
43 -- Grant priv only if its not already given.
44 execute immediate 'GRANT EXECUTE ON SYS.UTL_FILE TO ORACLE_OCM';
45 END IF;
46
47 select count(*) into l_priv_cnt from dba_tab_privs where
48 GRANTEE ='ORACLE_OCM' and TABLE_NAME='DBMS_SCHEDULER' and
49 upper(PRIVILEGE) = 'EXECUTE';
50 IF l_priv_cnt = 0 THEN
51 -- Grant priv only if its not given.
52 execute immediate 'GRANT EXECUTE ON SYS.DBMS_SCHEDULER TO ORACLE_OCM';
53 END IF;
54
55 select count(*) into l_priv_cnt from dba_tab_privs where
56 GRANTEE ='ORACLE_OCM' and TABLE_NAME='UTL_INADDR' and
57 upper(PRIVILEGE) = 'EXECUTE';
58 IF l_priv_cnt = 0 THEN
59 -- Grant priv only if its not given.
60 execute immediate 'GRANT EXECUTE ON SYS.UTL_INADDR TO ORACLE_OCM';
61 END IF;
62
63 select count(*) into l_priv_cnt from dba_tab_privs where
64 GRANTEE ='ORACLE_OCM' and TABLE_NAME='DBMS_SQL' and
65 upper(PRIVILEGE) = 'EXECUTE';
66 IF l_priv_cnt = 0 THEN
67 -- Grant priv only if its not given to public.
68 execute immediate 'GRANT EXECUTE ON SYS.DBMS_SQL TO ORACLE_OCM';
69 END IF;
70
71 -- Grant RESTRICTED SESSION
72 execute immediate 'GRANT RESTRICTED SESSION TO ORACLE_OCM';
73
74 -- need to set up ACL if DB version > 11
75 select LPAD(version,10,'0') into l_vers from v$instance;
76 -- Grant privilege to use UTL_INADDR via ACL if necessary
77 IF l_vers >= '11.0.0.0.0' THEN
78 -- Skip over ACL/XML DB steps if in UPGRADE mode
79 select status into l_stat from dba_registry where comp_id='CATPROC';
80 if l_stat <> 'UPGRADING' then
81 -- check for XML DB installed
82 execute immediate 'select count(*) from dba_registry '||
83 'where COMP_NAME = ''Oracle XML Database'' and STATUS = ''VALID'''into l_comp_cnt ;
84 IF l_comp_cnt > 0 THEN
85 BEGIN
86 -- make sure DBA_NETWORK_ACLS view exists (may not in upgrade path)
87 execute immediate 'select count(*) from dba_objects '||
88 'where object_type=''VIEW'' and object_name=''DBA_NETWORK_ACLS'' and STATUS=''VALID'''
89 into l_comp_cnt;
90 IF l_comp_cnt > 0 THEN
91 -- check for ACL assigned to localhost
92 execute immediate 'select count(*) from dba_network_acls where host=''localhost''' into l_acl_count;
93 IF (l_acl_count = 0) THEN
94 -- create ACL and assign to localhost
95 execute immediate
96 'BEGIN '||
97 ' DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(''oracle-sysman-ocm-Resolve-Access.xml'', ' ||
98 '''OCM User Resolve Network Access using UTL_INADDR'', ''ORACLE_OCM'', TRUE, ''resolve'');' ||
99 ' DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(''oracle-sysman-ocm-Resolve-Access.xml'', ''localhost'');' ||
100 ' COMMIT;' ||
101 'END;';
102 ELSE
103 -- ACL for localhost exists
104 -- check for resolve privilege for OCM user
105 execute immediate
106 'SELECT acl, DBMS_NETWORK_ACL_ADMIN.CHECK_PRIVILEGE_ACLID(aclid, ''ORACLE_OCM'', ''resolve'') ' ||
107 ' FROM dba_network_acls WHERE host = ''localhost'''
108 INTO l_acl_name, l_acl_priv;
109 IF (l_acl_priv IS NULL OR l_acl_priv = 0) THEN
110 -- add resolve privilege
111 execute immediate
112 'BEGIN ' ||
113 ' DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(' || DBMS_ASSERT.ENQUOTE_LITERAL(l_acl_name) || ', ' ||
114 '''ORACLE_OCM'', TRUE, ''resolve'');' ||
115 ' COMMIT;' ||
116 'END;';
117 END IF;
118 END IF;
119 END IF;
120 EXCEPTION
121 WHEN OTHERS THEN NULL;
122 END;
123 END IF;
124 END IF;
125 END IF;
126 END IF;
127
128 SELECT count(*) into l_pkg_cnt from dba_objects
129 where owner = 'ORACLE_OCM' and object_type='PACKAGE BODY' and object_name='MGMT_CONFIG_UTL';
130
131 IF l_pkg_cnt <> 0 THEN
132 ORACLE_OCM.MGMT_CONFIG_UTL.create_replace_dir_obj;
133 select count(*) into l_dirobj_priv_cnt from dba_tab_privs
134 where GRANTEE ='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR' and upper(PRIVILEGE) = 'READ';
135 IF l_dirobj_priv_cnt = 0 THEN
136 execute immediate 'GRANT READ ON DIRECTORY ORACLE_OCM_CONFIG_DIR TO ORACLE_OCM';
137 END IF;
138 select count(*) into l_dirobj_priv_cnt from dba_tab_privs
139 where GRANTEE ='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR' and upper(PRIVILEGE) = 'WRITE';
140 IF l_dirobj_priv_cnt = 0 THEN
141 execute immediate 'GRANT WRITE ON DIRECTORY ORACLE_OCM_CONFIG_DIR TO ORACLE_OCM';
142 END IF;
143 -- add grants for 2nd directory
144 select count(*) into l_dirobj_priv_cnt from dba_tab_privs
145 where GRANTEE ='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR2' and upper(PRIVILEGE) = 'READ';
146 IF l_dirobj_priv_cnt = 0 THEN
147 execute immediate 'GRANT READ ON DIRECTORY ORACLE_OCM_CONFIG_DIR2 TO ORACLE_OCM';
148 END IF;
149 select count(*) into l_dirobj_priv_cnt from dba_tab_privs
150 where GRANTEE ='ORACLE_OCM' and TABLE_NAME='ORACLE_OCM_CONFIG_DIR2' and upper(PRIVILEGE) = 'WRITE';
151 IF l_dirobj_priv_cnt = 0 THEN
152 execute immediate 'GRANT WRITE ON DIRECTORY ORACLE_OCM_CONFIG_DIR2 TO ORACLE_OCM';
153 END IF;
154
155 COMMIT;
156 END IF;
157
158 EXCEPTION
159 WHEN OTHERS THEN
160 raise_application_error(-20007,SQLERRM);
161 END;
162 END IF; -- IF connected to a PDB or not.
163 END;
164 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- remove old dba jobs, if exists
SQL> DECLARE
2 job_num NUMBER;
3 CURSOR job_cursor is
4 SELECT job
5 FROM dba_jobs
6 WHERE schema_user = 'ORACLE_OCM'
7 AND (what like 'ORACLE_OCM.MGMT_CONFIG.%'
8 OR what like 'ORACLE_OCM.MGMT_DB_LL_METRICS.%');
9 BEGIN
10 FOR r in job_cursor LOOP
11 sys.DBMS_IJOB.REMOVE(r.job);
12 COMMIT;
13 END LOOP;
14 END;
15 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_user_cnt NUMBER;
3 l_is_cdb VARCHAR2(4) := 'NO';
4 l_con_id NUMBER;
5 BEGIN
6 -- The following code was added to ensure OCM code is not executed while connected
7 -- to a PDB database. The associated bug number is 19792374.
8 -- This next select returns information required to determine if connected to a PDB or not.
9 BEGIN
10 execute immediate 'SELECT UPPER(CDB), SYS_CONTEXT(''USERENV'', ''CON_ID'') FROM V$DATABASE' into l_is_cdb, l_con_id;
11 EXCEPTION
12 WHEN OTHERS THEN
13 null;
14 END;
15 -- The pseudo logic is do nothing if connected to a PDB, all other scenarios run the code.
16 -- YES and con_id = 1, means connected to root container.
17 -- YES and con_id > 1, means connected to a PDB.
18 -- NO or NULL means connected to a normal (non-container/PDB) database.
19 IF l_is_cdb = 'YES' and l_con_id > 1 THEN
20 -- Inside PDB, do nothing.
21 NULL;
22 ELSE
23 -- If not connected to a PDB, then execute the code.
24 select count(*) into l_user_cnt from dba_users where username = 'ORACLE_OCM';
25 IF l_user_cnt <> 0 THEN
26 execute immediate 'GRANT MANAGE SCHEDULER TO ORACLE_OCM';
27 END IF;
28 END IF; -- IF connected to a PDB or not.
29 END;
30 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- stop the job
SQL> DECLARE
2 l_pkg_cnt NUMBER;
3 BEGIN
4 SELECT count(*) into l_pkg_cnt from dba_objects
5 where owner = 'ORACLE_OCM' and object_type='PACKAGE BODY' and object_name='MGMT_CONFIG';
6 IF l_pkg_cnt <> 0 THEN
7 BEGIN
8 -- call to stop the job
9 ORACLE_OCM.MGMT_CONFIG.stop_job;
10 EXCEPTION
11 WHEN OTHERS THEN
12 -- ignore any exception
13 null;
14 END;
15 END IF;
16 END;
17 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- submit the job and run now
SQL> DECLARE
2 l_pkg_cnt NUMBER;
3 l_is_cdb VARCHAR2(4) := 'NO';
4 l_con_id NUMBER;
5 BEGIN
6 -- Check first to see if connected to a PDB.
7 BEGIN
8 execute immediate 'SELECT UPPER(CDB), SYS_CONTEXT(''USERENV'', ''CON_ID'') FROM V$DATABASE' into l_is_cdb, l_con_id;
9 EXCEPTION
10 WHEN OTHERS THEN
11 null;
12 END;
13 -- Pseudo logic is do nothing if connected to a PDB, all other scenarios submit the job.
14 -- YES and con_id = 1, means connected to root container.
15 -- YES and con_id > 1, means connected to a PDB
16 -- NO or NULL means connected to a normal (non-container/PDB) database.
17 IF l_is_cdb = 'YES' and l_con_id > 1 THEN
18 NULL;
19 ELSE
20 SELECT count(*) into l_pkg_cnt from dba_objects
21 where owner = 'ORACLE_OCM' and object_type='PACKAGE BODY' and object_name='MGMT_CONFIG';
22 IF l_pkg_cnt <> 0 THEN
23 ORACLE_OCM.MGMT_CONFIG.submit_job;
24 -- Do NOT run the collection job immediately when creating data dictionary objects.
25 -- ORACLE_OCM.MGMT_CONFIG.run_now;
26 END IF;
27 END IF; -- IF connected to a PDB or not.
28 END;
29 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_user_cnt NUMBER;
3 l_is_cdb VARCHAR2(4) := 'NO';
4 l_con_id NUMBER;
5 BEGIN
6 -- The following code was added to ensure OCM code is not executed while connected
7 -- to a PDB database. The associated bug number is 19792374.
8 -- This next select returns information required to determine if connected to a PDB or not.
9 BEGIN
10 execute immediate 'SELECT UPPER(CDB), SYS_CONTEXT(''USERENV'', ''CON_ID'') FROM V$DATABASE' into l_is_cdb, l_con_id;
11 EXCEPTION
12 WHEN OTHERS THEN
13 null;
14 END;
15 -- The pseudo logic is do nothing if connected to a PDB, all other scenarios run the code.
16 -- YES and con_id = 1, means connected to root container.
17 -- YES and con_id > 1, means connected to a PDB.
18 -- NO or NULL means connected to a normal (non-container/PDB) database.
19 IF l_is_cdb = 'YES' and l_con_id > 1 THEN
20 -- Inside PDB, do nothing.
21 NULL;
22 ELSE
23 -- If not connected to a PDB, then execute the code.
24 select count(*) into l_user_cnt from dba_users where username = 'ORACLE_OCM';
25 IF l_user_cnt <> 0 THEN
26 execute immediate 'REVOKE MANAGE SCHEDULER FROM ORACLE_OCM';
27 BEGIN
28 execute immediate 'REVOKE RESTRICTED SESSION FROM ORACLE_OCM';
29 EXCEPTION
30 WHEN OTHERS THEN
31 raise_application_error(-20007,SQLERRM);
32 END;
33 END IF; -- IF l_user_cnt <> 0
34 END IF; -- IF connected to a PDB or not.
35 END;
36 /
PL/SQL procedure successfully completed.
SQL>
SQL> DECLARE
2 l_user_cnt NUMBER;
3 l_vers v$instance.version%TYPE;
4 BEGIN
5 SELECT lPAD(version,10,'0') INTO l_vers FROM v$instance;
6 IF l_vers >= '12.1.0.0.0' THEN
7 SELECT COUNT(*) INTO l_user_cnt FROM dba_users WHERE username = 'ORACLE_OCM';
8 IF l_user_cnt <> 0 THEN
9 -- Ensure this privilege is revoked, the privilege should only be in place
10 -- while executing execocm.sql.
11 BEGIN
12 execute immediate 'REVOKE INHERIT PRIVILEGES ON USER SYS FROM ORACLE_OCM';
13 EXCEPTION
14 WHEN OTHERS THEN
15 -- ignore any exception
16 null;
17 END;
18 END IF;
19 END IF;
20 END;
21 /
PL/SQL procedure successfully completed.
SQL>
SQL> -- If connected to a CDB database, set _oracle_script to FALSE at end of script
SQL> DECLARE
2 l_is_cdb VARCHAR2(5) := 'NO';
3 BEGIN
4 execute immediate 'SELECT UPPER(CDB) FROM V$DATABASE' into l_is_cdb;
5 IF l_is_cdb = 'YES' THEN
6 execute immediate 'ALTER SESSION SET "_oracle_script" = FALSE';
7 END IF;
8 EXCEPTION
9 WHEN OTHERS THEN null;
10 END;
11 /
PL/SQL procedure successfully completed.
SQL> execute dbms_qopatch.replace_logscrpt_dirs;
PL/SQL procedure successfully completed.
SQL> host /home/oracle/database/perl/bin/perl /home/oracle/database/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/orcl/scripts -v -b execemx -U "SYS"/"&&sysPassword" /home/oracle/database/rdbms/admin/execemx.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/orcl/scripts/execemx_catcon_4166.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/orcl/scripts/execemx*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/orcl/scripts/execemx_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
catcon::catconInit2: DBMS version: 21.0.0.0.0.
catcon::catconInit2: started SQL*Plus processes.
catcon::catconInit2: initialization completed successfully (2023-09-07 09:03:48)
catcon::catconExec: start executing scripts/SQL statements
catcon::catconExec_int: finished examining scripts/SQL statements to be executed.
catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB
catcon::log_script_execution: executing "@/home/oracle/database/rdbms/admin/execemx.sql" in container CDB$ROOT using process 0
catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs
catcon::log_script_execution: executing "@/home/oracle/database/rdbms/admin/execemx.sql" in container PDB$SEED using process 0
catcon::catconExec: finished executing scripts/SQL statements
catcon::catconWrapUp: (PID=4166) about to free up all resources
catcon::catconWrapUp: done
catcon.pl: completed successfully
SQL> spool off
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/postDBCreation.log append
SQL> @/u01/app/oracle/admin/orcl/scripts/lockAccount.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/lockAccount.log append
SQL> BEGIN
2 FOR item IN ( SELECT USERNAME, AUTHENTICATION_TYPE FROM DBA_USERS WHERE ACCOUNT_STATUS IN ('OPEN', 'LOCKED', 'EXPIRED') AND USERNAME NOT IN (
3 'SYS','SYSTEM','SYSRAC','XS$NULL') )
4 LOOP
5 IF item.AUTHENTICATION_TYPE='PASSWORD' THEN
6 dbms_output.put_line('Locking and Expiring: ' || item.USERNAME);
7 execute immediate 'alter user ' ||
8 sys.dbms_assert.enquote_name(
9 sys.dbms_assert.schema_name(
10 item.USERNAME),false) || ' password expire account lock' ;
11 ELSE
12 dbms_output.put_line('Locking: ' || item.USERNAME);
13 execute immediate 'alter user ' ||
14 sys.dbms_assert.enquote_name(
15 sys.dbms_assert.schema_name(
16 item.USERNAME),false) || ' account lock' ;
17 END IF;
18 END LOOP;
19 END;
20 /
PL/SQL procedure successfully completed.
SQL> spool off
SQL> @/u01/app/oracle/admin/orcl/scripts/postDBCreation.sql
SQL> SET VERIFY OFF
SQL> spool /u01/app/oracle/admin/orcl/scripts/postDBCreation.log append
SQL> host /home/oracle/database/OPatch/datapatch -skip_upgrade_check -db orcl;
SQL Patching tool version 21.3.0.0.0 Production on Thu Sep 7 09:03:52 2023
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/sqlpatch_4239_2023_09_07_09_03_52/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
No release update patches installed
PDB CDB$ROOT:
No release update patches installed
PDB PDB$SEED:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: CDB$ROOT PDB$SEED
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
SQL Patching tool complete on Thu Sep 7 09:03:57 2023
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup mount pfile="/u01/app/oracle/admin/orcl/scripts/init.ora";
ORACLE instance started.
Total System Global Area 1644166648 bytes
Fixed Size 9686520 bytes
Variable Size 956301312 bytes
Database Buffers 671088640 bytes
Redo Buffers 7090176 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> create spfile='/u01/app/oracle/dbs/spfileorcl.ora' FROM pfile='/u01/app/oracle/admin/orcl/scripts/init.ora';
File created.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> host /home/oracle/database/perl/bin/perl /home/oracle/database/rdbms/admin/catcon.pl -n 1 -l /u01/app/oracle/admin/orcl/scripts -v -b utlrp -U "SYS"/"&&sysPassword" /home/oracle/database/rdbms/admin/utlrp.sql;
catcon::set_log_file_base_path: ALL catcon-related output will be written to [/u01/app/oracle/admin/orcl/scripts/utlrp_catcon_4720.lst]
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/orcl/scripts/utlrp*.log] files for output generated by scripts
catcon::set_log_file_base_path: catcon: See [/u01/app/oracle/admin/orcl/scripts/utlrp_*.lst] files for spool files, if any
catcon::catconInit2: start initializing catcon
catcon::catconInit2: finished constructing connect strings
catcon::catconInit2: start CDB-specific processing
catcon::catconInit2: finished examining instances which can be used to run scripts/SQL statements.
catcon::catconInit2: DBMS version: 21.0.0.0.0.
catcon::catconInit2: started SQL*Plus processes.
catcon::catconInit2: initialization completed successfully (2023-09-07 09:04:08)
catcon::catconExec: start executing scripts/SQL statements
catcon::catconExec_int: finished examining scripts/SQL statements to be executed.
catcon::catconExec_int: will run all scripts/statements against the Root (Container CDB$ROOT) of a CDB
catcon::log_script_execution: executing "@/home/oracle/database/rdbms/admin/utlrp.sql" in container CDB$ROOT using process 0
catcon::catconExec_int: run all scripts/statements against remaining 1 PDBs
catcon::force_pdb_modes: reset_pdb_modes completed successfully
catcon::log_script_execution: executing "@/home/oracle/database/rdbms/admin/utlrp.sql" in container PDB$SEED using process 0
catcon::catconExec: finished executing scripts/SQL statements
catcon::catconWrapUp: (PID=4720) about to free up all resources
catcon::catconWrapUp: done
catcon.pl: completed successfully
SQL> select comp_id, status from dba_registry;
COMP_ID STATUS
------------------------------ --------------------------------------------
CATALOG VALID
CATPROC VALID
RAC OPTION OFF
JAVAVM VALID
XML VALID
CATJAVA VALID
APS VALID
XDB VALID
OWM VALID
CONTEXT VALID
ORDIM VALID
COMP_ID STATUS
------------------------------ --------------------------------------------
XOQ VALID
SDO VALID
LCTR VALID
OLS VALID
DV VALID
16 rows selected.
SQL> execute dbms_swrf_internal.cleanup_database(cleanup_local => FALSE);
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected to an idle instance.
SQL> startup ;
ORACLE instance started.
Total System Global Area 1644166648 bytes
Fixed Size 9686520 bytes
Variable Size 956301312 bytes
Database Buffers 671088640 bytes
Redo Buffers 7090176 bytes
Database mounted.
Database opened.
SQL> spool off
SQL> @/u01/app/oracle/admin/orcl/scripts/PDBCreation.sql
SQL> SET VERIFY OFF
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/PDBCreation.log append
SQL> @/u01/app/oracle/admin/orcl/scripts/plug_orclpdb1.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/plugDatabase1R.log append
SQL> spool /u01/app/oracle/admin/orcl/scripts/plugDatabase1R.log append
SQL> host mkdir -p /u01/app/oracle/oradata/ORCL/orclpdb1;
SQL> 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;
D.NAME||'|'||T.NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf|SYSTEM
/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf|SYSAUX
/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf|UNDOTBS1
SQL> 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;
D.NAME||'|'||T.NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/pdbseed/temp012023-09-06_13-27-11-019-PM.dbf|TEMP
SQL> CREATE PLUGGABLE DATABASE "orclpdb1" ADMIN USER PDBADMIN IDENTIFIED BY "&&pdbadminPassword" ROLES=(CONNECT) file_name_convert=('/u01/app/oracle/oradata/ORCL/pdbseed',
2 '/u01/app/oracle/oradata/ORCL/orclpdb1') STORAGE ( MAXSIZE UNLIMITED MAX_SHARED_TEMP_SIZE UNLIMITED);
Pluggable database created.
SQL> select name from v$containers where upper(name) = 'ORCLPDB1';
NAME
--------------------------------------------------------------------------------
ORCLPDB1
SQL> alter pluggable database "orclpdb1" open;
Pluggable database altered.
SQL> alter system register;
System altered.
SQL> ALTER SESSION SET CONTAINER = "orclpdb1";
Session altered.
SQL> @/u01/app/oracle/admin/orcl/scripts/postPDBCreation_orclpdb1.sql
SQL> SET VERIFY OFF
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set container="orclpdb1";
Session altered.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/postPDBCreation.log append
SQL> CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '/u01/app/oracle/oradata/ORCL/orclpdb1/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
Tablespace created.
SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";
Database altered.
SQL> host /home/oracle/database/OPatch/datapatch -skip_upgrade_check -db orcl -pdbs orclpdb1;
SQL Patching tool version 21.3.0.0.0 Production on Thu Sep 7 09:05:54 2023
Copyright (c) 2012, 2021, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/homes/OraDB21Home1/cfgtoollogs/sqlpatch/sqlpatch_5581_2023_09_07_09_05_54/sqlpatch_invocation.log
Connecting to database...OK
Gathering database info...done
Note: Datapatch will only apply or rollback SQL fixes for PDBs
that are in an open state, no patches will be applied to closed PDBs.
Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation
(Doc ID 1585822.1)
Bootstrapping registry and package to current versions...done
Determining current state...done
Current state of interim SQL patches:
No interim patches found
Current state of release update SQL patches:
Binary registry:
No release update patches installed
PDB ORCLPDB1:
No release update patches installed
Adding patches to installation queue and performing prereq checks...done
Installation queue:
For the following PDBs: ORCLPDB1
No interim patches need to be rolled back
No release update patches need to be installed
No interim patches need to be applied
SQL Patching tool complete on Thu Sep 7 09:05:58 2023
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> select property_value from database_properties where property_name='LOCAL_UNDO_ENABLED';
PROPERTY_VALUE
--------------------------------------------------------------------------------
TRUE
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set container="orclpdb1";
Session altered.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/postPDBCreation.log append
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set container="orclpdb1";
Session altered.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/postPDBCreation.log append
SQL> select TABLESPACE_NAME from cdb_tablespaces a,dba_pdbs b where a.con_id=b.con_id and UPPER(b.pdb_name)=UPPER('orclpdb1');
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
SQL> connect "SYS"/"&&sysPassword" as SYSDBA
Connected.
SQL> alter session set container="orclpdb1";
Session altered.
SQL> set echo on
SQL> spool /u01/app/oracle/admin/orcl/scripts/postPDBCreation.log append
SQL> Select count(*) from dba_registry where comp_id = 'DV' and status='VALID';
COUNT(*)
----------
1
SQL> alter session set container=CDB$ROOT;
Session altered.
SQL> exit;
Disconnected from Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0