Oracle Class Setup
Version 12.1.0.1

General Information
Description This page contains the SQL statements that can be used in a 12cR1 Oracle database to create an  environment compatible with running library code samples and demos. Some of the code in this section is repeated in the library but this page is designed to bring specific pieces together into a coherent structure to allow you to learn about and work with the new database CDB/PDB architecture.

The first instruction assumes you have already followed Oracle's published documentation and installed a 12cR1 database on Windows though everything here is compatible with a Linux or other install as well except for pathing. When doing so you created a CDB with a single PDB named PDBORCL that contains Oracle's sample schemas such as HR, OE, PM, SH, and SCOTT. A second PDB is created as part of the setup, as indicated below, into which our class user, UWCLASS, is created.
Edition Enterprise
Version 12.1.0.1.0 or above
 
As ROOT
These alterations are recommended assuming the indicated conditions are met -- server has > 16GB RAM and SGA will be larger than 5GB
-- configure HugePages

memory_target=0
memory_max_target=0
sga_target=integer
pga_aggregate_target=integer

run the script hugepages_settings.sh
-- copy the shell scripts output which will look something like this:
vm.nr_hugepages = 9999 to the last line of sysctl.conf
/etc/sysctl –p
-- validate the change has taken affect
grep Huge /proc/meminfo
 
As SYSDBA
Modify GLOGIN.SQL Find the file {ORACLE_HOME}/sqlplus/admin/glogin.sql and rename it to glogin.bak. Create a new file in the same directory named glogin.sql with the as follows contents.

set arraysize 250
set define off
set linesize 121
set long 1000000
set pagesize 45
set serveroutput on
set trim on
set trimspool on

col argument_name format a30
col cluster_name format a30
col col_name format a30
col column_name format a30
col constraint_name format a30
col container_name format a30
col data_type format a30
col db_link format a30
col directory_name format a30
col directory_path format a30
col edition_name format a30
col file_name format a60
col granted_role format a30
col grantee format a30
col host_name format a20
col index_name format a30
col iot_name format a30
col max_lag_time format a12
col name format a30
col object_name format a30
col object_type format a20
col owner format a25
col owner_name format a25
col package_name format a30
col param_name format a25
col partition_name format a30
col pdb format a20
col procedure_name format a30
col queue_table format a30
col role format a30
col schedule_name format a30
col segment_name format a30
col service_name format a30
col subobject_name format a30
col synonym_name format a30
col table_name format a30
col table_type format a30
col triggering_event format a35
col type_name format a30
col type_owner format a30
col type_subname format a30
col username format a30
col value format a30

ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL';

-- if *nix add this line too
define _editor=vi
Modify SQL*Plus (Windows only)
  1. After opening SQL*Plus in a terminal window right click on the window title: Select PROPERTIES
  2. On the Options tab page make sure the edit options "QuickEdit Mode" and "Insert Mode" are both selected
  3. On the Layout tab page set the Screen Buffer Size width to 161 and the height to 1000.
    Set the Window size width to 161 and the height to 50.
SQL*Plus Logon / as sysdba
Verify you are in the root of your Container Database (CDB) -- you must see the following:

SQL> sho con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> sho con_id

CON_ID
------------------------------
1
Create a view for monitoring the status of the containers inside your CDB CREATE OR REPLACE VIEW pdb_status AS
SELECT v.name, v.open_mode, NVL(v.restricted, 'n/a') "RESTRICTED", d.status
FROM v$pdbs v, dba_pdbs d
WHERE v.guid = d.guid
ORDER BY v.create_scn;

SQL> SELECT * FROM pdb_status;

-- you must see the following
NAME                           OPEN_MODE  RES STATUS
------------------------------ ---------- --- ------
PDB$SEED                       READ ONLY  NO  NORMAL
PDBORCL                        READ WRITE NO  NORMAL

-- if PDBORCL has an OPEN_MODE of MOUNT do the following:

SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Log into the PDBORCL PDB SQL> SELECT name
   2 FROM v$active_services
   3 ORDER BY 1;

NAME
------------------------------
SYS$BACKGROUND
SYS$USERS
orabase
orabaseXDB
pdborcl

-- listener changes ... then stop and restart
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:c:\app\oracle\product\12.1.0\dbhome_1\bin\oraclr12.dll")
    )
    (SID_DESC =
      (SID_NAME = PDBORCL)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = PDBDEV)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
    )
    (SID_DESC =
      (SID_NAME = PDBTEST)
      (ORACLE_HOME = c:\app\oracle\product\12.1.0\dbhome_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = PERRITO4)(PORT = 1522))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
    )
  )

ADR_BASE_LISTENER = C:\app\oracle

-- it may be necessary to configure listener.ora and tnsnames.ora to 127.0.0.1
-- and restart the listener ... check in final release
-- add an entry into tnsnames.ora file


PDBORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
      (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDBORCL)
      )
  )
Create first PDB: ORADEV -- create pdb attributes method
CREATE PLUGGABLE DATABASE pdbdev
ADMIN USER devdba IDENTIFIED BY devdba
FILE_NAME_CONVERT = ('\pdbseed\', '\pdbdev\')
STORAGE (MAXSIZE 400M MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE oradata DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\oradata01.dbf' SIZE 100M;

SELECT * FROM pdb_status;

ALTER PLUGGABLE DATABASE pdbdev OPEN;

SELECT * FROM pdb_status;
Create second PDB: ORATEST -- create pdb clone method
CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev
FILE_NAME_CONVERT = ('\pdbdev\', '\pdbtest\');
CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev
*
ERROR at line 1:
ORA-65081: database or pluggable database is not open in read only mode


ALTER PLUGGABLE DATABASE pdbdev CLOSE;

SELECT * FROM pdb_status;

ALTER PLUGGABLE DATABASE pdbdev OPEN READ ONLY;

SELECT * FROM pdb_status;

CREATE PLUGGABLE DATABASE pdbtest FROM pdbdev
FILE_NAME_CONVERT = ('\pdbdev\', '\pdbtest\');

ALTER PLUGGABLE DATABASE pdbdev CLOSE;
ALTER PLUGGABLE DATABASE ALL OPEN;

SELECT * FROM pdb_status;
Create tablespace and class user in all three PDBs -- here's where the connection fun begins

exec show_container;

ALTER SESSION SET CONTAINER = pdbdev;

select sys_context('USERENV', 'CON_NAME') from dual;

-- make sure open if not ALTER PLUGGABLE DATABASE OPEN;

desc dba_data_files

SELECT file_name FROM dba_data_files;

CREATE TABLESPACE uwdata
DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\uwdata01.dbf' SIZE 250M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata;

GRANT create session TO uwclass;
GRANT create cluster TO uwclass;
GRANT create database link TO uwclass;
GRANT create operator TO uwclass;
GRANT create procedure TO uwclass;
GRANT create role TO uwclass;
GRANT create sequence TO uwclass;
GRANT create synonym TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create type TO uwclass;
GRANT create view TO uwclass;
GRANT select any dictionary TO uwclass;
GRANT execute ON dbms_lock TO uwclass;

ALTER SESSION SET CONTAINER = PDBDEV;

SELECT sys_context('USERENV', 'CON_NAME') FROM dual;

ALTER PLUGGABLE DATABASE OPEN;

ALTER PLUGGABLE DATABASE SET DEFAULT BIGFILE TABLESPACE;
-- need to find where is this stored in the data dictionary

SELECT file_name FROM dba_data_files;

CREATE TABLESPACE uwdata
DATAFILE 'c:\app\oracle\oradata\orabase\pdbdev\uwdata01.dbf' SIZE 100M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TABLESPACE uwdata
*
ERROR at line 1:
ORA-65114: space usage in container is too high


SQL> select file_name, bytes/1024/1024 as bytes
2 from dba_data_files;

FILE_NAME                                                            MB
------------------------------------------------------------ ----------
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSTEM01.DBF                   260
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\SYSAUX01.DBF                   680
C:\APP\ORACLE\ORADATA\ORABASE\PDBDEV\ORADATA01.DBF                  100

ALTER PLUGGABLE DATABASE pdbdev STORAGE (MAXSIZE 600M);

CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata;

GRANT create session TO uwclass;
GRANT create cluster TO uwclass;
GRANT create database link TO uwclass;
GRANT create operator TO uwclass;
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create synonym TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create type TO uwclass;
GRANT create view TO uwclass;
GRANT select any dictionary TO uwclass;


alter session set container = PDBTEST;
Create class user in all 3 PDBs -- need to make TNSNAMES.ORA entry then
-- set ORACLE_HOME then
-- tnsping test then

sqlplus sys@pdborcl as sysdba

CREATE OR REPLACE VIEW sys.xplan AS
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE',NULL,'ALL'));

GRANT SELECT on xplan TO PUBLIC;

CREATE PUBLIC SYNONYM xplan FOR sys.xplan;

SELECT file_name
FROM dba_data_files;

CREATE TABLESPACE uwdata
DATAFILE 'C:\ORACLE\ORABASE\ORADATA\ORABETA\PDBORCL\UWDATA01.DBF' SIZE 250M
LOGGING FORCE LOGGING ONLINE PERMANENT BLOCKSIZE 8K
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K
SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER uwclass
IDENTIFIED BY uwclass
DEFAULT TABLESPACE uwdata
TEMPORARY TABLESPACE temp
QUOTA 0 ON SYSTEM
QUOTA 0 ON SYSAUX
QUOTA UNLIMITED ON uwdata;
Grant privileges to class user in all 3 PDBs GRANT create session TO uwclass;
GRANT create cluster TO uwclass;
GRANT create database link TO uwclass;
GRANT create operator TO uwclass;
GRANT create procedure TO uwclass;
GRANT create sequence TO uwclass;
GRANT create synonym TO uwclass;
GRANT create table TO uwclass;
GRANT create trigger TO uwclass;
GRANT create type TO uwclass;
GRANT create view TO uwclass;
GRANT select any dictionary TO uwclass;
Start Examples Pluggable conn / as sysdba

ALTER PLUGGABLE DATABASE pdborcl START;

SELECT * FROM pdb_status;

SELECT COUNT(*)
FROM cdb_status;

SELECT COUNT(*)
FROM dba_users;

SELECT con_id, COUNT(*)
FROM cdb_users
GROUP BY con_id;
Unlock Oracle Demo Schemas: If necessary conn / as sysdba

ALTER SESSION SET CONTAINER = PDBORCL;

ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger;

ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

ALTER USER oe ACCOUNT UNLOCK IDENTIFIED BY oe;

ALTER USER pm ACCOUNT UNLOCK IDENTIFIED BY pm;

ALTER USER sh ACCOUNT UNLOCK IDENTIFIED BY sh;
Revoke unnecessary grants to PUBLIC REVOKE execute ON dbms_job FROM PUBLIC;
REVOKE execute ON dbms_obfuscation_toolkit FROM PUBLIC;
REVOKE execute ON dbms_random FROM PUBLIC;
REVOKE select ON all_source FROM PUBLIC;
REVOKE select ON role_role_privs FROM PUBLIC;
REVOKE select ON user_role_privs FROM PUBLIC;
REVOKE select ON user_sys_privs FROM PUBLIC;
REVOKE select ON user_tab_privs FROM PUBLIC;
Remove access to DBA_ views REVOKE select ON dba_auto_segadv_ctl, FROM PUBLIC;
REVOKE select ON dba_auto_segadv_summary FROM PUBLIC;
REVOKE select ON dba_col_pending_stats FROM PUBLIC;
REVOKE select ON dba_dbfs_hs_fixed_properties FROM PUBLIC;
REVOKE select ON dba_editioning_view_cols FROM PUBLIC;
REVOKE select ON dba_editioning_view_cols_ae FROM PUBLIC;
REVOKE select ON dba_flashback_archive FROM PUBLIC;
REVOKE select ON dba_flashback_archive_tables FROM PUBLIC;
REVOKE select ON dba_flashback_archive_ts FROM PUBLIC;
REVOKE select ON dba_heat_map_segment FROM PUBLIC;
REVOKE select ON dba_heat_map_seg_histogram FROM PUBLIC;
REVOKE select ON dba_ind_pending_stats FROM PUBLIC;
REVOKE select ON dba_java_classes FROM PUBLIC;
REVOKE select ON dba_scheduler_remote_databases FROM PUBLIC;
REVOKE select ON dba_sdo_maps FROM PUBLIC;
REVOKE select ON dba_sdo_styles FROM PUBLIC;
REVOKE select ON dba_sdo_themes FROM PUBLIC;
REVOKE select ON dba_sr_partn_ops FROM PUBLIC;
REVOKE select ON dba_sr_stlog_stats FROM PUBLIC;
REVOKE select ON dba_sync_capture_tables FROM PUBLIC;
REVOKE select ON dba_tab_histgrm_pending_stats FROM PUBLIC;
REVOKE select ON dba_tab_pending_stats FROM PUBLIC;
REVOKE select ON dba_tab_stat_prefs FROM PUBLIC;
REVOKE select ON dba_tstz_tables FROM PUBLIC;
REVOKE select ON dba_xmlschema_level_view FROM PUBLIC;
Alter init params ALTER SYSTEM SET global_names = TRUE CONTAINER = CURRENT SCOPE = BOTH;
ALTER SYSTEM SET remote_login_passwordfile = NONE  CONTAINER = ALL SCOPE = SPFILE;
ALTER SYSTEM SET sec_max_failed_login_attempts = 3 CONTAINER = ALL SCOPE = SPFILE;
ALTER SYSTEM SET sec_protocol_error_further_action = 1 CONTAINER = ALL SCOPE = SPFILE;
ALTER SYSTEM SET sec_protocol_error_trace_action = log CONTAINER = CURRENT SCOPE = BOTH;
ALTER SYSTEM SET use_large_pages = TRUE CONTAINER = ALL SCOPE = SPFILE;

-- restart the database so these changes take effect immediately
-- the alteraction for sec_protocol_error_trace_action needs to performed individually for each container
Multiplex Redo Logs ALTER DATABASE ADD LOGFILE MEMBER 'c:\app\oracle\fast_recovery_area\orabase\REDO01b.LOG' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER 'c:\app\oracle\fast_recovery_area\orabase\REDO02b.LOG' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER 'c:\app\oracle\fast_recovery_area\orabase\REDO03b.LOG' TO GROUP 3;

ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM CHECKPOINT;

SELECT group#, status
FROM v$log;

SELECT group#, status
FROM v$logfile;
Enhance AWR -- make the SYSAUX tablespace autoextendable if it is not.
ALTER DATABASE DATAFILE 'C:\APP\ORACLE\ORADATA\ORABASE2\SYSAUX01.DBF' AUTOEXTEND ON;

-- get the database's dbid
SELECT dbid
FROM v$database;

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);

-- set retention to 30 days, interval to 20 minutes, and topnsql to 50,000
exec dbms_workload_repository.modify_snapshot_settings((24*60*31), 20, 50000, 428676178);

SELECT retention, snap_interval, topnsql
FROM wrm$_wr_control
WHERE dbid = (SELECT dbid FROM v$database);
Collect stats -- collect system stats
SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

exec dbms_stats.gather_system_stats('INTERVAL', 15);

SELECT pname, pval1
FROM aux_stats$
WHERE sname = 'SYSSTATS_MAIN';

-- fixed object stats
exec dbms_stats.gather_fixed_objects_stats;

-- gather dictionary stats
exec dbms_stats.gather_dictionary_stats;
Secure the file system (perform actions as root)
Location Command  
cd $ORACLE_BASE/ chmod -R 600 audit  
cd $ORACLE_BASE/diag/rdbms/orabase/orabase chmod -R 700 trace  
cd $ORACLE_HOME/network/admin chmod 644 listener.ora sqlnet.ora  
cd $ORACLE_HOME/bin chmod 750 sqlplus SQL*Plus command line utility
cd $ORACLE_HOME/bin chmod 750 exp imp Database export and import
cd $ORACLE_HOME/bin chmod 750 expdp impdp Datapump export and import
cd $ORACLE_HOME/bin chmod 750 orapwd dg4pwd Password file generators
cd $ORACLE_HOME/bin chmod 750 sqlldr SQL*Loader
cd $ORACLE_HOME/bin chmod 750 dbca dbua netca netmgr lsnrctl  
cd $ORACLE_HOME/bin chmod 750 kfed kfod  
cd $ORACLE_HOME/bin chmod 750 oraenv Oracle environment control
cd $ORACLE_HOME/bin chmod 750 owm Oracle Wallet Manager
cd $ORACLE_HOME/bin chmod 750 wrap PL/SQL code wrap utility
Secure the Listener -- add the following lines to the listener.ora file at $ORACLE_HOME/network/admin
ADMIN_RESTRICTIONS_LISTENER_NAME = ON
LOGGING_LISTENER = ON
SECURE_CONTROL_LISTENER = (TCPS, IPC)
SECURE_PROTOCOL_LISTENER = (TCPS, IPC)
SECURE_REGISTER_LISTENER = (TCP, IPC)
TRACE_DIRECTORY_LISTENER = /app/oracle/product/12.1.0/db_1/network/trace
TRACE_FILE_LISTENER = listener.trc

-- add the following lines to the sqlnet.ora file at $ORACLE_HOME/network/admin
ALLOW_LOGON_VERSION = 11
TRACE_DIRECTORY_CLIENT = ORACLE_BASE/diag/clients/user_oracle
TRACE_DIRECTORY_SERVER = ORACLE_BASE/diag/tnslsnr
Secure the Database -- alter the default profile
ALTER PROFILE DEFAULT LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 1;

-- prevent access from remotely created db links
-- later, when running db link demos, you will need to allow connections

exec dbms_distributed_trust_admin.deny_all;
Set Stats Collection Preferences exec dbms_stats.set_database_prefs('CASCADE', 'DBMS_STATS.AUTO_CASCADE');
exec dbms_stats.set_database_prefs('DEGREE','2');
Create Non-Installation Roles conn sys@pdbdev as sysdba

-- role for autotrace
@?/sqlplus/admin/plustrce.sql

-- role for tkprof
@?/rdbms/admin/utltkprf.sql

GRANT plustrace TO uwclass;
GRANT tkprofer TO uwclass;
 
As UWCLASS
Create Class Schema Objects conn uwclass/uwclass

Create the airplanes tables: Click Here

-- save this file as
c:\temp\airplanes.sql

Create the servers and serv_inst tables: Click Here

-- save this file as
c:\temp\servers.sql

Create the postal_code table: Click Here

-- save this file as
c:\temp\postal_code.sql

-- logged on as uwclass create the airplanes table
SQL> @c:\temp\airplanes.sql

-- logged on as uwclass create the servers and serv_inst tables
SQL> @c:\temp\servers.sql

-- logged on as uwclass create the postal_code table
SQL> @c:\temp\postal.sql

Related Topics
Container Database
SQL*Plus
Tables
Tablespaces
Users

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved