Monday, February 13, 2012

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
;









Oracle 10g Database manual Cloning Using Cold BackupSocialTwist Tell-a-Friend
Posted by Suvin Prathab at 5:13 AM 0 comments

Tuesday, January 24, 2012

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 overcome the error ( CRS-0223 resource has placement error) when starting rac database using srvctlSocialTwist Tell-a-Friend
Posted by Suvin Prathab at 3:51 AM 0 comments
Subscribe to: Posts (Atom)