DB - Setup the environment
Quick Summary
This post was initially written for 11g - how to setup the database environment to start the database instance. Post revised for 12c, in this case added some validations related with new functionalities related with conatiner databases .
Some assumptions, the database was created with dbca , or if manually the installation was completed with the /etc/oratab registration.
The easiest way to setup the environmnt variables is to run ". oraenv" in command line , let see the output:
11g - Setup environment variables
[oracle@oracle ~]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base for ORACLE_HOME=/u01/app/oracle/Database/11.2.0/orcl is /u01/app/oracle
[oracle@oracle ~]$ echo $ORACLE_SID
orcl11g - Start the server
Start the database instance, mount and open the database
[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Dec 19 20:02:16 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 577511424 bytes
Fixed Size 1338000 bytes
Variable Size 360711536 bytes
Database Buffers 209715200 bytes
Redo Buffers 5746688 bytes
Database mounted.
Database opened.11g - Network
Start the listener to make the database available in the network
[oracle@oracle ~]$ lsnrctl start;
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAR-2019 17:53:48
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Starting /u01/app/oracle/Database/11.2.0/orcl/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/Database/11.2.0/orcl/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-MAR-2019 17:53:48
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/Database/11.2.0/orcl/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@oracle ~]$ lsnrctl status;
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 17-MAR-2019 17:54:05
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.example.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 17-MAR-2019 17:53:48
Uptime 0 days 0 hr. 0 min. 16 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/Database/11.2.0/orcl/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/oracle/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle.example.com)(PORT=1521)))
The listener supports no services
The command completed successfully11g - Quick Checks
After start the database, it possible to check the database and the instance through the views v$instance and v$database.
SQL> select name, OPEN_MODE from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL> col STARTUP_TIME form a30
SQL> select INSTANCE_NAME,STARTUP_TIME,STATUS from v$instance
INSTANCE_NAME STARTUP_TIME STATUS
---------------- ------------------------------ ------------
orcl 17-MAR-19 OPENNow, 12c - Setup das variáveis de ambiente
It's the same as previous to run ". oraenv"
[oracle@wls1 ~]$ . oraenv
ORACLE_SID = [orcl] ?
The Oracle base remains unchanged with value /u01/app/oracle
oracle@wls1 ~]$ type oraenv
oraenv is /u01/app/oracle/product/12.1.0/db_1/bin/oraenv
[oracle@wls1 ~]$ file /u01/app/oracle/product/12.1.0/db_1/bin/oraenv
/u01/app/oracle/product/12.1.0/db_1/bin/oraenv: POSIX shell script, ASCII text executable
oracle@wls1 ~]$ echo $ORACLE_SID
orcl
[oracle@wls1 ~]$ echo $ORACLE_HOME
/u01/app/oracle/product/12.1.0/db_1 12c - Start the server .. still's the same as previous, .. or almost
and it is if for the container or non-container database , I will explain this a further.
[oracle@wls1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 17 16:07:05 2019
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup;
ORACLE instance started.
Total System Global Area 1157627904 bytes
Fixed Size 2923632 bytes
Variable Size 738198416 bytes
Database Buffers 402653184 bytes
Redo Buffers 13852672 bytes
Database mounted.
Database opened.12c - Quick check , same views : v$instance e v$database ..
Looks like the same
SQL> select name, OPEN_MODE from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL READ WRITE
SQL> col STARTUP_TIME form a30
SQL> select INSTANCE_NAME,STARTUP_TIME,STATUS from v$instance;
INSTANCE_NAME STARTUP_TIME STATUS
---------------- ------------------------------ ------------
orcl 17-MAR-19 OPEN12c - Pluggable's databases
Here is the new stuff, within 12c Version Oracle introduced the concept of Multitenant Architecture.
In short words, the Oracle conventional database, became Container Database (CDB) or Non- Container Database (non-CDB). The Container Database (CDB) can hold subsets knows as Pluggable Database (PDB)". It's possible to find info about this in some views such : v$containers;
It this case is need to check and open the pluggable databases, below how to action it.
SQL> set lines 200
SQL> select CON_ID,DBID,NAME, OPEN_MODE, OPEN_TIME from v$containers;
CON_ID DBID NAME OPEN_MODE OPEN_TIME
---------- ---------- --------- ----------- --------------------------------
1 1519088518 CDB$ROOT READ WRITE 17-MAR-19 04.07.20.073 PM +00:00
2 1452428368 PDB$SEED READ ONLY 17-MAR-19 04.07.20.074 PM +00:00
3 1091406587 PDB01 MOUNTED
4 1706722041 MW_PDB MOUNTED
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
SQL> select CON_ID,DBID,NAME, OPEN_MODE, OPEN_TIME from v$containers;
CON_ID DBID NAME OPEN_MODE OPEN_TIME
--------- ---------- --------- ---------- ----------------------
1 1519088518 CDB$ROOT READ WRITE 17-MAR-19 04.07.20.073 PM +00:00
2 1452428368 PDB$SEED READ ONLY 17-MAR-19 04.07.20.074 PM +00:00
3 1091406587 PDB01 READ WRITE 17-MAR-19 04.10.59.826 PM +00:00
4 1706722041 MW_PDB READ WRITE 17-MAR-19 04.10.59.827 PM +00:0012c - Ahh and about Network
To start the listener it's the same as in 11g , I only start it now to show another difference , in Multitenant Architecture the PDBs have the own service. Notice that when check the listerner status service.
[oracle@wls1 ~]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-MAR-2019 17:20:56
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/wls1/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wls1.mydomain)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wls1.mydomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 17-MAR-2019 17:20:56
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/wls1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wls1.mydomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@wls1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 17-MAR-2019 17:28:19
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=wls1.mydomain)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 17-MAR-2019 17:20:56
Uptime 0 days 0 hr. 7 min. 23 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/wls1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=wls1.mydomain)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=wls1.mydomain)(PORT=5500))(Security=(my_wallet_directory=/u01/app/oracle/admin/orcl/xdb_wallet))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "mw_pdb.mydomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl.mydomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.mydomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "pdb01.mydomain" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
Comments