DB - Setup the environment
This post was made initially to cover - how to setup an Oracle 11g Database - is reviewed for 12c - were added extra validations.
11g - Setup the database environment
Taking the assumption the database was create using the dbca. And/or the databases are registered in the /etc/oratab
The Standard way it to step-up the environment variables is run the . oraenv
The below code shows the how to setup and commands output.
The below code shows the how to setup and commands output.
[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
orcl
[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.
Validation
We can have a quick validation on 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 OPEN
Start Listener
We cannot forget to start the listener.
[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 successfully
12c - Setup the database environment
For 12c the process is similar - with an extra step if we have PDBs (pluggable databases)
[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
[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.
Validation
We can have a quick validation on 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 OPEN
Pluggable's
If we have pluggable databases we will need to start them as well
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:00
Start Listener
We cannot forget to start the listener - we can see the PDBs
[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
Thank you for reading, hope this post was helpful.
Rogerio
Comments