Halimdba

Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html

Thursday, September 10, 2015

emctl is not working in oracle 12c and How to manually configure EM Express

From oracle 12c, there is no command like “emctl start dbconsole” because, in 12c there is no DBCONSOLE ( no middle-tier for EM). In replace Oracle has introduced EM Express.

What is EM Express and its Architecture?

Oracle Enterprise Manager Database Express, also referred to as EM Express, is a web-based tool for managing Oracle Database 12c. Built inside the database server, it offers support for basic administrative tasks such as storage and user management, and provides comprehensive solutions for performance diagnostics and tuning..

EM Express is designed to be lightweight and to incur minimal overhead on the database server. In order to achieve this goal, EM Express is built inside the Oracle Database and only uses internal infrastructure components. . such as XDB and SQL*Net. It does not require any separate middle-tier components.

Since EM Express is built inside the database, the database has to be open in order to use EM Express, and EM Express cannot perform actions outside the database.

EM Express does not have background tasks or processes that periodically collect information. Instead, it utilizes data that is already collected by the database. Data is requested only when the user interacts with the UI and all UI processing is done in the browser, thus minimizing load on the database server.

By default EM Express will install by DBCA while you will create a database in oracle 12C, you just need invoke it via

https://full-database-hostname:portnumber/em/

for this you need to know the port number.

How can I find the HTTP/HTTPS port for EM Express

from SQL*Plus:

SQL> select dbms_xdb.getHttpPort() from dual; ---http port

GETHTTPPORT
-----------
5500

SQL> select dbms_xdb_config.getHttpsPort() from dual;--https

GETHTTPSPORT
------------
5500

Privilige for EM express

EM_EXPRESS_BASIC or EM_EXPRESS_ALL role

SQL> grant EM_EXPRESS_BASIC to scott ;

By default EM Express is installed by DBCA when you will create a database but still you can configure your OWN. Please see in below.

Manually configure the EM Express in Oracle 12c

Configure and start the Oracle Listener.

2) Assign local_listener parameter value ( If listener is running on a nonstandard port. for example, not 1521)

The local_listener entry references a TNSNAMES entry that points to the correct listener.

So you can set it like :

SQL> alter system set

local_listener = orcl ; --(orcl is the
tnsnames)

or

SQL>
 alter system set local_listener =
'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host_name)(PORT=1526))

(CONNECT_DATA=(SERVICE_NAME=service_name)(SERVER=DEDICATED)))' ;

Enable the TCP dispatcher by adding the following entry to the init.ora file for the database you want to manage using EM Express:

dispatchers="(PROTOCOL=TCP)(SERVICE=ORCLXDB)" ---here ORCL is the SID name .

SQL> alter system set dispatchers="(PROTOCOL=TCP)(SERVICE=ORCLXDB)" ;

Then Restart the database so that the changes made can take effect.

Set the HTTPS/HTTP port for EM Express

Connect with SYS then,

For a Non-Container Database :

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5500);

For Container Database:

SQL> alter session set container=CDB$ROOT;

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5507);

For PDB (plug-gable Database)

SQL> alter session set container=PDB1;

SQL> exec DBMS_XDB_CONFIG.SETHTTPSPORT(5508);

6) Use the following command to confirm that the port has registered with the listener:

cmd> lsnrctl status | grep -i 5507 ---(port number)

7) For accessing to EM express

https://full_database-hostname:portnumber/em/

For example:

https://myserver.test.com:5500/em/

Cheers...Halim

Thursday, July 9, 2015

Daily Oracle DBA Checklist - For Production

Daily Production Oracle DBA checklist

Here I’ve mentioned lot of Daily activities of a production Oracle DBA (based on my experience). Actually it totally depends on your Database’s characteristics, Size of Database, environment etc. you may have something more to check. So please keep in mind.

And here I am telling you to check that’s mean not to check all by manually. It will take lot of times. DBAs should try to avoid manually checking anything daily or weekly. Maximum checks should be automated, and should alert the DBA only if there is an error (by email, SMS). But for these, DBA need’s good knowledge on shell scripting, PowerShell, DOS, PL/SQL, OEM or other monitoring tools.

Here are the (in general) Daily Production DBA job checklist-

1) Checking all instances status, Make sure all are up and running

How: Log in to each instance, run any query to make sure databases are available and running. Such as

$ export ORACLE_SID=orcl

$ sqlplus /nolog

SQL> select host_name from v$instance ;

2) Check alert log entries for any error or any new things (at least daily two times for all databases)

i) When you enter into office

ii) When you are leaving office

And when any complain comes about database slowness or anything

3) Verify success of databases RMAN/export/any other backups.

i) Check the RMAN/export logs for any error.

ii) Check the backup size for confirmation and daily backup size growth.

4. Verify success of database backup archiving to tape (or let system admin know to back it up.)

5) Verify Auto control file backup successful

6) Verify success of all schedule job

7) Verify enough spaces are there for -

i) Space of Tablespaces (if autoextend = no)

ii) Space in OS Hard disk/ Storage LUN

iii) Archive log space/ flash_recovery_area locations

8) Check to all objects status to make sure all are 'VALID'

9) If you have a Standby Database, check the status of standby database and gap between primary.

10) Review contention for CPU, memory, network, and disk resources.

In linux can use - top, netstat, vmstat etc. commands

In Sun Solaris can use – prstat, vmstat ,netstat etc.

10) Checks for blocking locks , killed sessions etc.

11) Check connected sessions (average count) to verify there are no abnormalities (sometimes parallel thread exceeds your process parameter’s value)

i) Check top session’s information.

ii) Top SQL statements

12) Troubleshooting if any issue reported

13) Spend sometimes to increase and upgrade your knowledge

Oh, Next time I will try to write with how to do all checks...(Automated way)

Cheers.... Halim

Saturday, June 13, 2015

How to deinstall or remove Oracle Database Software (Binaries) from server/machine/laptop!

Here I have showed in Windows but you can use same steps for Linux or Unix etc.

Summary :

Please open CMD as a Administrator (right button click on CMD icon then Open as a Administrator)

1) Go to the Oracle_home path or directory

Example of oracle_home dir, C:\app\product\11.2.0\db_1\

like : CMD> cd C:\app\product\11.2.0\db_1 (enter)

2) Go to deinstall folder like below

C:\app\product\11.2.0\db_1> deinstall (enter)

3) Execute the deinstall ( windows batch file) like below

C:\app\product\11.2.0\db_1\deinstall> deinstall (enter)

4) Then follow instruction and proceed

5) After execution the script, restart the machine the remove the oracle installation folders .

Practical example: -

Microsoft Windows [Version 6.1.7601]

C:\Windows\system32>

C:\Windows\system32>cd C:\app\product\11.2.0\db_1\deinstall

C:\app\product\11.2.0\db_1\deinstall>

C:\app\product\11.2.0\db_1\deinstall>deinstall

Checking for required files and bootstrapping ...

Please wait ...

14 File(s) copied

610 File(s) copied

1 file(s) copied.

Location of logs C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\

logs\

############ ORACLE DEINSTALL & DECONFIG TOOL START ############

######################## CHECK OPERATION START ########################

Install check configuration START

Checking for existence of the Oracle home location C:\app\product\11.2.0\db_1

Oracle Home type selected for de-install is: SIDB

Oracle Base selected for de-install is: C:\app

Checking for existence of central inventory location C:\Program Files\Oracle\Inv

entory

Install check configuration END

Checking Windows and .NET products configuration START

The following Windows and .NET products will be deconfigured from the Oracle hom

e : asp.net,ode.net,odp.net,ntoledb,oramts

Checking Windows and .NET products configuration END

Network Configuration check config START

Network de-configuration trace file location: C:\Users\my\AppData\Local\Temp\Ora

Deinstall2015-06-13_12-59-45\logs\netdc_check3782178887990698751.log

Specify all Single Instance listeners that are to be de-configured [LISTENER]:

Network Configuration check config END

Database Check Configuration START

Database de-configuration trace file location: C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\

logs\databasedc_check1200720910770180178.log

Use comma as separator when specifying list of values as input

Specify the list of database names that are configured in this Oracle home [HALIMDB]:

###### For Database 'HALIMDB' ######

Single Instance Database

The diagnostic destination location of the database: C:\APP\diag\rdbms\halimdb

Storage type used by the Database: FS

Database file location: C:\APP\ORADATA\HALIMDB,C:\APP\FLASH_RECOVERY_AREA\HALIMDB

Flash recovery area location: C:\app\flash_recovery_area\HALIMDB

database spfile location: C:\APP\PRODUCT\11.2.0\DB_1\DATABASE\SPFILEHALIMDB.ORA

The details of database(s) HALIMDB have been discovered automatically. Do you still want to modify the details

of HALIMDB database(s)? [n]:

Database Check Configuration END

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\logs

\emcadc_check.log

Checking configuration for database HALIMDB

Enterprise Manager Configuration Assistant END

Oracle Configuration Manager check START

OCM check log file location : C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\logs\\ocm_check32

69.log

Oracle Configuration Manager check END

######################### CHECK OPERATION END #########################

####################### CHECK OPERATION SUMMARY #######################

Oracle Home selected for de-install is: C:\app\product\11.2.0\db_1

Inventory Location where the Oracle home registered is: C:\Program Files\Oracle\Inventory

The following Windows and .NET products will be deconfigured from the Oracle home : asp.net,ode.net,odp.net,nt

oledb,oramts

Following Single Instance listener(s) will be de-configured: LISTENER

The following databases were selected for de-configuration : HALIMDB

Database unique name : HALIMDB

Storage used : FS

Will update the Enterprise Manager configuration for the following database(s): HALIMDB

No Enterprise Manager ASM targets to update

No Enterprise Manager listener targets to migrate

Checking the config status for CCR

Oracle Home exists with CCR directory, but CCR is not configured

CCR check is finished

Do you want to continue (y - yes, n - no)? [n]: y

A log of this session will be written to: 'C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\logs

\deinstall_deconfig2015-06-13_01-00-08-PM.out'

Any error messages from this session will be written to: 'C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-1

3_12-59-45\logs\deinstall_deconfig2015-06-13_01-00-08-PM.err'

######################## CLEAN OPERATION START ########################

Enterprise Manager Configuration Assistant START

EMCA de-configuration trace file location: C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\logs

\emcadc_clean.log

Updating Enterprise Manager Database Control configuration for database HALIMDB

Updating Enterprise Manager ASM targets (if any)

Updating Enterprise Manager listener targets (if any)

Enterprise Manager Configuration Assistant END

Database de-configuration trace file location: C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\

logs\databasedc_clean2337324818903439886.log

Database Clean Configuration START HALIMDB

This operation may take few minutes.

Database Clean Configuration END HALIMDB

Network Configuration clean config START

Network de-configuration trace file location: C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\l

ogs\netdc_clean1302827479082610407.log

De-configuring Single Instance listener(s): LISTENER

De-configuring listener: LISTENER

Stopping listener: LISTENER

Listener stopped successfully.

Deleting listener: LISTENER

Listener deleted successfully.

Listener de-configured successfully.

De-configuring Listener configuration file...

Listener configuration file de-configured successfully.

De-configuring Naming Methods configuration file...

Naming Methods configuration file de-configured successfully.

De-configuring Local Net Service Names configuration file...

Local Net Service Names configuration file de-configured successfully.

De-configuring backup files...

Backup files de-configured successfully.

The network configuration has been cleaned up successfully.

Network Configuration clean config END

Oracle Configuration Manager clean START

OCM clean log file location : C:\Users\my\AppData\Local\Temp\OraDeinstall2015-06-13_12-59-45\logs\\ocm_clean32

69.log

Oracle Configuration Manager clean END

Removing Windows and .NET products configuration START

Removing Windows and .NET products configuration END

Oracle Universal Installer clean START

Removing service 'OracleOraDb11g_home1ClrAgent' on the local node : Done

Detach Oracle home 'C:\app\product\11.2.0\db_1' from the central inventory on the local node : Done

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oracell11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraclient11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oracommon11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oracore11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orageneric11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orahasgen11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraldapclnt11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oran11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orancds11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orancrypt11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oranhost11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oranl11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oranldap11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oranls11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orannzsbb11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oranro11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orantcp11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orantns11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraocr11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraocrb11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraocrutl11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraperf.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraperf11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraplp11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orapls11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\ORASLAX11.DLL'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orasnls11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orasql11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraunls11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orauts.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oravsn11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraxml11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\orazt11.dll'. The file is in use.

Failed to delete the file 'C:\app\product\11.2.0\db_1\BIN\oraztkg11.dll'. The file is in use.

Failed to delete the directory 'C:\app\product\11.2.0\db_1\BIN'. The directory is not empty.

Failed to delete the file 'C:\app\product\11.2.0\db_1\dbs\perf11.ora'. The file is in use.

Failed to delete the directory 'C:\app\product\11.2.0\db_1\dbs'. The directory is not empty.

Failed to delete the directory 'C:\app\product\11.2.0\db_1\deinstall'. The directory is in use.

Failed to delete the directory 'C:\app\product\11.2.0\db_1'. The directory is not empty.

Delete directory 'C:\app\product\11.2.0\db_1' on the local node : Failed <<<<

Delete directory 'C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Oracle - OraDb11g_home1' on the local n

ode : Done

Removing oracle home 'C:\app\product\11.2.0\db_1' from PATH variable on the local node : Done

Delete directory 'C:\Program Files\Oracle\Inventory' on the local node : Done

Delete directory 'C:\Program Files\Oracle' on the local node : Done

Delete Registry key 'HKEY_LOCAL_MACHINE\Software\Oracle\inst_loc' on the local node : Done