Skip to main content

DB - Setup the environment


Versao Portuguesa





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
orcl




11g - 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 successfully




11g - 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 OPEN




Now, 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 OPEN




12c - 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:00




12c - 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

Labels:

Comments

Post a Comment

Popular posts from this blog

DB - How to monitor Oracle datapump jobs

These days there is lot of work around database migrations not only but mainly to cloud providers. One of the ways to migrate Oracle databases is using data pump (expdp/impdp). In general works fine, it allows an easy way to bring the database even different OS. It is always good to monitor the progress, to know where we are and estimate how long we are from the end. In this post, I am sharing some queries to help with database monitoring progress. The queries can be easily tailed to various scenarios. Starting with a simple one - the track the number of objects loaded during an import operation. When there is a massive amount of errors during the import. Drop and run the import again. The bellow query can be useful also to monitor the number of objects if we are dropping the users. Here, I’m making the assumption there were no other database activity in progress on the last 24 hours. select owner, count(0) from dba_objects where owner in ( select username from dba_users where created...
Post a Comment
Read more

VM - Partilhar Pastas Windows - Linux

Neste post escrevo como partilhar pastas entre Windows (Host) e uma VM Guest, neste caso Linux. Ha algumas formas de fazer isto, esta parece me a mais facil. Seleccionar a Pasta Windows Na consola Virtual Box > Devices > Shared Folder Seleccionar a pasta windows para partilhar no Linux   O   Montar a pasta Windows na VM Ha duas opcoes : 1) Reiniciar a VM (Auto-mount) 2) Linha de comando [root@host ~]# mkdir -p /u01/stage/win_ebs_sw [root@host ~]# chmod 777 /u01/stage/win_ebs_sw [root@host ~]# mount -t vboxsf Oracle_EBS /u01/stage/win_ebs_sw/ [root@host ~]# ls /u01/stage/win_ebs_sw ebs_weblogic_webtier [root@host ~]# df -h /u01/stage/win_ebs_sw Filesystem Size Used Avail Use% Mounted on Oracle_EBS 895G 494G 401G 56% /u01/stage/win_ebs_sw Guardar a Configuracao no /etc/fstab para tornar as alteracoes permanentes. [root@host ~]# echo "Oracle_EBS /u01/stage/win_ebs_sw vboxsf defaults 0 0" >> /etc/fstab [root@host ~]# cat /etc/fstab # /etc/...
Post a Comment
Read more

DB – How to monitor Oracle database long ops ?

As DBAs, some questions we are often asked and to be fair we ask ourselfs when we are doing massive operations, are : Is the database doing something? How long the database takes to complete the task? There a data dictionary view, GV$SESSION_LONGOPS whom is really usefull for monitoring and to provide estimated time to get the task done. Starting with a first example, a query for general propose . set lines 240 col message form a100 col opname form a50 select * from ( select round(l.sofar/l.totalwork*100,2) as complete, --l.* l.sid, --l.opname, l.message, l.start_time, l.time_remaining/60 "minuts remaining", l.elapsed_seconds from gv$session_longops l where totalwork !=0 ) where complete < 100; The view is also usefull on specific operations such as Oracle Recovery Manager (RMAN) jobs monitoring, this query returns information about backup,restore and recovery. select sid, serial#,opname, context, sofar, totalwork, round(sofar/totalwork*100,2) "%_complet...
Post a Comment
Read more