Oracle: DBA's day out

Create Database Using DBCA In Oracle 11G

Create Database Using DBCA In Oracle 11G
This is the simplest way to create database in oracle database. Follow below given steps to create database.

First login using oracle system user using GUI Console and then execute dbca command

Now Select Create Database

Select Type Of Database You want to create

Now give Database Name SID name will be same as database name.

Select appropriate option

Create Listener if you get this Error or Warning

Give Password you want

This is just password policy warning can configured as per your need

Now Select Database File Location

Check file location using File Location Variables

Now specify flash recovery area

Check if you want sample schema

Now give Memory Details etc

Click Next

Now select Finish

Now Installation will start

Now Select EXIT

Now Database is created . You can login to database and verify

Now Database is created properly

Original post - link

Create Database Manually In Oracle 11G On Linux

Create Database Manually In Oracle 11G On Linux

Here we will explore how to create database manually in Oracle 11G. Although there is a utility available called as DBCA using which you can create database with ease but i will recommend you to create it manually doing so we will know where we have kept all the configuration files etc.

Below are some highlights of steps involved in creating database manually.

1. create pfile , password file for new database
2. Create necessary directories
3. create instance and start database in nomount mode
4. use create database to create new database
5. Run necessary scripts file to create data dictonary table
6. Testing newly created database and registering to listener

First Login as ORACLE OS USER.

Now goto $ORACLE_HOME/dbs location and copy the content of init.ora into file name you want to create

Now open initzahid.ora and make the necessary changes as per your need.

Now as per changes you have made. make sure you have created necessary folders given in the path. if not then create.

Now goto path and check if the necessary folders has been created.

Now open /etc/oratab file and make entry of database name and $ORACLE_HOME location.

Now set the environment path for zahid database. you can simply do it by executing oraenv command.

Now create a password file for sys user.

Once password file is created you can login to database and check.

Now you can start listener services if you want else it can be configure later also.

Now login to database and start the database using pfile you have created.

Now you can create spfile from pfile if you want. Major different using spfile over pfile is that you can make online changes in configuration file how ever in pfile you have to restart database for any global changes.

Now check if the spfile has been created or not.

Now all the configuration part has been completed Now you can execute create database command.I have created a script to create database you can execute this command on sql prompt also.

Now login to oracle and execute zahid.sql file to create database.

Now database has been created

Now import various catalog file to database

Once these catalogs are imported you can check if the database is mounted properly and it is opened in read write mode or not

Now you can once restart database services and test

Now come out of database and check if all the processes are running properly or not.

Since all the processes are running properly Database has been created successfully.

Original post -Link

OEM: The database is down. Please specify the host credentials to access database restart and diagnostics tools.

OEM: The database is down. Please specify the host credentials to access database restart and diagnostics tools

We recently faced this issue on "Oracle Enterprise manager" on one of our testing environments

Oracle Enterprise manager is showing following message as in above image:
The database is down. Please specify the host credentials to access database restart and diagnostics tools.

I debugged it with following steps

1) Check Database is open or Not

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 02:35:33 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select status from v$instance;
STATUS
------------
OPEN

2) Check listener is running properly

C:\Windows\system32>lsnrctl status
LSNRCTL for 64-bit Windows: Version 11.2.0.4.0 - Production on 06-FEB-2015 02:36:16
Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mydbhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.4.0 - Production
Start Date                05-FEB-2015 14:11:28
Uptime                    0 days 12 hr. 24 min. 47 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   E:\myorcl\Database\myorclDB\NETWORK\ADMIN\listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydbhost)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status READY, has 1 handler(s) for this service...
Service "myorcl" has 1 instance(s).
  Instance "myorcl", status READY, has 1 handler(s) for this service...
Service "myorclXDB" has 1 instance(s).
  Instance "myorcl", status READY, has 1 handler(s) for this service...
The command completed successfully

3) check status of emctl

C:\Windows\system32>emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://mydbhost:5500/em/console/aboutApplication

Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory 
E:\myorcl\Database\myorclDB/mydbhost/sysman/log

4) check emoms.log logs
E:\myorcl\Database\myorclDB\mydbhost_myorcl\sysman\log\emoms.log

java.sql.SQLException: ORA-28000: the account is locked

5) Check Account Status of SYSTEM, SYSMAN and DBSNMP users

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 02:37:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select username, account_status from dba_users where username in ('SYSTEM','SYSMAN','DBSNMP');

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------
DBSNMP                         OPEN
SYSTEM                         OPEN
SYSMAN                         LOCKED(TIMED)

By above steps I was sure that Database was functioning normally, the problem was with SYSMAN user being locked. Oracle use SYSMAN user as a default super-user account to administrator Oracle Enterprise manager and stores the Oracle Management Repository in SYSMAN schema. SYSMAN password is stored in encrypted format in files which are used by OEM, We need to unlock SYSMAN user and change password of SYSMAN.

I use following Oracle documentation to change the password of SYSMAN
https://docs.oracle.com/cd/B19306_01/em.102/b40002/repository.htm#i1029558

Here are the steps I follow to resolve the issue

1) Stop Enterprise Manager Database Console

C:\Windows\system32>emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://mydbhost:5500/em/console/aboutApp
lication
The OracleDBConsolemyorcl service is stopping..........
The OracleDBConsolemyorcl service was stopped successfully.

2) Connect to SYS user as SYSDBA

C:\Windows\system32>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Feb 6 02:37:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

3) Unlock the SYSMAN account and change password

SQL> alter user sysman account unlock;
User altered.

SQL> alter user SYSMAN identified by "NEW_SYSMAN_PASSWORD";
User altered.

4) locate and open the emoms.properties configuration file, in our case it was at
E:\myorcl\Database\myorclDB\mydbhost_myorcl\sysman\config\emoms.properties

5) change following entries value in the "emoms.properties" file:

    orcle.sysman.eml.mntr.emdRepPwd=NEW_SYSMAN_PASSWORD (in plain text)
    orcle.sysman.eml.mntr.emdRepPwdEncrypted=FALSE (true to false)

6) In some cases you may need to edit "targets.xml" file too, which is located at
E:\myorcl\Database\myorclDB\mydbhost_myorcl\sysman\emd\targets.xml

7) change following entries value in the "targets.xml" file:

        <Property NAME="UserName" VALUE="SYSMAN" ENCRYPTED="FALSE"/>
        <Property NAME="password" VALUE="NEW_SYSMAN_PASSWORD" ENCRYPTED="FALSE"/>

8) Start Enterprise Manager Database Console

C:\Windows\system32>emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation.  All rights reserved.
https://mydbhost:5500/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ...The OracleDBConsolemyorcl service is starting..................
The OracleDBConsolemyorcl service was started successfully.

All Done, and Oracle Enterprise Manager was not running properly :)

I really hope you all have enjoyed and learnt/revisited something from this demonstration on the OEM issue and how to change SYSMAN password. Don't forget to put your feedback as comments, they are really valuable.

How to clear server cache

How to clear server cache

Whenever you bounce the apache server, you need to clear the server side cache, so that those cached pages will not be used again.(these are code cache not data). The cache files are stored under $OA_HTML/_pages/_oa__html folder in the web/apps node.

1. Stop the apache listener - see below for command
2. Clear Cache

You do this by changing to directory $OA_HTML/_pages/_oa__html (for 11.5.5 - 11.5.9) or $COMMON_TOP/_pages/_oa__html (for 11.5.10) and then issue:

rm -rf *

Take a backup before removing.

3. Start the apache listener - see below for command

Start and Stop the Apache http listener
The 11i Rapid install provides a start/stop script for the Apache server.
This script is located in COMMON_TOP/admin/scripts and is called adapcctl.sh.

To start the Apache http listener processes, log on as the user that installed Oracle Applications (single node) or the user that installed the Oracle Applications web tier (multi-node). Change directories to $COMMON_TOP/admin/scripts and type the following:

adapcctl.sh start (to start the Apache http listener processes)
adapcctl.sh stop (to stop the Apache http listener processes)

Copy table from one database to another

Copy table from one database to another

Login as SYS

SQL> copy from scott/tiger@orcl to exam2/exam2@orcl create new_emp using select * from emp where 1=2;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table NEW_EMP created.

   0 rows selected from scott@orcl.
   0 rows inserted into NEW_EMP.
   0 rows committed into NEW_EMP at exam2@orcl.

SQL>