Datafile deleted how to recover database

Datafile deleted how to recover database :
Scenario : By any chance datafile deleted and backup  has not been taken any backup for Database , now need to recover Database
Solution :
For the solution we need to know datafile name which is missing and we can come to know from control file
So we have to follow below steps to recover database.
1.Create a new datafile as named in control file.
2.Restore Database backup or if there is no backup taken and archive log mode on we can recover from archive log. 

tablespace segment space management

Tablespace segment space management :

Tablespace segment space management has two options auto and manual and by default segment space management is manual.

Find below description for auto and manual SSM:

Manual:

Manual segment space management uses freelist for future utilization of tables.Data blocks are being added up to freelist then data insertion into table being possible.

To add blocks to freelist oracle database system compares PCT_USED and PCT_FREE

PCTUSED

PCTUSED is a block storage parameter used to specify when Oracle should consider a database block to be empty enough to be added to the freelist. Oracle will only insert new rows in blocks that is enqueued on the freelist. For example, if PCTUSED=40, Oracle will not add new rows to the block unless sufficient rows are deleted from the block so that it falls below 40% used.

That means when database block usespace <PCT_USED then the block being available to freelist and allow to insert row into tables.By default value for pct_used is 40

PCTFREE

PCTFREE is a block storage parameter used to specify how much space should be left in a database block for future updates. For example, for PCTFREE=10, Oracle will keep on adding new rows to a block until it is 90% full. This leaves 10% for future updates (row expansion).

When using Oracle Advanced Compression, Oracle will trigger block compression when the PCTFREE is reached. This eliminates holes created by row deletions and maximizes contiguous free space in blocks.

That means when database block freespace < PCT_FREE then the block being unavailable  to free list and does not allow to insert row. Because,pst_free specified space available only for future row expansion on update. By default value for pct_free is 10.

PCTINCREASE

PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow. The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.

Auto:

Its unlike manual SSM it uses bitmap instead of freelist and not using pct_used and pct_free parameter.

It is not available for temporary and system tablespace and only available for tablespace configured for local extend management .Automatic segment management performs better and reduces maintenance task.

Below shown picture Describes SSM and parameters :

oracle database upgradation from 11.2.0.3 to 11.2.0.4 (11.2.0.3-11.2.0.4)

Oracle Database Upgradation(from 11.2.0.3 to 11.2.0.4 )

Steps to follow:

1.Download patch  13390677 (11.2.0.4) from metalink  or any provided link for 11.2.0.4 DB 

2.Take backup of old DB(11.2.0.3)

3.Start installing 11.2.0.4 DB on server with separate home .

4.export new ORACLE_HOME in bash_profile  for permanent setting.

note:Since database version is same no need to create listener for new DB.

6.Start ./dbua from $ORACLE_HOME/bin(Check whether the database is up or not from old home)

PATH to Upgrade:

Old DB: 11.2.0.3--> 11(DB version).2(Release).0(application release).3(component release/patch release)

New DB: 11.2.0.4-->11(DB version).2(Release).0(application release).4(component release/patch release)

It clearly says need to upgrade patch release with component.

as shown in example below:

Installation of new DB software only and upgrade to new patch version:

To resolve the above error  :
Root cause database was not started start DB and proceed

'

After upgradation check for INVALID object for dba_objects check before and after run utlrp.sql scripts.

utlrp.sql script to recompile invalid object. 

REP-0501: Unable to connect to the specified database

Issue:

As found from log file:
REP-0501: Unable to connect to the specified database.
ORA-01017: invalid username/password; logon denied

Root Cause:  issue after attempting to re-install Object Migrator on EBS instance and sometimes for some patches like 12964564 ,13855823 oracle application patch.

Solution: 

solution was to apply patch 16946854.

because , patch describes as below details,

=========================================

This patch only affects customer who are using data groups with custom schemas
attached to a responsibility for request submission.

In this case, the patch is necessary to successfully decrypt the password for
the oracle id associated with the custom schema.

Please restart the managers after applying this patch.

=============================================

Bugs Fixed
The following bugs are fixed by this patch:

16946854 - REQUEST SUBMITTED BY CUSTOM RESP AND CUSTOM DATA GROUP CAUSES FNDLIBR TO COREDUM

and moreover below bugs being fixed by this patch,

Bugs Resolved by This Patch


POSSIBLE FND IMPACT FOR MIXED CASE APPLSYSPUB OR GWYUID PASSWORD
11G PASSWORD CASE SENSITIVITY PROJECT CONSOLIDATION BUG
10138986: 11G PASSWORD CASE SENSITIVITY PROJECT CONSOLIDATION BUG
QRE1220.11:GSI:FAILED - LOGIN PAGE NOT LAUNCHING AFTER 12.2 UPGRADE
12937670: 10138986: 11G PASSWORD CASE SENSITIVITY PROJECT CONSOLIDATION BUG
FNDCPASS: REGRESSION OF PASSWORD PROMPTING FEATURE
REQUEST SUBMITTED BY CUSTOM RESP AND CUSTOM DATA GROUP CAUSES FNDLIBR TO COREDUM
SYSTEM HOLD FOR SERVICE PROCESSES.
R:ICM IS DOWN IN ATG121D7.PLEASE BRING IT UP
ACTIVE DATA GUARD SUPPORT: CONCURRENT MANAGER

Try it hopefully it will take you ahead ..... :)

WebLogic Startup fails with Unable to obtain lock on Server may already be running

Issue : When weblogic server starting showing error "Unable to obtain lock" .
Root Cause: previous session has not been terminated cause <servername>.lok file exists

Solution:
-Go to>> $DOMAIN_HOME/servers/<servername>/tmp/
  Check <servername>.lok file exists in this location.
-Delete this <servername>.lok file and start admin server ,it will overcome the error ...

Note-this solution applicable for all managed server as well..

weblogic domain target configuration in OEM 12c

Middleware target configuration  in OEM 12c

In OEM console go to>>setup >>add target>>configure auto discovery

sql server plugin deployment on OEM 12c

Sql server plugin deployment :

Go to setup>>extensibility >>plug-in>>database>>sql server>>rightclick on plugin for optiondeploy then screen will come  as cited below,

Clone Oracle Apps R12 Short Steps

Clone Oracle Apps R12 Short Steps

Pre-Clone Steps:

On the DB server:

· cd $ORACLE_HOME/appsutil/scripts/

· perl adpreclone.pl dbTier

· Check the log file under

· $ORACLE_HOME/appsutil/log//StageDBTier_06201503.log

On the apps tier server:

· cd $ADMIN_SCRIPTS_HOME

· perl adpreclone.pl appsTier

· Check the log file $APPL_TOP/admin/log/

Clone Steps:

Copy the source apps tier file system to the target system.

· APPL_TOP.

· COMMON_TOP

· ORACLEAS_10.1.2_ORACLE_HOME

· ORACLEAS_10.1.3_ORACLE_HOME

Copy source system database tier file system to the target system. As user oracle:

· Shutdown normal the source system database.

· Copy the database (.dbf) files plus ORACLE_HOME to the target system.

On the target Database system

· cd $ORACLE_HOME/appsutil/clone/bin

· perl adcfgclone.pl dbTier

· Verify errors the log $ORACLE_HOME/appsutil/log/

On the target apps system

· $cd $COMMON_TOP/clone/bin

· $perl adcfgclone.pl appsTier

· Verify the errors the log. $APPL_TOP/admin//log

· Once clone is finished. If you have customized environment then change post clone profile options, utl_file_dir etc.

database target configuration on OEM

Inorder to add new Db hosts and instance to OEM follow the below steps:

And keep agent as source OEM agent , in my case it was 164 machine (hostname:optimebs64.estuate) and agent is residing on same machine and we just have to add database instance which will be monitoring by OEM on OEM installed machine.

                                                                                                                                                                    

    

        

select agent it will add database instance as database target on OEM console ...        

OEM server reconfiguration after statis IP change

OEM_server_issue _ after static IP change:

Step-1 Changes  ip address in /etc/hosts file as root user

Step-2 restarted oms from backend with command :

./emctl stop oms

And again ./emctl start oms

Step-3 restart agent with command :

./emctl stop agent

And again ./emctl start agent

target status is pending on OEM CONSOLE

Issue: FROM oeM CONSOLE  target status is pending .

Solution:increased job_queue_process to 20 in target db and restarted OMS services and restarted agent .

OMS and agent in  machine1 and  DB in machine2.

Sol:

SQL> alter system set job_queue_processes=20;

System altered.

SQL> sho parameter job_queue_processes;

NAME                                 TYPE        VALUE

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

job_queue_processes                  integer     20

SQL>

Then come to OMS installed machine and restart oms on $OMS_HOME/bin

./emctl  stop oms

Ans then

./emctl  start oms.

And then restart agent  go to >$AGENT_HOME/bin

./emctl  stop agent

Then

./emctl  start agent

ORA-03113_endof_file_in_communication

Microsoft Windows 2000 [Version 5.00.2195]

(C) Copyright 1985-2000 Microsoft Corp.

C:\Documents and Settings\SADMIN>set ORACLE_SID=siebeldb

C:\Documents and Settings\SADMIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 06:46:44 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup

ORA-03113: end-of-file on communication channel

SQL> startup pfile='D:\ora10\dbs\initSIEBELDB.ora'

ORA-24324: service handle not initialized

ORA-01041: internal error. hostdef extension doesn't exist

SQL> exit

Disconnected

IT happened because of background process not being started because in initSIEBELDB.ora file the path fore udump,adump,cdump mismatched .

So changed in that file and started database with this file.

C:\Documents and Settings\SADMIN>echo %ORACLE_HOME%

D:\ora10

C:\Documents and Settings\SADMIN>echo %PATH%

d:\ora10\BIN;D:\ora10\NETWORK\tools;D:\ora10\jre\1.4.2\bin\client;D:\ora10\jre\1

.4.2\bin;D:\ora10\NETWORK\ADMIN;C:\WINNT\system32;C:\WINNT;C:\WINNT\System32\Wbe

m;C:\Program Files\Executive Software\DiskeeperLite\

C:\Documents and Settings\SADMIN>set ORACLE_SID=siebeldb

C:\Documents and Settings\SADMIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Wed May 15 06:49:05 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter user-name: / as sysdba

Connected to an idle instance.

SQL> startup pfile='D:\ora10\database\initSIEBELDB.ora'

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  1253096 bytes

Variable Size             264241432 bytes

Database Buffers          805306368 bytes

Redo Buffers                2940928 bytes

Database mounted.

Database opened.

SQL> create spfile='D:\ora10\database\SPFILESIEBELDB.ora' from pfile='D:\ora10\database\in

itSIEBELDB.ora'

  2  ;

File created.

SQL> startup

ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1073741824 bytes

Fixed Size                  1253096 bytes

Variable Size             264241432 bytes

Database Buffers          805306368 bytes

Redo Buffers                2940928 bytes

Database mounted.

Database opened.

SQL>

internal manager and all manager shows actual=1 and target=1 AND OTHER ARE ACTUAL=0 TARGET=0

REF:MOS  ID 555081.1

Issue: when internal manager and all manager shows actual=1 and target=1 AND OTHER ARE ACTUAL=0 TARGET=0

 SOLUTION FOR THIS ISSUE:

Step1:stop application services  ./adstpall.sh apps/apps

step2: sqlplus apps/apps

update apps.fnd_concurrent_queues set node_name = 'OPTIMEBS26' where node_name is NULL;

           update fnd_concurrent_queues set TARGET_NODE='OPTIMEBS26' where TARGET_NODE is NULL;

step3:  EXEC FND_CONC_CLONE.SETUP_CLEAN;

             COMMIT;

            EXIT;

          commit;

step4: Ran cmclean.sql by loging database with apps/apps user.

Step5: LAST STEP: RUN AUTO CONFIG ON APPLICATION TIER

If still not resolving then try below:

Stop application services again and run below command as DB user as “apps”.

UPDATE fnd_concurrent_queues set target_node = 'OPTIMEBS64';

UPDATE fnd_concurrent_queues set node_name = 'OPTIMEBS64';

Check all target and node with below commands :

select control_code,target_node,node_name,CONCURRENT_QUEUE_NAME from fnd_concurrent_queues;

EXEC FND_CONC_CLONE.SETUP_CLEAN;

             COMMIT;

            EXIT;

          commit;

and start application services .