how to use opatch auto

> OPatch auto automaticaly patch the typical Grid Infrastructure (GI) and RAC home directories with minimal intervention.
> OPatch auto performs many of the pre-patch checks as well as the post-patch verification.
> OPatch auto lies in its ability to perform end-to-end configuration patching (patching a GI or RAC home based on its configuration.) By incorporating the configuration information into the patch process.
> OPatch auto streamlines patching tasks by automating most of the steps.
> OPatch auto uses your GI/RAC configuration and, from that information, automatically generates patching instructions specific to your site configuration.
> OPatch auto then uses OPatch to implement these instructions and perform the actual application of the patch.

 -------------------------------- Create Response file -----------------------------------------

[oracle@v-box1 OPatch]$ sudo su -

[root@v-box1 ~]# export ORACLE_HOME=/u01/app/11.2.0.4/grid

[root@v-box1 ~]# $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /opt/software/gridocm_config.rsp

Provide your email address to be informed of security issues, install and

initiate Oracle Configuration Manager. Easier for you if you use your My

Oracle Support Email address/User Name.

Visit http://www.oracle.com/support/policies.html for details.

Email address/User Name:

You have not provided an email address for notification of security issues.

Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]:  Y

The OCM configuration response file (/opt/software/gridocm_config.rsp) was successfully created.

 -------------------------------- Patch GI home -----------------------------------------

[root@v-box1 ~]# cd /opt/software/19380115

[root@v-box1 19380115]# sudo /u01/app/11.2.0.4/grid/OPatch/opatch auto /opt/software/19380115 -oh /u01/app/11.2.0.4/grid/ -ocmrf /opt/software/gridocm_config.rsp

Executing /u01/app/11.2.0.4/grid/perl/bin/perl /u01/app/11.2.0.4/grid/OPatch/crs/patch11203.pl -patchdir /opt/software -patchn 19380115 -oh /u01/app/11.2.0.4/grid/ -ocmrf /opt/software/gridocm_config.rsp -paramfile /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2015-07-16_04-39-48.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:

/u01/app/11.2.0.4/grid/cfgtoollogs/opatchauto2015-07-16_04-39-48.report.log

2015-07-16 04:39:48: Starting Oracle Restart Patch Setup

Using configuration parameter file: /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

Stopping CRS...

Stopped CRS successfully

patch /opt/software/19380115/19121551  apply successful for home  /u01/app/11.2.0.4/grid

patch /opt/software/19380115/19121549  apply successful for home  /u01/app/11.2.0.4/grid

patch /opt/software/19380115/19121552  apply successful for home  /u01/app/11.2.0.4/grid

Starting CRS...

CRS-4123: Oracle High Availability Services has been started.

opatch auto succeeded.

-------------------------------- Patch Oracle Home -----------------------------------------

[oracle@v-box1 OPatch]$ sudo /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/opatch auto /opt/software/19380115 -oh /u01/app/oracle/product/11.2.0.4/dbhome_1/ -ocmrf /opt/software/gridocm_config.rsp

Executing /u01/app/11.2.0.4/grid/perl/bin/perl /u01/app/oracle/product/11.2.0.4/dbhome_1/OPatch/crs/patch11203.pl -patchdir /opt/software -patchn 19380115 -oh /u01/app/oracle/product/11.2.0.4/dbhome_1/ -ocmrf /opt/software/gridocm_config.rsp -paramfile /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatchauto2015-07-16_05-02-44.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:

/u01/app/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatchauto2015-07-16_05-02-44.report.log

2015-07-16 05:02:44: Starting Oracle Restart Patch Setup

Using configuration parameter file: /u01/app/11.2.0.4/grid/crs/install/crsconfig_params

Stopping RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 ...

Stopped RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 successfully

patch /opt/software/19380115/19121551  apply successful for home  /u01/app/oracle/product/11.2.0.4/dbhome_1

patch /opt/software/19380115/19121549/custom/server/19121549  apply successful for home  /u01/app/oracle/product/11.2.0.4/dbhome_1

Starting RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 ...

Started RAC /u01/app/oracle/product/11.2.0.4/dbhome_1 successfully

opatch auto succeeded.

[oracle@v-box1 OPatch]$

------------------------------------------------------------------------------------------------

Moving Spfile from File System to ASM Disk

[oracle@vbox-01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 15 08:00:15 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, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0.4/dbhome_1/dbs/spfilefaizal.ora

SQL> create pfile='/home/oracle/initfaizal.ora' from spfile;

File created.

SQL> create spfile='+DATA' from pfile='/home/oracle/initfaizal.ora';

File created.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
 Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

[oracle@vbox-01 ~]$ cd $ORACLE_HOME/dbs
[oracle@vbox-01 dbs]$ ls -ltr *faizal*

-rw-r----- 1 oracle oinstall 1536 Jul 15 07:23 orapwfaizal
-rw-r----- 1 oracle oinstall 4608 Jul 15 07:48 spfilefaizal.ora
-rw-rw---- 1 oracle oinstall 1544 Jul 15 07:57 hc_faizal.dat
[oracle@vbox-01 dbs]$
[oracle@vbox-01 dbs]$ mv spfilefaizal.ora spfilefaizal.ora.bck

[oracle@vbox-01 dbs]$ export ORACLE_SID=+ASM

ASMCMD> ls -ltr

WARNING:option 'r' is deprecated for 'ls'

please use 'reverse'

 

Type           Redund  Striped  Time             Sys  Name

PARAMETERFILE  UNPROT  COARSE   JUL 15 08:00:00  Y    spfile.313.885110543

ASMCMD> pwd

+data/FAIZAL/PARAMETERFILE

# you must give init.ora not spfile.ora

[oracle@vbox-01 dbs]$ vi initfaizal.ora 
[oracle@vbox-01 dbs]$
[oracle@vbox-01 dbs]$ cat initfaizal.ora
spfile='+DATA/faizal/parameterfile/spfile.313.885110543'

[oracle@vbox-01 dbs]$ ls -ltr *faizal*
-rw-r----- 1 oracle oinstall 1536 Jul 15 07:23 orapwfaizal
-rw-r----- 1 oracle oinstall 4608 Jul 15 07:48 spfilefaizal.ora.bck
-rw-r--r-- 1 oracle oinstall   61 Jul 15 07:54 initfaizal.ora
-rw-rw---- 1 oracle oinstall 1544 Jul 15 07:57 hc_faizal.dat
[oracle@vbox-01 dbs]$

[oracle@vbox-01 dbs]$ export ORACLE_SID=faizal

[oracle@vbox-01 dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 15 08:05:30 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup;

ORACLE instance started.
Total System Global Area 1.2827E+10 bytes
Fixed Size                  2265224 bytes
Variable Size            6140465016 bytes
Database Buffers         6677331968 bytes
Redo Buffers                7307264 bytes
Database mounted.
Database open.

SQL> show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      +DATA/faizal/parameterfile/spfile.313.885110543

Done

 

Oracle Patch

Oracle Patch

As of 14 July 2009, Oracle is now introducing a new method for patching, patch set updates, or PSU. According to MOSC notes 854428.1 and 850471.1, Oracle PSU is a new patching strategy whereby the DBA can choose only "recommended" and "proactive" patches, instead of all of the patches in a quarterly Critical Patch Update (CPU).



An Oracle PSU contains recommended bug fixes and "proactive" cumulative patches, a nice change that makes it simple for the DBA to choose to apply "priority" patches.


The Database Patch Set Updates and Critical Patch Updates that are released each quarter contain the same security fixes. However, they use different patching mechanisms, and Patch Set Updates include both security and recommended bug fixes. Consider the following guidelines when you are deciding to apply Patch Set Updates instead of Critical Patch Updates.


• Critical Patch Updates are applied only on the base release version, for example 10.2.0.4.0.


• Patch Set Updates can be applied on the base release version or on any earlier Patch Set Update. For example, 11.1.0.7.2 can be applied on 11.1.0.7.1 and 11.1.0.7.0.


• Once a Patch Set Update has been applied, the recommended way to get future security content is to apply subsequent Patch Set Updates. Reverting from an applied Patch Set Update back to the Critical Patch Update, while technically possible, requires significant time and effort, and is not advised.


• One-off patch is specific bug fix for requested customer.




There are critical and DB patches are there and it’s applied to Oracle binaries, DB dictionary and agent services. If you are using RAC patches are available for cluster services also. Mostly it will be consolidated in critical patches which will be released every quarter. You need to have metalink access to download the patches and corresponding document. You need to read and understand carefully this document before attempting applies patches. You may try the patches on testing environment before applying in live.


Patch will be released in January, April, July and October on each year. Doc Id: 1291877.1

imp partition table in different schema

Query I used in par file



file='/oracle/export/tab/tab3a.dmp'
log='/oracle/export/tab/tab3a.log'
tables=esam.summary
query="where TID IN ('010003050000','010003080000','010003090000') AND SUMMARY_PERIOD BETWEEN '01-oct-2010' AND '31-dec-2010'"


exp “'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log'

Export log


. . exporting table SAM_SUMMARY
. . exporting partition SAM_SUMMARY_04_2002 0 rows exported
. . exporting partition SAM_SUMMARY_05_2002 0 rows exported
. . exporting partition SAM_SUMMARY_06_2002 8000 rows exported


Import into different schema on different server



imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y


Import log


Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
IMP-00032: SQL statement exceeded buffer length
IMP-00008: unrecognized statement in the export file:
LESS THAN (TO_DATE(' 2010-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 0 PCTUSED 95 INITRANS 5 MAXTRANS
255 STORAGE(INITIAL 8388608 FREELISTS 3 FREELIST GROUPS 3 BUFFER_POOL DEFAULT) TABLESPACE "SAM_SUMM_DBF_2010" NOLOG...
 . importing ESAM's objects into NMAPP
Import terminated successfully with warnings.

Cause: Import utility verifies the syntax of all SQL statements in the dump file. Import utility uses default buffer size is 64k, if the SQL statement in the dump file exceeds the default buffer size, then import will be failed and raise 31 & 8 error..



Solution: increase the buffer size

imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y buffer=100000


Import log


IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'SAM_SUMM_DBF' does not exist
Import terminated successfully with warnings.

My partition table having 50 partitions in prod and each partition in different tablespace but those tablespace doesn’t exist in QA.


Solution: Here we have 2 options to done this job.

1. Create missing tablespace in target, and then run import with buffer size.


2. Reff metalink document (ID: 372992.1) to import partition tables in different tablespace


                     a. Run the import utility with show=y or indexes=filename.sql
                     b. Save the ddl to a script file
                     c. Run the script in target db..

imp "'/ as sysdba'" file='/oracle/export/tab/tab3a.dmp' log='/oracle/export/tab/tab3a.log' from user='esam' touser='nmapp' ignore=y commit=y buffer=100000



this time partition tablespace are imported successfull without any mistake..
:-) feel happy now

------------------------------->>>>>>><<<<<<<<<<<<<<<<<<<---------------------------------------

ORA-27041 in DR

Error: ORA-01186, ORA-01116, ORA-01110, ORA-27041

Overview of the problem

Tablespace was dropped on Primay and archived logs not applied to DR and the below data file was removed from OS level on DR site and below are the errors on DR alert log and DR went down and not starting..

MRP process failling with below error since it could not able to find those datafile physically on DR

ORA-01186: file 35 failed verification tests  

ORA-01116: error in opening database file 35
ORA-01110: data file 35: '/db01/oradata/warm/ASAM200901_f01.dbf'
ORA-27041: unable to open file
IBM AIX RISC System/6000 Error: 2: No such file or directory

Solution: On DR database:

a. Set standby file management from 'AUTO' to 'MANUAL'

            SQL> alter system set standby_file_management='MANUAL';

b. Drop those datafiles what ever appered in the error

SQL> alter database datafile '/db01/oradata/warm/ASAM200901_f01.dbf' offline drop;

SQL> alter database datafile '/db01/oradata/warm/ASAM200901_f02.dbf' offline drop;

c. Recover the DR database 

            SQL> alter database recover standby database;

You may see below errors in alert log and these may be ignored:

Managed Standby Recovery not using Real Time Apply

parallel recovery started with 7 processes
ORA-279 signalled during: alter database recover standby database...
Managed Standby Recovery not using Real Time Apply
MRP0: Some datafile enqueues are still held! Retry recovery...

Thu Feb 12 00:28:39 2010
Errors in file /oracle/admin/asam/bdump/asam_mrp0_770128.trc:
ORA-01124: cannot recover data file 1 - file is in use or recovery
ORA-01110: data file 1: '/db02/oraundo/system01.dbf

d. restart the mpr process..

      SQL> exit
      $ sqlplus "/as sysdba"
      SQL> alter database recover managed standby database disconnect from session;

You should be seeing below in alert log:

Thu Feb 12 00:31:03 2010
Recovery deleting file #35:'/db01/oradata/warm/ASAM200901_f01.dbf' from controlfile.
Recovery deleting file #125:'/db01/oradata/warm/ASAM200901_f02.dbf' from controlfile.
Recovery dropped tablespace 'MTL201026'
Media Recovery Log /db01/oraarch/asam020_1_632092_602170022.arc
Media Recovery Log /db01/oraarch/asam020_1_632093_602170022.arc
Thu Feb 12 00:31:15 2010

we can confirmed the MRP process restarted and we can switch back the file management to 'AUTO'.

       SQL> alter system set standby_file_management='AUTO';
       SQL> exit;
       $ ps -ef|grep mrp

 you see the MRP process..

We also have one more alternate option to fix this problem...

Create standby control file in primary and recovery the standby database, if this option doesn't work then use rman backup and start Point Of Recovery use roll forward..

ORA-00600: keltnfy-ldmInit

Problem:ORA-00600: internal error code, arguments: [keltnfy-ldmInit], [46], [1], [], [], [], [], []


Oracle 10.2.0.1.0 while startup database it fail with favourite error code ora-600.


Cause: oracle was unable to get the host info from OS.


In error msg 'ldmInit" indicates that the problem is related while getting host info of oracle during the startup.


the first argument [46] indicates the exception LDMERR_HOST_NOT_FOUND which is 'gethostbyname system call fails'.


Soultion: correct the host name by login root user.


1. su -
2. vi /etc/hosts


$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost localhost.localdomain localhost


correct the hostname to


127.0.0.1 localhost ocean.com


save and exit


3. restart the network service with -d


# /etc/rc.d/init.d/network restart


and vi /etc/sysconfig/network
replace Hostname=xyz to Hostname=ocean.com
save and exit, then restart


# service xinetd restart
#hostname
ocean.com
#ping ocean.com
PING ocean.com (127.0.0.1) 56(84) bytes of data.
64 bytes from ocean.com (127.0.0.1): icmp_seq=0 ttl=64 time=0.057 ms
64 bytes from ocean.com (127.0.0.1): icmp_seq=1 ttl=64 time=0.050 ms


4. now start the db..


it works fine for me now..

Configure Recovery Catalog - Rman

Pre-requisite

> we can use same instance for catalog db. my recommendation is separate instance is better.
> create separate schema and grant 'recovery_catalog_owner' privilege
> configure password file and listener file

1. configure recovery catalog.

$> export oracle_sid=rmandb
$> rman catalog rmanc/rmanc
...
...

rman> create catalog tablespace tbsnam;
...
rman> exit;

2. Register target db with catalog db

$> rman catalog rmanc/rmanc target sys@db1
target database password:
connected to target.....
.....

rman> register database;
database register in recovery catalog...
....
...
rman> exit

3. check and confirm the db register with catalog db

login rman schema

$ sqlplus rmanc/rmanc
$ select * from db;

you can see the target db id and incarnation information..

now target db was configured with catalog db.. next we have to configure rman for target db...

bye..

ORA-00704 bootstrap process failure

while i create fresh database using 10.2.0. i faced bootstrap failure. i was scared. most of them asked me to contact oracle. but again i tried with upgrade option and run catalog.sql and catproc.sql script, the problem was solved, now the db is working fine. But still i am not able to find the root cause why it was happen. If any one know please share the details..

ORA-00704: bootstrap process failure
ORA-39700: database must be opened with upgrade option

platform : Redhat 4

error in invoking target 'all_no_orcl ihsodbc' of makefile

I'm installing Oracle 10.2.0 on RHEL 4 ES. The Linux runs on VMWare 1.0.5. The Virtual Machine has 800 Mb of RAM, and 12Gb of hard drive space (/dev/sda).When I was running the Oracle Universal Installer, I got the following error message:

error in invoking target 'all_no_orcl ihsodbc' of makefile '/rman/oracle/product/10.2.0/rdbms/lib/ins_rdbms.mk

I was wounder what happen actually. i investigate the error log.. two library files are missing, so OUI not able to link files..

'/rman/oracle/product/10.2.0/rdbms/lib/ins_rdbms.mk'.See '/u01/app/oracle/oraInventory/logs/installActions2009-11-14_06-41-37PM.log'

solution

Install the following rpm

compat-libstdc++-296-2.96-132.7.2.i386.rpm
compat-libstdc++-33-3.2.3-47.3.i386.rpm

note: check all mandatory rpm before install..

Oracle 10g upgradation Outline

Platform: Sun Solaris 5.8
Architecture: Sun Sparc 64bit
Oracle Version: 10.1.0.2.0 64bit
Upgradation path: 10.1.0.2.0 --> 10.2.0.1.0 --> 10.2.0.4.0
How to get RDBMS: Download from OTN / Metalink

Upgradation Steps:

1. Shutdown oracle db [shutdown immediate]

2. Take complete backup [cold backup]

3. Stop all services

Listener, OEM console, Cronjobs

4. Backup oracle home and inventory files

a. $ tar -cvf 10102.tar $OH/10102

b. $ tar –cvf Inventory.tar $OH/oraInventory

c. Save it in backup location

5. Install new Oracle home 10.2.0.1 in separate folder

6. Startup database with previous home [10.1.0.2.0]

a. Export ORACLE_HOME=/u01/app/oracle/product/10.1.0

b. Startup normal;

7. Check the compatibility

a. sql> @/u01/app/oracle/product/10.2.0/rdbms/admin/utlu102i.sql

b. Examine spool file and correct if any errors.

8. Startup db with new home 10.2.0.1.0 in upgrade mode.

a. $ export ORACCLE_HOME=/u01/app/oracle/product/10.2.0

b. Sql> Startup upgrade; (copy spfile from pervious to new home)

9. Upgrade the database

a. Sql> @/u01/app/oracle/product/10.2.0/rdbms/admin/catupgrade.sql

b. Verify the spool file and correct the errors if any. c. Bounce the database.

10. Examine and compile invalid object

a. Sql> @/u01/app/oracle/product/10.2.0/rdbms/admin/utlrp.sql

b. Examine spool file and re-compaile if any invalid objects.

11. Database has upgraded to 10.2.0.1.0 and now want to apply the patchset 10.2.0.4.0 on top of 10.2.0.1.0.

12. Omit step 5 and 6. Repeat remaining steps. Before install 10.2.0.4 patchset backup the following files " utlu102i.sql and catupgrade.sql "

Enable Archive Log in 10g

Oracle version : 10.1.0.2.0 (64b)

Platform : Sun Sparc 5.8 (64b)

Server : Solaris Fire v20

Storage : SAN

before enabling archivelog mode take complete backup after database shutdown (cold backup).

1. backup spfile : create pfile='/path/pfile.ora' from spfile;

2. enable archive destination

sql> alter system set log_archive_dest_1='Locatin=\path' scope=spfile;

sql> shutdown immediate;

3. perform complete backup

5. connect exclusive mode

$sqlplus "/as sysdba"

sql> startup mount;

sql> alter database archivelog;

sql> alter database open;

sql> archive log list;

Database log mode Archive Mode

Automatic archival Enabled
Archive destination \path\archive
Oldest online log sequence 5
Next log sequence to archive 7

Current log sequence 7

6. after enable archive, better to another complete backup

sql> create spfile from pfile='/path/pfile.ora'

sql> shutdown immediate;

$ cold backup

sql> startup

Note :

log_archive_format must be in this format '%t_%s_%r

%t - thread number

%s - sequence number

%r - resetlog id

Error :

SQL> startup pfile=D:\oracle\admin\ORCL\pfile\initORCL.ora

ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated

SQL> startup pfile=D:\oracle\admin\ORCL\pfile\initORCL.ora
ORA-32006: LOG_ARCHIVE_START initialization parameter has been deprecated
ORA-19905: log_archive_format must contain %s, %t and %r

Metalink Doc:

Doc ID: 274302.1

Doc ID: 371139.1

Doc ID: 69739.1

Doc ID: 420371.1

Good Luck!

Configure OEM in 10G R1/R2

Pre-Requestits

a. Password file is required.
b. Set remote_login_passwordfile is set to EXCLUSIVE or SHARED.
c. Increase the job_queue_processes more that 1.
d. Emca is located in $ORACLE_HOME/bin

Create DB Console repository object and configuration files

Oracle 10.1

Emca –r (create repository object only)
Emca (create both)

Oracle 10.2

emca –repos create (create repository object only)
emca –config dbcontrol db (create config file only) (or)
emca –config dbcontrol db –repos create (create both)

Delete and recreate database console

There are 5 way to remove db control objects.

Delete db control config files using emca script
Delete db control config files manually
Delete db control repository using RepManager
Delete db control repository manually
Delete both config and repository using emca script.

Using emca script

Oracle 10.1 > Emca –x

Oracle 10.2 >

Emctl stop dbconsole

Emca –deconfig dbcontrol db (won’t drop repository) (Or)
Emca -deconfig dbcontrol db –repose drop (drop the repository also)

Manually

Remove the following files from your filesystem
ORACLE_HOME\_
ORACLE_HOME\oc4j\j2ee\OC4J_DBConsole__
On windows you also need to remove console service from registry

Run > Regedit
Navigate > HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/OracleDBConsolesid

Action > delete (Or )
Command line : sc delete
Eg : service_name = OracleDBConsole

Using RepManager (not recommended)

ORACLE_HOME/sysman/admin/emdrep/bin
RepManager –sys_password –action drop
But don't delete by RepManager. It puts the database in quiescence mode.

Both at the same time

Oracle 10.1 > emca –x sid
Repmanager hostname listner_port sid –action drop
Oracle 10.2 > emca –deconfig dbcontrol db –repos drop

Recreate config files and repository

Config files only

Emca –repose recreate
Emca –config dbcontrol db
Emca –reconfig ports –DBCONTROL_HTTP_PORT_

Config files and repository

Emca -config dbcontrol db –repose recreate (or)
Emca –config dbcontrol db –repose recreate –SID -PORT -ORACLE_HOME -DBCONTROL_HTTP_PORT_

4. How to Drop Misconfigured repository

a. ORACLE_HOME/sysman/admin/emdrep/bin
b. Set SID and Run the following command

RepManager –sys_password –action drop

5. Commands to Star, Stop and check the Status of Console

a. Emctl start dbconsole
b. Emctl status dbconsole
c. Emctl stop dbconsole

6. Commands to start, stop and check status of EM Agent

a. Emctl start agent
b. Emctl status agent
c. Emctl stop agent

7. Connect to database control portal

http://HOSTNAME:portnumber>/em

8. Files and Folders

Local Configuration files.

$ORACLE_HOME/_
Port Number is updated in config file, this file is updated by emca while installing.
$ORACLE_HOME/install/protlist

Alert Log file location

$ORACLE_HOME/ cfgtoollogs/emca/

In Windows you can check Services.msc for running services

ORA - 16179

ORA - 16179 : incremental changes to "log_archive_dest_1" not allowed with spfile

SQL> alter system set log_archive_dest_1='d:\archive\' scope=spfile; 

alter system set log_archive_dest_1='d:\ora\' scope=spfile 

* 

ERROR at line 1: ORA-32017: failure in updating SPFILE ORA-16179: 

incremental changes to "log_archive_dest_1" not allowed with SPFILE

When i try to enable Archivelog mode in production server, i faced this ORA-16179. 

production server is version 10.1.0.2 and host is solaris sparc 64bit.

Cause:

Incremental changes to a log_archive_dest_n parameter cannot be made when using an SPFILE. 

Solution: 

Specify either LOCATION or SERVICE plus all other attributes to  be set in one ALTER SYSTEM/SESSION SET command.

Action :

SQL> ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=/db1/archive';

System altered.

Reff: Metalink Doc ID: 194494.1

EM : Policy violation

Policy rule 1 : Insufficient Number of Control Files

Cause : there is only one control file in the database

Solution : Control file multiplexed..

Action : Shutdown immediate;

Startup mount;

alter system set control_files='/path/control01.ctl','/path/newcontrol02.ctl' scope=spfile;

shutdown immediate;

startup;

Policy rule 2 : Insufficient Number of Redo Logs

cause : i have two groups in my database, but each group has only one member.

Solution : Log member multiplexed

Action : Using EM control log members added...

Policy rule 3 : EXECUTE UTL_FILE privileges to PUBLIC

Cause : bydefault 'UTL_FILE' privilege grant to PUBLIC profile.

Action : Revoke execute utl_file from public;

But still policy violation still shown.
Reason, there are many other polices are set to public profile ( UTL_SMTP, UTL_TCP, UTL_HTTP and DBMS_RANDOM).

EM- java.lang.Exception:UnknownHostException

Environment : Windows XP sp3, Oracle 10.2.0.4.0

Problem : When i connect to EM console, with SYS user. it thrown an exception error message

Error : java.lang.Exception: UnknownHostException sending request :: naachi-514ccc13

Cause : Recently I was changed the server hostname. previously it was "naachi-514ccc13", now it is "naachi".

Solution : d:\> emca -config dbcontrol db -repose recreate

The following action will be performed automatically by the above single line.

>>> Stop the database control
>>> Drop the database control
>>> Create the database control
>>> Start the database control

Status : successfully problem solved.

Clone using Export Dump

create a new database or clone a existing database or recover using oracle export Dump.

1. You must have full backup dump

2. List the content from export dump

Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y show=y
It will list all sql statement in log (imp.log)

c. Open the imp.log file, search and find the tablespace and users sql statements

i. Create tablespace .......
ii. Create users ....
iii. Create index..
iv. And what every you want

d. Copy and past it in a file.sql. now u will the script for creating a database.

e. Copy and save ‘create user.. ‘ is seperate file and ‘create tablespace..’ in sepereate sql file.

3. Create required folders (bdump,udump, also oradata)

4. Edit the pfile, use it from backup, if not. copy it from old db alert log or statspack report if u have.
5. Create oracle service

Windows : oradim –new –sid xxx
Solaris : export oracle_sid= xxx

6. Startup the database

Sqlplus “/as sysdba”
Startup nomount pfile=’/pfile.ora’

7. Run the script

$> @ file.sql
It will create all tablespaces

8. Open the database

Alter database mount;
Alter database open;

9. Create the users

Run the script to create users
@users.sql’

10. Import the datas

Imp file=’/dump/xyz.dmp’ log=’/dump/imp.log’ full=y

After import data, update the database metrics using analyze command..

Oracle EM Console 10g

Introduction

OEM is a set of system management tool for managing, monitoring and automate tasks
of oracle database environment. database control was first introduced in oracle 10g.

For windows/Unix you can follow the same method to configure EM console

1. Pre-Reuestits

a. Password file is required.
b. Set remote_login_passwordfile is set to EXCLUSIVE or SHARED.
c. Increase the job_queue_processes more that 1.
d. Unlock sysman and dbsnmp user account if already exist.
e. Emca is located in $ORACLE_HOME/bin

2. Create the repository for OEM console

emca –repos create

3. Configure the database control

emca –config dbcontrol db

4. Connect to the database control

http://hostname:portnumber/em

5. Commands to Star, Stop and check the Status of dbConsole

Emctl start dbconsole
Emctl status dbconsole
Emctl stop dbconsole

6. Commands to start, stop and check status of EM Agent

Emctl start agent
Emctl status agent
Emctl stop agent

7. how to Drop the database consol

Emca –deconfig dbcontrol db

8. What are the file Files and Folders create for your db console. lets check.

Local Configuration files

ORACLE_HOME/hostname_sid

Port Number

ORACLE_HOME/install/protlist

Log location

ORACLE_HOME/cfgtoollogs/emca/sid

9. Services

In windows you can check the services is services.msc
service like : OracleDBConsole

10. Add OS user in "Log on as a batch job" for windows

Windows user must add their name in batch job in user policies folder

Control panel -> Admin Tool -> Local security Policy -> Local Policies -> User Right Assignment
-> Log on as a batch job -> Local security setting

11. How does it work

WebBrowser <--> Http/Https <--> J2EE (OC4J) AppServer <-- Thin JDBC --> Sysman schema.

OEM create a sysman schema to store dbconsole metadata and statistics.

12. Management Packs

Oracle Congfiguration management pack

1. Track h/w and s/w configuration for hosts and database.
2. Cloning for database instance and oracle home for deployments.

Oracle Change management pack

1. Changes to support new application requirements
2. Eliminate error or data loss when making changes and minimize downtime.

Oracle Diagnostics pack and tuning pack

1. Reduce the complex performance tasks.

Cloning Using Rman

1. Creating Catalog DB called “Cloning Using Rman Buckup”.

2. Register the target DB “Source “with catalog db “rman_buckup” using rman client.

3. Configure and full back up the target db “source” using rman catalog mode.

4. Now we are going to duplicate the target db “source” using rman catalog. Duplicate database name is DEST

A. Create all necessary folders on duplicate database location “dest”
     Bdump, cdump, udump, pfile, create
B. Backup spfile or copy pfile from target db “source” to duplicate db “dest” location.
    SQL> Create pfile=’/oracle/admin/dest/pfile/initdest.ora’ from spfile;
Or
   $ cp ‘/oracle/admin/source/pfile/initsource.ora’ ‘/oracle/admin/dest/pfile/initsdest.ora’

5. Edit the parameter file and add additional parameters in dest db pfile

  Replace db_name=source to db_name=dest

  Replace all location (path) names to duplication database location

  Include additional parameters in the duplication “dest” database pfile

  Db_file_name_convert= (‘/oracle/oradata/source’, ‘/oracle/oradata/dest’)

  log_file_name_convert= (‘/oracle/oradata/source’, ‘/oracle/oradata/dest’)

6. Create a password file for duplicate database “dest”

  $ orapwd file=’/u01/app/oracle/product/9.2.0/dbs/PWDdest.ora’ password=dest entries=5

7. Make an entry in TNSNAME and LISTNER for “dest” database

8. Reload the listener : lsnrctl reload

9. Now login and connect the “dest” database in nomount stage

    $ sqlplus "sys@dest as sysdba"
    SQL*Plus: Release 9.2.0.1.0 - Production on Sat Jun 21 19:05:53 2008
    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
    Enter password:
    Connected to an idle instance.
    SQL> startup pfile='E: \oracle\admin\dest\pfile\initdest.ora' nomount;
    ORACLE instance started.
    Total System Global Area 97589952 bytes
    Fixed Size 453312 bytes
    Variable Size 46137344 bytes
    Database Buffers 50331648 bytes
   Redo Buffers 667648 bytes

 · Leave this session alive and move to rman session

10. Invoke the rman session and connect the catalog db, target db and duplicate db

 $ rman catalog rman/rman@rman target sys/admin@source auxiliary sys/admin@dest
 Recovery Manager: Release 9.2.0.1.0 - Production
 Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.
 connected to target database: SOURCE (DBID=596212533)
 connected to recovery catalog database
 connected to auxiliary database: dest (not mounted)

 OR another way to connect rman

  $ rman

   Recovery Manager: Release 9.2.0.1.0 - Production
   Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved

 
   Rman> connect catalog rman/rman@rman
               connected to recovery catalog database
   Rman> connect target sys/admin@source
                connected to target database: SOURCE (DBID=596212533)
   Rman> connect auxiliary sys/admin@dest
               connected to auxiliary database: dest (not mounted)

11. Now Execute the below script to clone the new database.

   RMAN> run {allocate auxiliary channel c1 type disk;
          2> duplicate target database to dest};

12. After executing the script, rman start execute some internal script to create instance for the duplicate database and also restore target database data files to the duplicate database location, then start applying archive logs from target database backup locations to duplicate database locations.

13. While run this script, rman itself internally run some scripts to allocated instance for the duplicate database. In middle of the script running that is “executing script: Memory Script “rman may stop execute further. So no need to worry, leave the session remain or close it. Move to your duplicate “dest” database session and execute the below script.

        SQL> alter database open resetlogs;
        Database altered.

Before that ensure all files are restore properly to duplicate database location “dest”

        SQL> select name,dbid from v$database;
        NAME DBID
       --------- ----------
       DEST 596212533

Both database source and dest database ID will be remain same. 

So we need to change the dbid in dest db using DBNEW ID utility.