Oracle 10g Database manual Cloning Using Cold Backup

Source Database: mandb11g

Destination Database to be created: manclone

Prerequisites: Clean backup of all source database files (datafiles, controfiles, logfiles, pfile)

Step 1: Take clean backup of all source database files (datafiles, controfiles, logfiles, pfile).

Step 2: Make directories structure for the destination database as below.

 

 

 

Step 3: Copy the files to the appropriate destination database directory as shown below.

D:\app\UserXP\oradata\mandb11g\data\MANDB11G\DATAFILE>copy *.DBF D:\app\UserXP\oradata\manclone\data

O1_MF_SYSAUX_741CPC5V_.DBF

O1_MF_SYSTEM_741CP7KL_.DBF

O1_MF_SYS_UNDO_741CPFMK_.DBF

O1_MF_USERDATA_741CY41G_.DBF

        4 file(s) copied.

D:\app\UserXP\oradata\mandb11g\redo\MANDB11G\CONTROLFILE>copy *.CTL D:\app\UserXP\oradata\manclone\control\

O1_MF_741CP37T_.CTL

        1 file(s) copied.

D:\app\UserXP\oradata\mandb11g\redo\MANDB11G\ONLINELOG>copy *.LOG D:\app\UserXP\oradata\manclone\redo\

O1_MF_1_741CP3PG_.LOG

O1_MF_2_741CP5D6_.LOG

        2 file(s) copied.

D:\app\UserXP\oradata\manclone\trace>dir

Directory of D:\app\UserXP\oradata\manclone\trace

11/28/2011 03:36 PM adump

11/28/2011 03:34 PM bdump

11/28/2011 03:34 PM cdump

11/28/2011 03:34 PM udump

D:\app\UserXP\oradata\manclone>dir

Directory of D:\app\UserXP\oradata\manclone

11/28/2011 03:29 PM control

11/28/2011 03:26 PM data

11/28/2011 03:00 PM flash_recovery

11/28/2011 03:32 PM redo

11/28/2011 03:36 PM trace

D:\app\UserXP\oradata\manclone\control>dir

Directory of D:\app\UserXP\oradata\manclone\control

11/28/2011 03:29 PM .

11/28/2011 03:29 PM ..

11/28/2011 03:25 PM 10,240,000 O1_MF_741CP37T_.CTL

D:\app\UserXP\oradata\manclone\data>dir

Directory of D:\app\UserXP\oradata\manclone\data

11/28/2011 03:25 PM 104,865,792 O1_MF_SYSAUX_741CPC5V_.DBF

11/28/2011 03:25 PM 209,723,392 O1_MF_SYSTEM_741CP7KL_.DBF

11/28/2011 03:25 PM 157,294,592 O1_MF_SYS_UNDO_741CPFMK_.DBF

11/28/2011 03:25 PM 10,493,952 O1_MF_USERDATA_741CY41G_.DBF

D:\app\UserXP\oradata\manclone\flash_recovery>dir

Directory of D:\app\UserXP\oradata\manclone\flash_recovery

11/28/2011 03:00 PM archive

11/28/2011 03:00 PM backupset

D:\app\UserXP\oradata\manclone\redo>dir

Directory of D:\app\UserXP\oradata\manclone\redo

11/28/2011 03:25 PM 104,858,112 O1_MF_1_741CP3PG_.LOG

11/28/2011 03:25 PM 104,858,112 O1_MF_2_741CP5D6_.LOG

D:\app\UserXP\oradata\manclone\trace>dir

Directory of D:\app\UserXP\oradata\manclone\trace

11/28/2011 03:36 PM adump

11/28/2011 03:34 PM bdump

11/28/2011 03:34 PM cdump

11/28/2011 03:34 PM udump

Step 4: Create pfile from spfile for the source database.

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

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Nov 28 15:58:15 2011

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production

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

SQL> show parameter service

NAME TYPE VALUE

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

service_names string MANDB11G

SQL> create pfile='D:\app\UserXP\oradata\manclone\initmanclone.ora' from spfile;

File created.

Step 5: Create service for destination database manclone.

C:\Documents and Settings\UserXP>set oracle_sid=manclone

C:\Documents and Settings\UserXP>oradmin -new -sid manclone -intpwd manclone

'oradmin' is not recognized as an internal or external command,

operable program or batch file.

C:\Documents and Settings\UserXP>oradim -new -sid manclone -intpwd manclone

Instance created.

C:\Documents and Settings\UserXP>sqlplus "sys/manclone as sysdba"

SQL*Plus: Release 11.1.0.7.0 - Production on Mon Dec 5 14:55:34 2011

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

Connected to an idle instance.

SQL> create spfile from pfile='D:\app\UserXP\oradata\manclone\pfile\initmanclone.ora';

File created.

SQL> startup nomount

ORA-32004: obsolete and/or deprecated parameter(s) specified

ORACLE instance started.

Total System Global Area 150667264 bytes

Fixed Size 1345868 bytes

Variable Size 92276404 bytes

Database Buffers 50331648 bytes

Redo Buffers 6713344 bytes

SQL> select status from v$instance;

STATUS

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

STARTED

SQL> show parameter service

NAME TYPE VALUE

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

service_names string manclone

SQL> ed

Wrote file afiedt.buf

1 CREATE CONTROLFILE REUSE SET DATABASE "MANCLONE" RESETLOGS ARCHIVELOG

2 MAXLOGFILES 32

3 MAXLOGMEMBERS 2

4 MAXDATAFILES 32

5 MAXINSTANCES 16

6 MAXLOGHISTORY 1752

7 LOGFILE

8 GROUP 1 'D:\app\UserXP\oradata\manclone\redo\O1_MF_1_741CP3PG_.LOG' SIZE 100M,

9 GROUP 2 'D:\app\UserXP\oradata\manclone\redo\O1_MF_2_741CP5D6_.LOG' SIZE 100M

10 -- STANDBY LOGFILE

11 DATAFILE

12 'D:\app\UserXP\oradata\manclone\data\O1_MF_SYSTEM_741CP7KL_.DBF',

13 'D:\app\UserXP\oradata\manclone\data\O1_MF_SYSAUX_741CPC5V_.DBF',

14 'D:\app\UserXP\oradata\manclone\data\O1_MF_SYS_UNDO_741CPFMK_.DBF',

15 'D:\app\UserXP\oradata\manclone\data\O1_MF_USERDATA_741CY41G_.DBF'

16* CHARACTER SET US7ASCII

SQL> /

Control file created.

SQL>

SQL> select status from v$instance;

STATUS

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

MOUNTED

SQL> alter database open resetlogs;

Database altered.

SQL> select status from v$instance;

STATUS

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

OPEN

SQL> set linesize 150

SQL> set pagesize 50

SQL> col tablespace_name format a20

SQL> col file_name format a30

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

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

SYSTEM D:\APP\USERXP\ORADATA\MANCLONE

\DATA\O1_MF_SYSTEM_741CP7KL_.DBF

USERDATA D:\APP\USERXP\ORADATA\MANCLONE

\DATA\O1_MF_USERDATA_741CY41G_.DBF

SYS_UNDOTS D:\APP\USERXP\ORADATA\MANCLONE

\DATA\O1_MF_SYS_UNDO_741CPFMK_.DBF

SYSAUX D:\APP\USERXP\ORADATA\MANCLONE

\DATA\O1_MF_SYSAUX_741CPC5V_.DBF

SQL> col member format a40

SQL> select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_

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

2 ONLINE D:\APP\USERXP\ORADATA\MANCLONE\REDO\O1_M NO

F_2_741CP5D6_.LOG

1 ONLINE D:\APP\USERXP\ORADATA\MANCLONE\REDO\O1_M NO

F_1_741CP3PG_.LOG

STARTUP NOMOUNT <  <==Remove

CREATE CONTROLFILE REUSE DATABASE "MANDB11G" NORESETLOGS ARCHIVELOGß <==Change

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1752

LOGFILE

GROUP 1 'D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\ONLINELOG\O1_MF_1_741CP3PG_.LOG' SIZE 100M, <==Change

GROUP 2 'D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\ONLINELOG\O1_MF_2_741CP5D6_.LOG' SIZE 100M <==Change

-- STANDBY LOGFILE

DATAFILE

'D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYSTEM_741CP7KL_.DBF', <==Change

'D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYSAUX_741CPC5V_.DBF', <==Change

'D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYS_UNDO_741CPFMK_.DBF', <==Change

'D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_USERDATA_741CY41G_.DBF'  <==Change

CHARACTER SET US7ASCII

;

STARTUP NOMOUNT <==Remove

CREATE CONTROLFILE REUSE SETDATABASE "MANCLONE" NORESETLOGS ARCHIVELOG <==Change

MAXLOGFILES 32

MAXLOGMEMBERS 2

MAXDATAFILES 32

MAXINSTANCES 16

MAXLOGHISTORY 1752

LOGFILE

GROUP 1 ' D:\app\UserXP\oradata\manclone\redo\O1_MF_1_741CP3PG_.LOG' SIZE 100M, <==Change

GROUP 2 ' D:\app\UserXP\oradata\manclone\redo\O1_MF_2_741CP5D6_.LOG' SIZE 100M <==Change

-- STANDBY LOGFILE

DATAFILE

‘D:\app\UserXP\oradata\manclone\data\O1_MF_SYSTEM_741CP7KL_.DBF', <==Change

D:\APP\USERXP\ORADATA\MANCLONE\DATA\MANDB11G\DATAFILE\O1_MF_SYSAUX_741CPC5V_.DBF', <==Change

'D:\APP\USERXP\ORADATA\MANCLONE\DATA\O1_MF_SYS_UNDO_741CPFMK_.DBF', <==Change

'D:\APP\USERXP\ORADATA\MANCLONE\DATA\O1_MF_USERDATA_741CY41G_.DBF' <==Change

CHARACTER SET US7ASCII

;

How to overcome the error ( CRS-0223 resource has placement error) when starting rac database using srvctl

When starting the RAC database using srvctl you may get the following error
CRS-0223 resource has placement error
the reason behind this the ASM instances are not started before
Lets consider we run a two node rac setup
where nodes are rac1,rac2
do the following to overcome the error
Step 1.
login to rac1 using putty as root
login as oracle user
$crsctl check crs
make sure all the status is UP
Step 2.
do the following in rac1 as oracle user
$export ORACLE_SID=+ASM1
$sqlplus ‘/ as sysdba’
SQL>select * from v$instance;
ensure that the ASM1 is up..
if not start it
you can start also using $srvctl start asm -n rac1
Step 3.
do the following in rac2 as oracle user
$export ORACLE_SID=+ASM2
$sqlplus ‘/ as sysdba’
SQL>select * from v$instance;
ensure that the ASM2 is up..
if not start it
you can start also using $srvctl start asm -n rac2
Step 4.
verify the listener is up in both nodes
lsnrctl status , if not start in rac1
using $lsnrctl start LISTENER_RAC1
in rac2
$lsnrctl status , if not started then start it
using $lsnrctl start LISTENER_RAC2
Step 5.
login to tmprac1 and start the database using
$srvctl start database -d
if it is giving any error then start it using sqlplus
$sqlplus ‘ / as sysdba’
SQL>startup
do this in rac2 also
after everything login to sqlplus ‘/ as sysdba’
SQL>select * from v$active_instances;
it will display two RAC database instances;;
so now the startup is complete..

How to enable Wampserver when Oracle Agent is Running

First of all both Wampserver and Oracle Sql PLus utilises the same web server Apache .Since you have installed both the oracle and the wampserver suite .. it means that you have installed Apache Web Server two times .
Databases Normalization E Book(R. Comprehensive EBook(R) On Normalization Techniques For General Relational Databases Such As Oracle, Sql Server And Others.!
The one which is installed recently will be working fine and the firstly installed suite will not work since it will be disabled by the recently installed suite.
Or else the Oracle will work fine since the oracle agent and the oracle HTTP server will be activated while booting . Since wampserver is activated only when th developer starts it , it will show that the wampserver is offline in the quick launch toolbar .
For rectifying this problem,Do the following steps:-
Step 1:
Start the Wampserver and wait for the wampserver icon to launch in the quick launch toolbar.Then click on the icon anselect -> localhost.


Step 2:
Step 1 results in the following page.

Step 3:
Oracle 10g Database, Oracle 9i, Oracle 11g, Oracle 11i & Oracle R12 Student Guides.
Now select the following Wampserver icon -> Apache ->httpd.conf

Step 4:
Now the httpd.conf file opens ,then search for the text listen and then change the port no Listen 80 to
ip address:8080
Listen 8080
as shown below in the image


Step 5:
Now restart the wampserver and click put online if the below screen is displayed.

Step 6:
If that doesnt work out, do this and stop the Oracle services which are automatic as shown below..


Step 7:
Then repeat the step 5 .Then click on the icon anselect -> localhost.
I hope ur wampserver works fine ... if u still have doubt comment here ...

Manual Database Creation Oracle 11g

Manually Database Creation is one of the most important works of aDBA.

The procedure is same as previous which we are used for 9i, 10g.

In this database creation I used below features

1. OMF (Oracle Managed File) for datafiles, redolog files & controlfiles
2. FRA (Flash Recovery Area) for Archivelog or backup files

1. Create Required Directories

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\UserXP>mkdir D:\app\UserXP\admin\mandb11g

2. Set Enviourment Variable (ORACLE_HOME,ORACLE_SID,PATH)

C:\Documents and Settings\UserXP>set ORACLE_HOME=D:\app\UserXP\product\11.1.0\db
_1

C:\Documents and Settings\UserXP>set PATH=D:\app\UserXP\product\11.1.0\db_1\BIN

C:\Documents and Settings\UserXP>set ORACLE_SID=mandb11g

3. Creating INIT.ORA parameter file 

Set minimum required parameter in INIT.ora file.

Open NOTEPAD file and set below parameters

db_name = 'MANDB11G'
#Database name.

db_create_file_dest='D:\app\UserXP\oradata\mandb11g\data'
#OMF configuration for Datafile,controlfile
db_create_online_log_dest_1='D:\app\UserXP\oradata\mandb11g\redo'
#OMF configuration for redolog file

db_recovery_file_dest='D:\app\UserXP\oradata\mandb11g\flash_recovery'
db_recovery_file_dest_size=4G
#FRA (FLASH RECOVERY AREA configuration)

diagnostic_dest='D:\app\UserXP\admin\mandb11g'
#It is new feature with 11g for trace files (bdump,udump,cdump or many others
#folder created in "DIAG" folder inside "D:\app\UserXP\admin\mandb11g" folder.

Save it in Temporary folder.

4. Create an Instance

C:\Documents and Settings\UserXP>oradim -new -sid mandb11g -startmode auto

Instance created.

5. Connect to an Instance

C:\Documents and Settings\UserXP>sqlplus /nolog

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 9 05:49:42 2011

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

SQL> conn / as sysdba

Connected to an idle instance.

6. Create Server Parameter file (SPFILE)

 

SQL> create spfile from pfile='D:\init.ora';

File created.

7. Start the Instance

SQL> startup nomount

ORACLE instance started.

Total System Global Area  150667264 bytes

Fixed Size                  1345868 bytes

Variable Size              92276404 bytes

Database Buffers           50331648 bytes

Redo Buffers                6713344 bytes

7. Start the Instance

 

SQL> create database mandb11g;

Database created.

9. Create Temporary and Additional Tablespace

SQL> create temporary tablespace temp tempfile size 5m;

Tablespace created.

SQL> create tablespace userdata datafile size 10m autoextend on;

Tablespace created.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> alter database default tablespace userdata;

Database altered.

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  150667264 bytes

Fixed Size                  1345868 bytes

Variable Size              92276404 bytes

Database Buffers           50331648 bytes

Redo Buffers                6713344 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

10. Run Scripts to build data dictionary views

@%ORACLE_HOME%/rdbms/admin/catalog.sql
@%ORACLE_HOME%/rdbms/admin/catproc.sql
@%ORACLE_HOME%/sqlplus/admin/pupbld.sql

11. Change Database Mode from NO-ARCHIVELOG to ARCHIVELOG.

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

SQL> startup mount
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1331740 bytes
Variable Size 92278244 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
SQL> alter database ARCHIVELOG;
Database altered.
SQL> alter database OPEN;
Database altered.

NOTE: It is recommended to run database in ARCHIVELOG MODE

12. Check all database files

SQL> select NAME from v$datafile

  2  union all

  3  select NAME from v$controlfile

  4  union all

  5  select MEMBER from v$logfile;

NAME

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

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYSTEM_741CP7KL_.DBF

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYSAUX_741CPC5V_.DBF

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_SYS_UNDO_741CPFMK_.D

BF

D:\APP\USERXP\ORADATA\MANDB11G\DATA\MANDB11G\DATAFILE\O1_MF_USERDATA_741CY41G_.D

BF

D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\CONTROLFILE\O1_MF_741CP37T_.CTL

D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\ONLINELOG\O1_MF_1_741CP3PG_.LOG

D:\APP\USERXP\ORADATA\MANDB11G\REDO\MANDB11G\ONLINELOG\O1_MF_2_741CP5D6_.LOG

7 rows selected.

SQL>