Practices Environment

Overview

The practices are designed to be independent of each other. However, before starting a practice it is suggested that you re-create your database in your testing environment.

You should not run these labs on a production system. Commands and scripts are provided solely for testing purposes and need some adaptation to your testing environment.

Step 1 : Create an Oracle Database 21c instance

Create an Oracle Database 21c instance. If you plan to create the instance in Oracle Cloud Infrastructure, follow the instructions described in the Create an Oracle Cloud Infrastructure VM Database tutorial.
In the tutorial, in Lab 4 Create an Oracle Cloud Infrastructure VM Database - Step 2 Create a Database Virtual Machine - Substep 4 define the CDB and PDB as:
- Database name: Choose default database name to "cdb21".
- PDB name field, enter "pdb21".

Step 2 : Define and test the connections

Log in to the VM of your Database Virtual Machine. Following the tutorial, Lab 4 Create an Oracle Cloud Infrastructure VM Database - Step 3 Gather system details and connect to the Database using SSH to get the IP address of the node and the procedure to log in to the VM.

Create the net service name alias CDB21 for the container database CDB21 and the net service name alias PDB21 for the pluggable database PDB21.

Run the lsnrctl utility to find the path of the /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora file. The letters in the Unix prompt following the login username (opc or root or oracle) are the Hostname prefix defined during the Database VM creation via the tutorial, Lab 4 Create an Oracle Cloud Infrastructure VM Database - Step 2 Create a Database Virtual Machine - Subset 3 On the DB System Information form.


[oracle@xx ~]$ lsnrctl status

LSNRCTL for Linux: Version 21.0.0.0.0 - Production on 11-DEC-2020 09:28:09

Copyright (c) 1991, 2020, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxx.sub12100925130.vcndj.oraclevcn.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 21.0.0.0.0 - Production
Start Date                10-DEC-2020 09:43:06
Uptime                    0 days 23 hr. 45 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/homes/OraDB21Home1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/xxxxx/listener/alert/log.xml
Listening Endpoints Summary...
...
Service "CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com" has 1 instance(s).
  Instance "CDB21", status READY, has 1 handler(s) for this service...
Service "pdb21.sub12100925130.vcndj.oraclevcn.com" has 1 instance(s).
  Instance "CDB21", status READY, has 1 handler(s) for this service...

The OraDB21Home1 sub-directory is the sub-directory mentioned in the /u01/app/oraInventory/ContentsXML/inventory.xml file.


<HOME_LIST>
<HOME NAME="OraDB21Home1" LOC="/u01/app/oracle/product/21.0.0/dbhome_1" TYPE="O" IDX="1">
</HOME_LIST>

Open the /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora file. There is a net service alias for the container database, CDB21, using the databaseUniqueName. Replace the SERVICE_NAME entry with the value found in the service from lsnrctl, such as CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com.


[oracle@xx ~]$ vi /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora

# tnsnames.ora Network Configuration File: /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsn
# Generated by Oracle configuration tools.

CDB21_FRA1XN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XX.sub12100925130.xxxx.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com)
    )
  )

Create an alias entry by copying the CDB alias entry. Replace the CDB alias name with CDB21.


CDB21_FRA1XN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XX.sub12100925130.xxxx.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com)
    )
  )

CDB21 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XX.sub12100925130.xxxx.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com)
    )
  )

Create an alias entry for the PDB by copying the CDB alias entry. Replace the CDB alias name with PDB21.


CDB21_FRA1XN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XX.sub12100925130.xxxx.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com)
    )
  )

CDB21 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XX.sub12100925130.xxxx.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = CDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com)
    )
  )

PDB21 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = XX.sub12100925130.xxxx.oraclevcn.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PDB21_fra1xn.sub12100925130.xxxxx.oraclevcn.com)
    )
  )

Save the updates in the /u01/app/oracle/homes/OraDB21Home1/network/admin/tnsnames.ora file.

Test the connection to CDB21.

Connect to CDB21 with SQL*Plus.


[oracle@xx ~]$ sqlplus sys@CDB21 AS SYSDBA

Enter password: password_defined_during_DBSystem_creation

Connected to:
...
SQL>

Verify that the container name is CDB$ROOT.


SQL> SHOW CON_NAME

CON_NAME
------------------------------
CDB$ROOT
SQL>

Test the connection to PDB21, and then exit SQL*Plus.

Connect to PDB21.


SQL> CONNECT sys@PDB21 AS SYSDBA

Enter password: password_defined_during_DBSystem_creation

Connected.
SQL>

Show the container name.


SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDB21
SQL>

Exit SQL*Plus.
```
SQL> EXIT
[oracle@xx ~]$
```

Step 3 : Download the practices scripts

Download the Cloud_21c_labs_for_practices.zip file to the home directory of the login user opc, /home/opc on your VM.

Copy the /home/opc/Cloud_21c_labs_for_practices.zip to /home/oracle/Cloud_21c_labs_for_practices.zip.


[opc@xx ~]$ sudo su -
Last login: Thu Dec 10 09:49:37 UTC 2020
[root@xx ~]# cp /home/opc/Cloud_21c_labs_for_practices.zip /home/oracle/
[root@xx ~]# chown oracle:oinstall  /home/oracle/Cloud_21c_labs_for_practices.zip
[root@xx ~]# exit
logout
[opc@xx ~]$

Unzip Cloud_21c_labs.zip.


[opc@xx ~]$ sudo su - oracle
[oracle@xx ~]$ unzip /home/oracle/Cloud_21c_labs_for_practices.zip
Archive:  Cloud_21c_labs_for_practices.zip
   creating: labs/
   creating: labs/M104786GC10/
  inflating: labs/M104786GC10/startup.sql
  inflating: labs/M104786GC10/open_keystore_set_key.sql
  inflating: labs/M104786GC10/hr_cre.sql
...
[oracle@xx ~]$

Step 4 : Update the practices scripts to the current environment

Execute the /home/oracle/labs/update_pass.sh shell script. The shell script prompts you to enter the password_defined_during_DBSystem_creation and sets it in all shell scripts and SQL scripts that will be used in the practices.

Make the script readable, writable, and executable by everyone.


[oracle@xx ~]$ sed -i -e "s/\r//g" /home/oracle/labs/update_pass.sh
[oracle@xx ~]$ sed -i -e "s/\n//g" /home/oracle/labs/update_pass.sh
[oracle@xx ~]$ chmod 777 /home/oracle/labs/update_pass.sh
[oracle@xx ~]$

Run the script.


[oracle@xx ~]$ /home/oracle/labs/update_pass.sh
Enter the password you set during the DBSystem creation: password_defined_during_DBSystem_creation
[oracle@xx ~]$