DBA NAREN BLOG: 2012

15 November 2012

checking operating system version: must be

checking operating system version: must be 5.8, 5.9 Actual 5.10 Failed

$ ./runInstaller

Starting Oracle Universal Installer...

checking installer requirements...

checking operating system version: must be 5.8 or 5.9. Actual 5.10

Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at /export/home/oracle/oraInventory/logs/installActions2012-11-12_12-42-08PM.log

In this case we need to edit the oraparam.ini file. This file located in” /database/Disk1/install/”.

Please add the following entry:

[Certified Versions]

Solaris=5.8,5.9,5.10

Linux=redhat-2.1AS

HPUX=B.11.11

Decunix=V5.1A,V5.1

AIX=5.1.0.0

Save the file “oraparam.ini”

bash-3.00$ ./runInstaller

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be 5.8, 5.9 or 5.10. Actual 5.10

Passed

All installer requirements met.

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-11-12_12-43-38PM. Please wait ...bash-3.00$ Oracle Universal Installer, Version 10.1.0.3.0 Production

3 August 2012

the following files are active oci.dll

The following files are active oci.dll while applying 11g patch in windows environment

Following files are active:

E:\app\product\11.2.0\dbhome_1\bin\oci.dll

Apply Session failed during prerequisite checks: Prerequisite check "CheckActive FilesAndExecutables" failed.

System intact, OPatch will not attempt to restore the system

OPatch failed with error code = 47

How to disable the oci.dll files accessing in windows.

1) Pls rename the oracle_home directory

2) Reboot the windows server

3) Again rename the oracle_home directory with original name.

Now apply the patch:

E:\app\product\11.2.0\dbhome_1\OPatch>opatch apply

Oracle Interim Patch Installer version 11.2.0.3.0

Oracle Home : E:\app\product\11.2.0\dbhome_1

Central Inventory : C:\Program Files\Oracle\Inventory

from : n/a

OPatch version : 11.2.0.3.0

OUI version : 11.2.0.3.0

Log filel ocation : E:\app\product\11.2.0\dbhome_1\cfgtoollogs\opatch\14223717_Aug_03_2012_11_57_20\apply2012-08-03_11-57-20AM_1.log

Applying interim patch '14223717' to OH 'E:\app\product\11.2.0\dbhome_1'

Verifying environment and performing prerequisite checks...

Patch 14223717: Optional component(s) missing : [ oracle.precomp.lang, 11.2.0.3.0 ] , [ oracle.rdbms.tg4msql, 11.2.0.3.0 ] , [ oracle.rdbms.ic, 11.2.

.0 ]

All checks passed.

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: lakshminarayanan.n@media.in

Provide your My Oracle Support password to receive security updates via your My Oracle Support account.

Password (optional):

Unable to establish connection to Oracle Configuration Manager server.

Hostname (https://ccr.oracle.com) is unknown.

Unable to establish a network connection to Oracle. Specify the URL for an

Oracle Support Hub in this format:

http[s]://<OracleSupportHubHost>:<OracleSupportHubPort>

If you do not wish to configure OCM through an Oracle Support Hub, enter NONE

Oracle Support Hub URL:

Invalid Oracle Support Hub address specified ().

Unable to establish a network connection to Oracle. Specify the URL for an

Oracle Support Hub in this format:

http[s]://<OracleSupportHubHost>:<OracleSupportHubPort>

If you do not wish to configure OCM through an Oracle Support Hub, enter NONE

Oracle Support Hub URL: NONE

Unable to establish a network connection to Oracle. If your systems require a

proxy server for outbound Internet connections, enter the proxy server details

in this format:

[<proxy-user>@]<proxy-host>[:<proxy-port>]

If you want to remain uninformed of critical security issues in your

configuration, enter NONE

Proxy specification:

An invalid proxy specification was given. Please re-enter the proxy information.

Unable to establish a network connection to Oracle. If your systems require a

proxy server for outbound Internet connections, enter the proxy server details

in this format:

[<proxy-user>@]<proxy-host>[:<proxy-port>]

If you want to remain uninformed of critical security issues in your

configuration, enter NONE

Proxy specification: NONE

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.

(Oracle Home = 'E:\app\product\11.2.0\dbhome_1')

Is the local system ready for patching? [y|n]

User Responded with: Y

Backing up files...

Patching component oracle.rdbms, 11.2.0.3.0...

Patching component oracle.rdbms.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.plsql, 11.2.0.3.0...

Patching component oracle.network.rsf, 11.2.0.3.0...

Patching component oracle.precomp.common, 11.2.0.3.0...

Patching component oracle.rdbms.rman, 11.2.0.3.0...

Patching component oracle.rdbms.util, 11.2.0.3.0...

Patching component oracle.ntoledb.odp_net_2, 11.2.0.3.0...

Patching component oracle.rdbms.dbscripts, 11.2.0.3.0...

Patching component oracle.xdk.rsf, 11.2.0.3.0...

Patching component oracle.rdbms.rsf.ic, 11.2.0.3.0...

Patching component oracle.rdbms.oci, 11.2.0.3.0...

Patching component oracle.ntoramts, 11.2.0.3.0...

Patching component oracle.sysman.console.db, 11.2.0.3.0...

Patching component oracle.ntoledb, 11.2.0.3.0...

Patching component oracle.ctx, 11.2.0.3.0...

Patching component oracle.odbc.ic, 11.2.0.3.0...

Patching component oracle.network.listener, 11.2.0.3.0...

Patching component oracle.sysman.agent, 10.2.0.4.3...

Patching component oracle.ldap.rsf.ic, 11.2.0.3.0...

Patching component oracle.rdbms.deconfig, 11.2.0.3.0...

Verifying the update...

Patch 14223717 successfully applied

Log file location: E:\app\product\11.2.0\dbhome_1\cfgtoollogs\opatch\14223717_Aug_03_2012_11_57_20\apply2012-08-03_11-57-20AM_1.log

OPatch succeeded.

Ensure the OPatch applied successfully

E:\app\product\11.2.0\dbhome_1\OPatch>opatch lsinventory

Oracle Interim Patch Installer version 11.2.0.3.0

Oracle Home : E:\app\product\11.2.0\dbhome_1

Central Inventory : C:\Program Files\Oracle\Inventory

from : n/a

OPatch version : 11.2.0.3.0

OUI version : 11.2.0.3.0

Log file location : E:\app\product\11.2.0\dbhome_1\cfgtoollogs\opatch\opatch201

-08-03_12-20-49PM_1.log

Lsinventory Output file location : E:\app\product\11.2.0\dbhome_1\cfgtoollogs\o

atch\lsinv\lsinventory2012-08-03_12-20-49PM.txt

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

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.3.0

There are 1 products installed in this Oracle Home.

Interim patches (1) :

Patch 14223717 : applied on Fri Aug 03 12:13:22 PDT 2012

Unique Patch ID: 15228601

Patch description: "ORACLE 11G 11.2.0.3 PATCH 8 BUG FOR WINDOWS 32 BIT"

Created on 21 May 2012, 23:46:07 hrs PST8PDT

Bugs fixed:

14223717, 14018752, 13258936, 13723052, 12898020, 10625145, 14052871

13076238, 13992002, 13242070, 12955701, 12693573, 14026888, 13984965

13366199, 13843286, 13616375, 12839247, 8547978, 13714926, 13023854

13910420, 11837095, 12842909, 12959852, 13632140, 13780035, 13860201

13711083, 13628002, 13397104, 12358083, 13743357, 13945708, 10384051

13591484, 13051250, 14095819, 5144934, 13657605, 13582702, 12966665

13463131, 13773133, 12918738, 13099577, 13690409, 13645917, 11708510

14042380, 13627489, 13652437, 12733042, 13907462, 13608299, 13624984

9095696, 12794090, 9706532, 13469158, 13947480, 13555112, 13399236

12910565, 13790109, 12552578, 13896848, 13737746, 13080778, 13458016

13873885, 12312133, 12731940, 12672969, 13355095, 13981051, 13467683

13457582, 13370330, 13340388, 12976376, 12797420, 12658411, 10133521

9761357, 13827394, 13729832, 13851978, 13559770, 13592937, 13592919

13478856, 13559540, 13641696, 13729808, 13632530, 14100237, 11071989

13786142, 13839641, 13041324, 13965210, 12424121, 13478060, 13786778

13579992, 13705338, 13603613, 13476583, 12745662, 9190186, 13530646

13520452, 9019231, 13610777, 12905053, 10263668, 13550185, 13385346

13448206, 12772404, 13911711, 11072246, 13104881, 13863326, 12889054

13775960, 13777823, 13464002, 11824898, 13535622, 13916709, 12880299

12918338, 13885388, 13649031, 13350245, 13553883, 13388104, 13635347

13404129, 13031118, 12530140, 13023541, 13767921, 13787482, 13641076

13807411, 13724992, 12965899, 12879027, 13584130, 13495307, 12594032

13709220, 13247965, 13243072, 13683125, 13652493, 12957127, 10215977

13014128, 13040331, 12857222, 12977501, 12664456, 12405931, 13525554

13492863, 13573521, 13873471, 13791443, 13366268, 13352423, 12919564

12894807, 12829021, 12612118, 11063191, 13503598, 13482688, 13354082

13484963, 13395403, 13542159, 12983611, 13718476, 12846562, 9659614

13326736, 11846902, 11665727, 13257247, 13588248, 13544396, 13566938

13615517, 13570056, 12403721, 12585543, 12784549, 12834800, 12975771

13040171, 13058950, 13063120, 13077335, 13365700, 13382280, 13384397

13440516, 13454210, 13457537, 13477790, 13496250, 13501787, 13505390

13506110, 13524899, 13572659, 13594712, 13617627, 13555973, 13035360

13420224, 13419660, 13036331, 13332439, 13420174, 12583826, 13358781

11883969, 13524237, 12867713, 13328193, 13516727, 11865420, 13502183

13259364, 12998795, 13044108, 12971242, 12834027, 12620823, 13420516

12646784, 13366202, 11840910, 12656535, 12617123, 13073340, 12938841

13023632, 12942119, 11877623, 13070939, 12535346, 12985184, 13103913

12622441, 12923168, 13362079, 12591252, 12718090, 10350832, 12795861

13017584, 12627504, 12847466, 12797765, 12861463, 12764337, 12662040

12668341, 12597906, 12960925, 12886827, 12913474, 3522216, 12678920

12827166, 9703627, 12905058, 10357727, 13004894, 12780983, 13146719

12655301, 12960302, 12979199, 12638117, 13357509, 12401111, 8631856

13035804, 12845115, 12971775, 12917230, 12582664, 13413167, 12849688

12950644, 12588744

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

OPatch succeeded.

13 July 2012

ORA-01516 nonexistent log file, datafile, or tempfile

SQL> select file_name,file_id from dba_data_files where tablespace_name='DEV';

FILE_NAME

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

FILE_ID

----------

/ora9i/dev01.dbf

/ora9i/dev02.dbf

/ora9i/dev03.dbf

/ora9i/dev04.dbf

Sql>alter database datafile ‘/ora9i/dev04.dbf’ resize 1500m;

Error at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile ‘/ora9i/dev04.dbf’.

Extra special character or symbol has been added in the datafile during the datafile creation. So we should rename the datafile for further operations such as resize.

Here i am going to rename to datafile using rman utility.

RMAN> run

2> {

3> backup datafile 43;

4> sql 'alter database datafile 43 offline';

5> set newname for datafile 43 to '/ora9i/new_dev04.dbf';

6> restore datafile 43;

7> switch datafile all;

8> recover datafile 43;

9> sql 'alter database datafile 43 online';

10> }

Starting backup at 13-JUL-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=197 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=83 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=189 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=193 devtype=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: sid=179 devtype=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: sid=185 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00043 name=/ora9i/dev04.dbf

channel ORA_DISK_1: starting piece 1 at 13-JUL-12

channel ORA_DISK_1: finished piece 1 at 13-JUL-12

piece handle=/ora9i/oracle/dbs/jing155f_1_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Finished backup at 13-JUL-12

Starting Control File and SPFILE Autobackup at 13-JUL-12

piece handle=/ora9i/oracle/dbs/c-3541388257-20120713-07 comment=NONE

Finished Control File and SPFILE Autobackup at 13-JUL-12

sql statement: alter database datafile 43 offline

executing command: SET NEWNAME

Starting restore at 13-JUL-12

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00043 to /ora9i/new_dev04.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/ora9i/oracle/dbs/jing155f_1_1 tag=TAG20120713T222655 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 13-JUL-12

datafile 43 switched to datafile copy

input datafilecopy recid=23 stamp=788567291 filename=/ora9i/new_dev.dbf

starting full resync of recovery catalog

full resync complete

Starting recover at 13-JUL-12

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

starting media recovery

media recovery complete

Finished recover at 13-JUL-12

sql statement: alter database datafile 43 online

RMAN>

SQL> select file_name,file_id from dba_data_files where tablespace_name='DEV';

FILE_NAME

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

FILE_ID

----------

/ora9i/dev01.dbf

/ora9i/dev02.dbf

/ora9i/dev03.dbf

/ora9i/new_dev04.dbf

Sql>alter database datafile ‘/ora9i/new_dev04.dbf’ resize 1500m;

Database altered.

31 May 2012

how to set the memory target parameter in oracle 11g

How to set the memory target parameter in oracle 11g

SQL> show parameter memory

NAME TYPE VALUE

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

hi_shared_memory_address integer 0

memory_max_target big integer 0

memory_target big integer 0

shared_memory_address integer 0

SQL> ALTER SYSTEM SET MEMORY_MAX_TARGET=3G SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET MEMORY_TARGET=2G SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP;

ORACLE instance started.

Total System Global Area 3207790592 bytes

Fixed Size 2092920 bytes

Variable Size 2248151176 bytes

Database Buffers 939524096 bytes

Redo Buffers 18022400 bytes

Database mounted.

Database opened.

SQL> show parameter memory

NAME TYPE VALUE

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

hi_shared_memory_address integer 0

memory_max_target big integer 3G

memory_target big integer 2G

shared_memory_address integer 0

SQL>

how to change archive log location in Oracle 11g database

how to change archive log location in Oracle 11g database

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 512

Next log sequence to archive 515

Current log sequence 515

Db_recovery_file_dest location change:

alter system set db_recovery_file_dest='/RMANB/' scope=spfile;

log_archive_dest_1 location change:

alter system set log_archive_dest_1='/RMANB/11g/archive' scope=spfile;

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 1603887104 bytes

Fixed Size 2089064 bytes

Variable Size 385884056 bytes

Database Buffers 1207959552 bytes

Redo Buffers 7954432 bytes

Database mounted.

Database opened.

SQL> show parameter db_recovery_file_dest

NAME TYPE VALUE

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

db_recovery_file_dest string /RMANB/

db_recovery_file_dest_size big integer 2G

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /RMANB/11g

Oldest online log sequence 512

Next log sequence to archive 515

Current log sequence 515

30 May 2012

maximum number of processes ( s) exceeded

ORA-00020: maximum number of processes (%s) exceeded

Solution:

We have to increase the value of the PROCESSES initialization parameter and we must need one db bounce.

The session and transaction parameter is depends upon the the process parameter value.

Formula for session, transaction increase steps:

Processes=x

Sessions=x*1.1+5

Transactions=sessions*1.1

sql> alter system set processes=250 scope=spfile;
sql> alter system set sessions=280 scope=spfile;
sql> alter system set transactions=308 scope=spfile;

After the db bounce pls ensure the parameter value.

sql>show parameter processes

NAME TYPE VALUE

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

aq_tm_processes integer 1

db_writer_processes integer 1

job_queue_processes integer 10

log_archive_max_processes integer 2

processes integer 250

sql>show parameter session

NAME TYPE VALUE

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

java_max_sessionspace_size integer 0

java_soft_sessionspace_limit integer 0

license_max_sessions integer 0

license_sessions_warning integer 0

logmnr_max_persistent_sessions integer 1

mts_sessions integer 275

session_cached_cursors integer 0

session_max_open_files integer 10

sessions integer 280

shared_server_sessions integer 275

sql>show parameter transaction

NAME TYPE VALUE

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

transaction_auditing boolean TRUE

transactions integer 308

transactions_per_rollback_segment integer 5

sql>select count(1) from v$process;

COUNT(1)
----------
146

The value should be minimum of the process parameter value.

I hope this will help you...

23 March 2012

how to switch over the datafile on another mount point

how to switch over the datafile on another mount point using RMAN

1)Datafiles location in production db

SQL>Select file_name from dba_data_files;

FILE_NAME

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

/RMANB/11g/oradata/orcl/users01.dbf

/RMANB/11g/oradata/orcl/undotbs01.dbf

/RMANB/11g/oradata/orcl/sysaux01.dbf

/RMANB/11g/oradata/orcl/system01.dbf

/RMANB/11g/oradata/orcl/example01.dbf

/RMANB/11g/data_file/tables/oeaoratbs_01.dbf

/RMANB/11g/data_file/tables/oeaoratbs_02.dbf

SQL> select name from v$tablespace;

NAME

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

SYSTEM

SYSAUX

UNDOTBS1

USERS

TEMP

EXAMPLE

OEAORATBS

Now we are going to switch the datafile of oeaoratbs to new /opt mountpoint.

Rman rarget /

RMAN>

run{

Backup tablespace oeaoratbs;

sql 'alter tablespace oeaoratbs offline immediate';

set newname for datafile '/RMANB/11g/data_file/tables/oeaoratbs_01.dbf

' to '/opt/oeaoratbs_01.dbf ';

restore tablespace oeaoratbs;

switch datafile all;

recover tablespace oeaoratbs;

sql 'alter tablespace oeaoratbs online';

}

2> 3> 4> 5> 6> 7> 8>

sql statement: alter tablespace oeaoratbs offline immediate

executing command: SET NEWNAME

Starting restore at 20-JAN-12

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

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

restoring datafile 00005 to /opt/ oeaoratbs_01.dbf

channel ORA_DISK_1: reading from backup piece /opt/oracle/flash_recovery_area/PROD/backupset/2012_01_20/o1_mf_nnndf_TAG20120120T003258_7kjt73qm_.bkp

channel ORA_DISK_1: restored backup piece 1

piece handle=/opt/oracle/flash_recovery_area/PROD/backupset/2012_01_20/o1_mf_nnndf_TAG20120120T003258_7kjt73qm_.bkp tag=TAG20120120T003258

channel ORA_DISK_1: restore complete, elapsed time: 00:00:04

Finished restore at 20-JAN-12

datafile 5 switched to datafile copy

input datafile copy recid=2 stamp=773022827 filename=/opt/ oeaoratbs_01.dbf

Starting recover at 20-JAN-12

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 20-JAN-12

sql statement: alter tablespace oeaoratbs online

RMAN> exit

SQL>Select file_name from dba_data_files;

FILE_NAME

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

/RMANB/11g/oradata/orcl/users01.dbf

/RMANB/11g/oradata/orcl/undotbs01.dbf

/RMANB/11g/oradata/orcl/sysaux01.dbf

/RMANB/11g/oradata/orcl/system01.dbf

/RMANB/11g/oradata/orcl/example01.dbf

/opt/oeaoratbs_01.dbf

/RMANB/11g/data_file/tables/oeaoratbs_02.dbf

finally the datafile has been moved to new mont point.

Thanks

how to recover a datafile without backup.

how to recover a datafile without backup

Your database must be in archivelog mode.

Scenario:

The datafile was created at 9 am.

The datafile was corrupted or dropped at 5 pm.

SQL> select count(*) from event;

ERROR at line 1:

ORA-01116: error in opening database file 6

ORA-01110: data file 6: '/opt/oracle/oradata/pindb/god.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Solution for the issue:

Sql> alter database datafile 6 offline;

Database altered.

Sql> alter database create datafile ‘/opt/oracle/oradata/pindb/god.dbf';

Database altered.

sql> recover datafile 6;

Media recovery complete.

sql> alter database datafile 6 online;

Database altered.

SQL> select count(*) from event;

4 January 2012

rman cloning steps

Primary database: prod

Duplicate database: clone

Localhost: sunmk4

We are going to create the clone database with in same server.

Step 1:

Create the clone database password file

The prod and clone password must be same

orapwd file=/opt/oracle/product/9.2.0.1.0/dbs/orapwclone password=syspassword entries=10

C:\db_1\database> orapwd FILE=PWDclone.ora PASSWORD=syspassword ENTRIES=10 (in windows platform)

Step 2:

Add the tns entry:

CLONE =

(DESCRIPTION =

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

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = clone)

)

Check the listener and service name

>lsnrctl status

>export ORACLE_SID=clone (in unix )

> $ORACLE_HOME\bin\oradim -NEW -SID clone (in windows)

>set oracle_sid=clone

>tnsping clone

Step 3:

Create a default location of bdump ,cdump,udump,adump for clone db

mkdir -p /opt/oracle/admin/clone

cd opt/oracle/admin/clone

mkdir -p adump bdump cdump udump

mkdir –p /opt/oracle/oradata/clone

in windows

cd c:\oracle\admin\

create a clone dir

cd clone

create adump,bdump,cdump,udump dir

cd c:\oracle\oradata\

mkdir clone

Step 4:

export ORACLE_SID=prod

set oracle_sid=prod(in windows)

rman target /

run{

backup database plus archivelog;

}

After the rman backup.we are going to clone the database in same server so no need to move the rman backup.

Step 5:

Create pfile for clone db.

>create pfile=’/opt/init_clone.ora’ from spfile;

>create pfile=’c:\init_clone.ora’ from spfile;(in windows)

Edit the clone pfile

clone.__db_cache_size=180355072

clone.__java_pool_size=4194304

clone.__large_pool_size=4194304

clone.__shared_pool_size=92274688

clone.__streams_pool_size=0

*.audit_file_dest='/opt/oracle/admin/clone/adump'

*.background_dump_dest='/opt/oracle/admin/clone/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/opt/oracle/oradata/clone/control01.ctl','/opt/oracle/oradata/clone/control02.ctl','/opt/oracle/oradata/clone/control03.ctl'

*.core_dump_dest='/opt/oracle/admin/clone/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='clone'

*.job_queue_processes=10

*.log_archive_start=TRUE

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/opt/oracle/admin/clone/udump'

db_file_name_convert=(/opt/oracle/oradata/prod/,/opt/oracle/oradata/clone)

log_file_name_convert=(/opt/oracle/oradata/prod/,/opt/oracle/oradata/clone)

save the pfile.

Step 6:

Export ORACLE_SID=clone

set oracle_sid=clone(in windows)

>Sqlplus “/as sysdba”

>Create spfile from pfile=’/opt/ init_clone.ora’;

>Startup nomount;

>Rman target sys@prod nocatalog auxiliary sys

enter the sys password of the prod db

enter the sys password of the clone db

Pls ensure the prod db is open stage and clone db is nomount stage

Rman>run{

DUPLICATE TARGET DATABASE TO 'CLONE';

}