Oracle Fusion Middleware and Oracle Application

Saturday, 14 March 2015

File polling frequency setting in SOA

Issue : Files were not getting picked up from sftp location .

Workaround:

Touch the files in sftp location and go to EM console to do as shown in screenshot,

Click on the composites >>Service/Reference Properties>>FTP adapter get <specific composite>data and change polling frequency.

EM login slowness issue

Navigate to fmwc System mBean browser.

Access following AdminServer mBean for setting the cache property.

emoms.props:Location=AdminServer,name=emoms.properties,type=Properties,Application=em

Setting following three properties. Unless using non-default values, the last two properties are optional.

# Enable caching of FMw Discovery data and use it for other subsequent users.
# Values=true/false Default=false
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true

# If caching of discovery data is true, this parameter indicates how long the discovery data
# from cache should be used before requiring a fresh discovery.
# Time value is in milliseconds. Default is 7200000 milliseconds.
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_CACHE_AGE=7200000

# If caching of discovery data is true, a user logs in and a discovery session is in progress,
# this parameter indicates how long the user can wait for current discovery to complete.
# After this wait time is elapsed and discovery is still not finished: If there is already data
# in cache it will be used, else the user will launch a new discovery session.
# Time value is in milliseconds. Default is 10000 milliseconds.
oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_MAX_WAIT_TIME=10000

Following is example of setting oracle.sysman.emas.discovery.wls.FMW_DISCOVERY_USE_CACHED_RESULTS=true

If new targets are added after enabling discovery cache and new targets are not displayed in fmwc, perform a manual refresh of the Farm to update the discovery cache.

Heap Resizement of SOA Server

Heap Size vale entry in last of setDomainEnv.sh file for SOA Servers and associated OSB.
==================================================
if [ "${SERVER_NAME}" == "AdminServer" ] ;
then
MEM_ARGS="-Xms1024m -Xmx1024m"
export MEM_ARGS
fi

if [[ "${SERVER_NAME}" == *osb_server* ]] ;
then
MEM_ARGS="-Xms2048m -Xmx2048m"
export MEM_ARGS
fi
Heap Size vale entry in last of setSOADomainEnv.sh file for SOA servers.
==================================================
if [[ "${SERVER_NAME}" == *soa_server* ]] ;
then
USER_MEM_ARGS="-Xms5192m -Xmx5192m -Xgc:gencon -Xverbose:gc -Xverboselog:/u01/SOA/Middleware/java/gclog/gclog.txt -XXgcTrigger=20"
export USER_MEM_ARGS
fi

GC logs of server : Xverboselog:/u01/SOA/Middleware/java/gclog/gclog.txt

SOA server polling issue

Resolution steps for SOA file polling issue:

1. Stop the Servers (whole domain Admin server,SOA).

2. Backup the controlDir folders.
Backup and delete the Controlfiles directory , when servers starts it will be created again.
3. Delete cache, tmp
Delete tmp, cacche when server starts these directory will be craeted again.
4. Backup the FILEADAPTER_IN table and remove status value as 2 (PROCESSED).
Check if table contains file_processed status=2.
select count(*) from FILEADAPTER_IN where file_processed=2;
If above return value is equal to row count of whole table then take a back up of table and truncate.

5. Touch the files to new timestamp.
put command : touch -m * inside locate where files are availble for pollling.
6. Restart the Servers.
Restat whole domain including Admin Server.

Now Check the server polling files or not .. hope it will start polling .
Same you can check from EM console with instance through interface.

Tuesday, 9 September 2014

ORA-00214 error code in oracle database

Issue:
ORA-00214: control file '/u01/app/oradata/soaclone/control02.ctl' version 88384
inconsistent with file '/u01/app/oradata/soaclone/control01.ctl' version 88807

Root Cause: An inconsistent set of control files was used.

Solution:
[oracle@soauat132 soaclone]$ cp control02.ctl control02.ctl_bkp (for safe side)
[oracle@soauat132 soaclone]$ cp control01.ctl control01.ctl_bkp (for safe side)
[oracle@soauat132 soaclone]$ cp control01.ctl control02.ctl
[oracle@soauat132 soaclone]$ cd /u01/app/oracle/product/11.2.0/dbhome_1/bin/

[oracle@soauat132 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 10 05:54:31 2014

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area 1104814080 bytes

Fixed Size 1335980 bytes

Variable Size 671092052 bytes

Database Buffers 419430400 bytes

Redo Buffers 12955648 bytes

Database mounted.

Database opened.

SQL> select name,status from v$instance,v$database;

NAME STATUS

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

SOACLONE OPEN

SQL>

==============Hope it will help you==========

Database cloning manual steps

Old database : soauat

New database as cloned: SOACLONE (clone of “soauat” database)

Step-1: Backup control file of existing database to which we are going take clone.

SQL>alter database backup controlfile to trace;

Step-2: Check alert logfile to check which trace file has been created after control file backup.

Alert Log file : /u01/app/diag/rdbms/soauat/soauat/trace/alert_soauat.log

We can see output of alert log file as control file has been traced to below file,

alter database backup controlfile to trace

Backup controlfile written to trace file /u01/app/diag/rdbms/soauat/soauat/trace/soauat_ora_10668.trc

Step-3: Change contents of that trace file as below contents and save it as sql file ,

CREATE CONTROLFILE REUSE SET DATABASE "SOACLONE" RESETLOGS ARCHIVELOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 584

LOGFILE

GROUP 1 '/u01/app/oradata/soaclone/redo01.log' SIZE 50M BLOCKSIZE 512,

GROUP 2 '/u01/app/oradata/soaclone/redo02.log' SIZE 50M BLOCKSIZE 512,

GROUP 3 '/u01/app/oradata/soaclone/redo03.log' SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

'/u01/app/oradata/soaclone/system01.dbf',

'/u01/app/oradata/soaclone/sysaux01.dbf',

'/u01/app/oradata/soaclone/undotbs01.dbf',

'/u01/app/oradata/soaclone/users01.dbf',

'/u01/app/oradata/soaclone/example01.dbf',

'/u01/app/oradata/soaclone/DEV_iassdpm.dbf',

'/u01/app/oradata/soaclone/DEV_mds.dbf',

'/u01/app/oradata/soaclone/DEV_soainfra.dbf',

'/u01/app/oradata/soaclone/DEV_orabam.dbf',

'/u01/app/oradata/soaclone/DEV_soainfra01.dbf'

CHARACTER SET AL32UTF8

And saved the file as “db-ctrl-file-create.sql”

Step-4

Shutdown soauat database and take a cold backup of all files to appropriate location as mentioned in newly created control file creation sql script(db-ctrl-file-create.sql).

Step-5

Take a copy of initialization parameter file and named it as init<SID>.ora like we have here initSOACLONE.ora

And change the contents as below with new database name,

##############################################################################

###########################################

# Cache and I/O

###########################################

db_block_size=8192

###########################################

# Cursors and Library Cache

###########################################

open_cursors=300

###########################################

# Database Identification

###########################################

db_domain=estuate

db_name=soaclone

###########################################

# File Configuration

###########################################

control_files=("/u01/app/oradata/soaclone/control01.ctl", "/u01/app/oradata/soaclone/control02.ctl")

###########################################

# Miscellaneous

###########################################

compatible=11.2.0.0.0

diagnostic_dest=/u01/app

memory_target=1107296256

###########################################

# Processes and Sessions

###########################################

processes=150

###########################################

# Security and Auditing

###########################################

audit_file_dest=/u01/app/admin/soaclone/adump

audit_trail=db

remote_login_passwordfile=EXCLUSIVE

###########################################

# Shared Server

###########################################

dispatchers="(PROTOCOL=TCP) (SERVICE=soacloneXDB)"

###########################################

# System Managed Undo and Rollback Segments

###########################################

undo_tablespace=UNDOTBS1

new created pfilename : initSOACLONE.ora

Step -6

[oracle@soauat132 bin]$ export ORACLE_SID=SOACLONE

[oracle@soauat132 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 9 08:22:26 2014

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area 1104814080 bytes

Fixed Size 1335980 bytes

Variable Size 671092052 bytes

Database Buffers 419430400 bytes

Redo Buffers 12955648 bytes

SQL>

SQL> @/u01/app/db-ctrl-file-create.sql

Control file created.

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Disabled

Archive destination /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence 0

Next log sequence to archive 0

Current log sequence 0

SQL> alter database archivelog;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> select name from v$database;

NAME

---------

SOACLONE

Step-7
Give entry in oratab file for new SID because when we source . oraenv it reads SID info from oratab file,
oratab file location : /etc/oratab

SOACLONE:/u01/app/oracle/product/11.2.0/dbhome_1:N

Now if any other terminal we are trying to connect we can source environment as shown below,

[oracle@soauat132 bin]$ . oraenv
ORACLE_SID = [oracle] ? SOACLONE
The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app
[oracle@soauat132 bin]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 10 04:32:40 2014

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

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

SQL> select status,name from v$instance,v$database;

STATUS NAME
------------ ---------
OPEN SOACLONE

SQL>

Step-8
For netservice give entry in tnsnames.ora file,

file location:$TNS_ADMIN/tnsnames.ora

give below entry:
==============
SOACLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = soauat132.estuate)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = SOACLONE)
)
)
===============
And check with listener status whether it is handling this new service name.

SQL> !lsnrctl status soauat

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 10-SEP-2014 06:09:39

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=soauat132.estuate)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias soauat
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 10-SEP-2014 06:07:18
Uptime 0 days 0 hr. 2 min. 20 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/diag/tnslsnr/soauat132/soauat/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=soauat132.estuate)(PORT=1521)))
Services Summary...
Service "SOAUAT.estuate" has 1 instance(s).
Instance "soauat", status READY, has 1 handler(s) for this service...
Service "soaclone.estuate" has 1 instance(s).
Instance "SOACLONE", status READY, has 1 handler(s) for this service...
Service "soacloneXDB.estuate" has 1 instance(s).
Instance "SOACLONE", status READY, has 1 handler(s) for this service...
Service "soauatXDB.estuate" has 1 instance(s).
Instance "soauat", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>

===========================Hope it will help you=====================

Wednesday, 3 September 2014

SQL Command to check I/O bottle neck for sql statement in oracle database

>Sessions that are currently waiting for I/O resources:

SELECT username,

       program,

       machine,

       sql_id

  FROM v$session

 WHERE event LIKE 'db file%read';

> Which SQL statements are using a lots of disks:

 col schema format a20

 SELECT *

  FROM (  SELECT parsing_schema_name Schema, SQL_ID,

                 SUBSTR (sql_text, 1, 75) SQL,

                 disk_reads

            FROM v$sql

        ORDER BY disk_reads DESC)

 WHERE ROWNUM < 20;

 And with the result set of (1) or (2):

 set long 1000

 select SQL_FULLTEXT from v$sql;
O/P:

Database cloning using RMAN

In this workout we have,
Source database="ORACLE"
Duplicate database="CLONEDB"

Step-1:Create Password file for Auxiliary Database:

C:\Documents and Settings\suvdas>orapwd file='D:\app\EST_Suvendu\product\11.2.0\

dbhome_1\database\PWDclonedb' password=manager

Step-2: Create Initializing parameter file for auxiliary database:

SQL> create pfile='D:\app\EST_Suvendu\product\11.2.0\dbhome_1\database\initCLONEDB.ora' from spfile;

File created.

After creating the initialization parameter for the duplicate database, and change at leastthe following parameters:

           db_file_name_convert = ('D:\APP\EST_SUVENDU\oradata
\ORACLE', 'D:\APP\EST_SUVENDU\oradata
\CLONEDB')

log_file_name_convert = ('D:\APP\EST_SUVENDU\oradata
\ORACLE', 'D:\APP\EST_SUVENDU\oradata
\CLONEDB')

control_files = 'D:\APP\EST_SUVENDU\oradata
\CLONEDB\control01.ctl'

              , 'D:\APP\EST_SUVENDU\oradata
\CLONEDB\control02.ctl'

              , 'D:\APP\EST_SUVENDU\oradata
\CLONEDB\control03.ctl'

db_name = 'CLONEDB'
instance_name = 'CLONEDB'
background_dump_dest = 'D:\APP\EST_SUVENDU
\admin\CLONEDB
\bdump'
core_dump_dest = 'D:\APP\EST_SUVENDU
\admin\CLONEDB
\cdump'
user_dump_dest = 'D:\APP\EST_SUVENDU
\admin\CLONEDB
\udump'
dispatchers = '(PROTOCOL=TCP) (SERVICE=CLONEDBXDB)'
log_archive_dest_1 = 'location=D:\APP\EST_SUVENDU\oradata
\CLONEDB
\archive MANDATORY'

Step-3:Create/start Auxiliary database
Create a new Windows service for the duplicate database CLONEDB using oradim:

C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\oradata\CLONEDB
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\oradata\CLONEDB\archive
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\create
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump\pfile
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump\scripts
C:\Documents and Settings\suvdas> mkdir D:\APP\EST_SUVENDU\admin\CLONEDB\bdump\udump

C:\Documents and Settings\suvdas>oradim -new -sid CLONEDB -intpwd manager -start

mode auto -pfile 'D:\app\EST_Suvendu\product\11.2.0\dbhome_1\database\initCLONEDB.ora

 C:\Documents and Settings\suvdas>set ORACLE_SID=CLONEDB


 C:\Documents and Settings\suvdas> sqlplus "/ as sysdba"

 SQL> startup nomount

Step-4:

The listener file should look like below:

# listener.ora Network Configuration File: D:\app\EST_Suvendu\product\11.2.0\dbhome_1\network\admin\listener.ora

# Generated by Oracle configuration tools.

LOCALLSNR =

(DESCRIPTION_LIST =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.52)(PORT = 1521))

(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

)

SID_LIST_LOCALLSNR =

(SID_LIST =

(SID_DESC =

(SID_NAME =CLONEDB )

(ORACLE_HOME = D:\app\EST_Suvendu\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS=ONLY:D:\app\EST_Suvendu\product\11.2.0\dbhome_1\bin\oraclr11.dll")

)

(SID_DESC =

(SID_NAME =oracle )

(ORACLE_HOME = D:\app\EST_Suvendu\product\11.2.0\dbhome_1)

(PROGRAM = extproc)

(ENVS = "EXTPROC_DLLS=ONLY:D:\app\EST_Suvendu\product\11.2.0\dbhome_1\bin\oraclr11.dll")

)

ADR_BASE_LOCALLSNR = D:\app\EST_Suvendu

#CLRExtProc

Step-5: Mount/Open the source database "oracle" if not open,

C:\Documents and Settings\suvdas> set ORACLE_SID=ORACLE
C:\Documents and Settings\suvdas> sqlplus "/ as sysdba"
SQL> startup open

Step-6:

>rman sys/manager@oracle auxiliary sys/manager@clonedb

it will connect to RMAN session then give below commands:

RMAN>run {

# Allocate the channel for the duplicate work

allocate auxiliary channel ch1 type disk;

# Duplicate the database to CLONEDB

duplicate target database to CLONEDB;

}

/////////

below are results of cloning:

Redo Buffers 5804032 bytes

allocated channel: ch1

channel ch1: SID=134 device type=DISK

contents of Memory Script:

{

sql clone "alter system set db_name =

''ORACLE'' comment=

''Modified by RMAN duplicate'' scope=spfile";

sql clone "alter system set db_unique_name =

''CLONEDB'' comment=

''Modified by RMAN duplicate'' scope=spfile";

shutdown clone immediate;

startup clone force nomount

restore clone primary controlfile;

alter clone database mount;

}

executing Memory Script

sql statement: alter system set db_name = ''ORACLE'' comment= ''Modified by RM

AN duplicate'' scope=spfile

sql statement: alter system set db_unique_name = ''CLONEDB'' comment= ''Modifi

ed by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes

Variable Size 192938448 bytes

Database Buffers 335544320 bytes

Redo Buffers 5804032 bytes

allocated channel: ch1

channel ch1: SID=134 device type=DISK

Starting restore at 13-MAY-13

channel ch1: starting datafile backup set restore

channel ch1: restoring control file

channel ch1: reading from backup piece D:\APP\EST_SUVENDU\RMANBKP\C-1687975765-2

0130510-01.CTL

channel ch1: piece handle=D:\APP\EST_SUVENDU\RMANBKP\C-1687975765-20130510-01.CT

L tag=TAG20130510T144129

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:01

output file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\CONTROL01.CTL

output file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\CONTROL02.CTL

Finished restore at 13-MAY-13

database mounted

contents of Memory Script:

{

set until scn 1288024;

set newname for datafile 1 to

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSTEM01.DBF";

set newname for datafile 2 to

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSAUX01.DBF";

set newname for datafile 3 to

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDOTBS01.DBF";

set newname for datafile 4 to

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USERS01.DBF";

set newname for datafile 5 to

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAMPLE01.DBF";

restore

clone database

;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

Starting restore at 13-MAY-13

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00002 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSA

UX01.DBF

channel ch1: restoring datafile 00003 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDO

TBS01.DBF

channel ch1: restoring datafile 00005 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAM

PLE01.DBF

channel ch1: reading from backup piece D:\APP\EST_SUVENDU\RMANBKP\10O99OUC_1_1.D

channel ch1: piece handle=D:\APP\EST_SUVENDU\RMANBKP\10O99OUC_1_1.DBF tag=TAG201

30510T144011

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:25

channel ch1: starting datafile backup set restore

channel ch1: specifying datafile(s) to restore from backup set

channel ch1: restoring datafile 00001 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYST

EM01.DBF

channel ch1: restoring datafile 00004 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USER

S01.DBF

channel ch1: reading from backup piece D:\APP\EST_SUVENDU\RMANBKP\11O99OUE_1_1.D

channel ch1: piece handle=D:\APP\EST_SUVENDU\RMANBKP\11O99OUE_1_1.DBF tag=TAG201

30510T144011

channel ch1: restored backup piece 1

channel ch1: restore complete, elapsed time: 00:00:33

Finished restore at 13-MAY-13

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=7 STAMP=815332942 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\SYSTEM01.DBF

datafile 2 switched to datafile copy

input datafile copy RECID=8 STAMP=815332942 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=9 STAMP=815332943 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=10 STAMP=815332943 file name=D:\APP\EST_SUVENDU\ORADAT

A\CLONEDB\USERS01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=11 STAMP=815332943 file name=D:\APP\EST_SUVENDU\ORADAT

A\CLONEDB\EXAMPLE01.DBF

contents of Memory Script:

{

set until scn 1288024;

recover

clone database

delete archivelog

;

}

executing Memory Script

executing command: SET until clause

Starting recover at 13-MAY-13

starting media recovery

archived log for thread 1 with sequence 14 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000014_0814898327.0001

archived log for thread 1 with sequence 15 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000015_0814898327.0001

archived log for thread 1 with sequence 16 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000016_0814898327.0001

archived log for thread 1 with sequence 17 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000017_0814898327.0001

archived log for thread 1 with sequence 18 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000018_0814898327.0001

archived log for thread 1 with sequence 19 is already on disk as file D:\APP\EST

_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000019_0814898327.0001

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000014_0

814898327.0001 thread=1 sequence=14

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000015_0

814898327.0001 thread=1 sequence=15

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000016_0

814898327.0001 thread=1 sequence=16

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000017_0

814898327.0001 thread=1 sequence=17

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000018_0

814898327.0001 thread=1 sequence=18

archived log file name=D:\APP\EST_SUVENDU\ORADATA\ARCHIVE\ORACLE\ARC0000000019_0

814898327.0001 thread=1 sequence=19

media recovery complete, elapsed time: 00:00:30

Finished recover at 13-MAY-13

contents of Memory Script:

{

shutdown clone immediate;

startup clone nomount;

sql clone "alter system set db_name =

''CLONEDB'' comment=

''Reset to original value by RMAN'' scope=spfile";

sql clone "alter system reset db_unique_name scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

database dismounted

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes

Variable Size 192938448 bytes

Database Buffers 335544320 bytes

Redo Buffers 5804032 bytes

allocated channel: ch1

channel ch1: SID=133 device type=DISK

sql statement: alter system set db_name = ''CLONEDB'' comment= ''Reset to orig

inal value by RMAN'' scope=spfile

sql statement: alter system reset db_unique_name scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 535662592 bytes

Fixed Size 1375792 bytes

Variable Size 192938448 bytes

Database Buffers 335544320 bytes

Redo Buffers 5804032 bytes

allocated channel: ch1

channel ch1: SID=133 device type=DISK

sql statement: CREATE CONTROLFILE REUSE SET DATABASE "CLONEDB" RESETLOGS ARCHIVE

LOG

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

MAXINSTANCES 8

MAXLOGHISTORY 292

LOGFILE

GROUP 1 ( 'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\REDO01.LOG' ) SIZE 50 M REUSE,

GROUP 2 ( 'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\REDO02.LOG' ) SIZE 50 M REUSE,

GROUP 3 ( 'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\REDO03.LOG' ) SIZE 50 M REUSE

DATAFILE

'D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSTEM01.DBF'

CHARACTER SET AL32UTF8

contents of Memory Script:

{

set newname for tempfile 1 to

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\TEMP01.DBF";

switch clone tempfile all;

catalog clone datafilecopy "D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSAUX01.DBF"

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDOTBS01.DBF",

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USERS01.DBF",

"D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAMPLE01.DBF";

switch clone datafile all;

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to D:\APP\EST_SUVENDU\ORADATA\CLONEDB\TEMP01.DBF in control f

ile

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\SYSAUX01.DBF RECID=1

STAMP=815332990

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\UNDOTBS01.DBF RECID=2

STAMP=815332990

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\USERS01.DBF RECID=3 S

TAMP=815332990

cataloged datafile copy

datafile copy file name=D:\APP\EST_SUVENDU\ORADATA\CLONEDB\EXAMPLE01.DBF RECID=4

STAMP=815332990

datafile 2 switched to datafile copy

input datafile copy RECID=1 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\SYSAUX01.DBF

datafile 3 switched to datafile copy

input datafile copy RECID=2 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\UNDOTBS01.DBF

datafile 4 switched to datafile copy

input datafile copy RECID=3 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\USERS01.DBF

datafile 5 switched to datafile copy

input datafile copy RECID=4 STAMP=815332990 file name=D:\APP\EST_SUVENDU\ORADATA

\CLONEDB\EXAMPLE01.DBF

contents of Memory Script:

{

Alter clone database open resetlogs;

}

executing Memory Script

database opened

Finished Duplicate Db at 13-MAY-13

released channel: ch1

RMAN>

RMAN> exit

Recovery Manager complete.

C:\Documents and Settings\suvdas>sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Mon May 13 17:27:55 2013

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$instance,v$database;

NAME

---------

CLONEDB

SQL> select name,status from v$instance,v$database;

NAME STATUS

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

CLONEDB OPEN

SQL>

===============Hope it will help you================================

About Me

Saturday, 14 March 2015

Tuesday, 9 September 2014

Wednesday, 3 September 2014