Thursday, September 20, 2012


  1. Prepare primary database

Ø  Enable database logging

 

C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL\ONLINELOG>sqlplus

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 01:46:03 2012

 

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

 

Enter user-name: sys /as sysdba

Enter password:

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> alter database force logging;

 

Database altered.

 

SQL> host

Microsoft Windows XP [Version 5.1.2600]

(C) Copyright 1985-2001 Microsoft Corp.

 

C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL\ONLINELOG>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL\ONLINELOG

 

09/19/2012  01:02 AM    <DIR>          .

09/19/2012  01:02 AM    <DIR>          ..

               0 File(s)              0 bytes

               2 Dir(s)   1,328,300,032 bytes free

 

C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL\ONLINELOG>cd ..

 

C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL

 

09/19/2012  01:02 AM    <DIR>          .

09/19/2012  01:02 AM    <DIR>          ..

09/19/2012  01:02 AM    <DIR>          ONLINELOG

               0 File(s)              0 bytes

               3 Dir(s)   1,328,300,032 bytes free

 

C:\oracle\product\10.2.0\flash_recovery_area\MRUHTEL>cd..

 

C:\oracle\product\10.2.0\flash_recovery_area>cd ..

 

C:\oracle\product\10.2.0>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0

 

09/05/2012  06:45 AM    <DIR>          .

09/05/2012  06:45 AM    <DIR>          ..

09/19/2012  01:00 AM    <DIR>          admin

09/19/2012  01:04 AM    <DIR>          db_1

09/19/2012  01:02 AM    <DIR>          flash_recovery_area

09/19/2012  01:00 AM    <DIR>          oradata

               0 File(s)              0 bytes

               6 Dir(s)   1,328,300,032 bytes free

 

C:\oracle\product\10.2.0>cd db_1

 

C:\oracle\product\10.2.0\db_1>dir database

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\db_1\database

 

09/19/2012  01:04 AM    <DIR>          .

09/19/2012  01:04 AM    <DIR>          ..

09/05/2012  01:05 AM    <DIR>          archive

09/19/2012  01:00 AM             2,048 hc_mruhtel.dat

09/18/2012  08:43 PM             2,048 hc_miki.dat

09/18/2012  11:51 PM             2,048 hc_mruhtel.dat

09/19/2012  12:17 AM             2,048 hc_mruhtel2.dat

09/05/2012  04:15 AM             2,048 hc_orcl.dat

09/05/2012  05:10 AM             2,048 hc_orcl1.dat

09/18/2012  08:46 PM             2,048 hc_stbymiki.dat

09/05/2012  05:01 AM             2,048 hc_test.dat

09/19/2012  01:04 AM                62 initmruhtel.ora

06/25/2005  03:18 AM            31,744 oradba.exe

09/19/2012  12:59 AM             3,608 oradim.log

09/19/2012  01:04 AM             1,536 PWDmruhtel.ora

09/19/2012  12:24 AM             1,536 PWDmruhtel2.ora

              13 File(s)         54,870 bytes

               3 Dir(s)   1,328,300,032 bytes free

 

Ø  Copy the password file for the new database

C:\oracle\product\10.2.0\db_1>cd database

 

C:\oracle\product\10.2.0\db_1\database>copy PWDmruhtel.ora PWDmruhtel2.ora

        1 file(s) copied.

 

C:\oracle\product\10.2.0\db_1\database>dir PWD*

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\db_1\database

 

09/19/2012  01:04 AM             1,536 PWDmruhtel.ora

09/19/2012  01:04 AM             1,536 PWDmruhtel2.ora

09/19/2012  12:24 AM             1,536 PWDmruhtel2.ora

               3 File(s)          4,608 bytes

               0 Dir(s)   1,328,279,552 bytes free

Ø  Change database parameter on the primary database (in our case using spfile). Same parameters can be changed in pfile if one is used

 

C:\oracle\product\10.2.0\db_1\database>sqlplus

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 01:53:56 2012

 

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

 

Enter user-name: sys /as sysdba

Enter password:

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name from v$database

  2  ;

 

NAME

---------

MRUHTEL

 

SQL> show parameter spfile;

 

NAME                                 TYPE        VALUE

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

spfile                               string      C:\ORACLE\PRODUCT\10.2.0\DB_1\

                                                 DBS\SPFILEMRUHTEL.ORA

SQL> alter system set db_unique_name=mruhtel scope=spfile;

 

System altered.

 

SQL> alter system set log_archive_config='dg_config=(mruhtel, mruhtel2)' scope=f

inance;

alter system set log_archive_config='dg_config=(mruhtel, mruhtel2)' scope=financ

e

                                                                          *

ERROR at line 1:

ORA-00922: missing or invalid option

 

 

SQL> alter system set log_archive_config='dg_config=(mruhtel,mruhtel2) scope=spf

ile;

alter system set log_archive_config='dg_config=(mruhtel,mruhtel2) scope=spfile

                                    *

ERROR at line 1:

ORA-01756: quoted string not properly terminated

 

 

SQL> alter system set log_archive_config='dg_config=(mruhtel,mruhtel2)' scope=sp

file

  2  ;

 

System altered.

 

SQL> alter system set log_archive_dest_1='location=c:\database\oradata\mruhtel\a

rchived_logs\ valid_for=(all_logfiles,all_roles) db_unique_name=mruhtel' scope=s

pfile

  2  ;

 

System altered.

 

SQL> alter system set log_archive_dest_2='service=mruhtel2 valid_for=(online_log

files,primary_role) db_unique_name=mruhtel2' scope=spfile;

 

System altered.

 

SQL> alter system set standby_file_management=auto scope=spfile;

 

System altered.

 

SQL> alter system set db_file_name_convert='mruhtel2','mruhtel' scope=spfile;

 

System altered.

 

SQL> alter system set log_file_name_convert='mruhtel2','mruhtel' scope=spfile;

 

System altered.

 

SQL> startup force;

ORACLE instance started.

 

Total System Global Area  293601280 bytes

Fixed Size                  1248624 bytes

Variable Size             100663952 bytes

Database Buffers          188743680 bytes

Redo Buffers                2945024 bytes

Database mounted.

Database opened.

SQL> spool off;

not spooling currently

SQL>

 

Ø  Make copy of all datafiles from the primary database location to the standby database location

 

C:\oracle\product\10.2.0\oradata\mruhtel>sqlplus

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 02:18:20 2012

 

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

 

Enter user-name: sys /as sysdba

Enter password:

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

SQL> select name from v$database;

 

NAME

---------

MRUHTEL

 

SQL> select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE             591334 19-SEP-12

         2 NOT ACTIVE             591334 19-SEP-12

         3 NOT ACTIVE             591334 19-SEP-12

         4 NOT ACTIVE             591334 19-SEP-12

 

SQL> alter database begin backup;

SQL> select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

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

         1 ACTIVE             591334 19-SEP-12

         2 ACTIVE             591334 19-SEP-12

         3 ACTIVE             591334 19-SEP-12

         4 ACTIVE             591334 19-SEP-12

 

C:\oracle\product\10.2.0\db_1\database>cd ..

 

C:\oracle\product\10.2.0\db_1>cd ..

 

C:\oracle\product\10.2.0>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0

 

09/05/2012  06:45 AM    <DIR>          .

09/05/2012  06:45 AM    <DIR>          ..

09/19/2012  01:00 AM    <DIR>          admin

09/19/2012  01:04 AM    <DIR>          db_1

09/19/2012  01:02 AM    <DIR>          flash_recovery_area

09/19/2012  01:00 AM    <DIR>          oradata

               0 File(s)              0 bytes

               6 Dir(s)   1,323,995,136 bytes free

 

C:\oracle\product\10.2.0>cd oradata

 

C:\oracle\product\10.2.0\oradata>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\oradata

 

09/19/2012  01:00 AM    <DIR>          .

09/19/2012  01:00 AM    <DIR>          ..

09/19/2012  01:03 AM    <DIR>          mruhtel

09/19/2012  12:22 AM    <DIR>          mruhtel

09/18/2012  11:03 PM    <DIR>          mruhtel2

               0 File(s)              0 bytes

               5 Dir(s)   1,323,995,136 bytes free

 

C:\oracle\product\10.2.0\oradata>cd mruhtel

 

C:\oracle\product\10.2.0\oradata\mruhtel>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\oradata\mruhtel

 

09/19/2012  01:03 AM    <DIR>          .

09/19/2012  01:03 AM    <DIR>          ..

09/19/2012  02:06 AM         7,061,504 CONTROL01.CTL

09/19/2012  02:06 AM         7,061,504 CONTROL02.CTL

09/19/2012  02:06 AM         7,061,504 CONTROL03.CTL

09/19/2012  02:05 AM        52,429,312 REDO01.LOG

09/19/2012  02:06 AM        52,429,312 REDO02.LOG

09/19/2012  02:05 AM        52,429,312 REDO03.LOG

09/19/2012  02:05 AM       251,666,432 SYSAUX01.DBF

09/19/2012  02:05 AM       503,324,672 SYSTEM01.DBF

09/19/2012  01:04 AM        20,979,712 TEMP01.DBF

09/19/2012  02:05 AM        26,222,592 UNDOTBS01.DBF

09/19/2012  02:05 AM         5,251,072 USERS01.DBF

              11 File(s)    985,916,928 bytes

               2 Dir(s)   1,323,995,136 bytes free

 

C:\oracle\product\10.2.0\oradata\mruhtel>copy *.dbf c:\backup

SYSAUX01.DBF

SYSTEM01.DBF

TEMP01.DBF

UNDOTBS01.DBF

USERS01.DBF

        5 file(s) copied.

 

 

SQL> alter database end backup;

 

Database altered.

 

SQL> select * from v$backup;

 

     FILE# STATUS                CHANGE# TIME

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

         1 NOT ACTIVE             591334 19-SEP-12

         2 NOT ACTIVE             591334 19-SEP-12

         3 NOT ACTIVE             591334 19-SEP-12

         4 NOT ACTIVE             591334 19-SEP-12

 

 

Ø  Create control file for the standby database

 

SQL>alter database create standby controlfile as 'c:\database\oradata\mruhtel\control.stby';

Database altered.

SQL>host

C:\database\oradata\mruhtel2>copy c:\backup\* .

c:\backup\CONTROL.STBY

c:\backup\SYSAUX01.DBF

c:\backup\SYSTEM01.DBF

c:\backup\TEMP01.DBF

c:\backupUNDOTBS1.DBF

            5 file(s) copied.

 

C:\database\oradata\mruhtel2>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\database\oradata\mruhtel2

 

09/19/2012  02:31 AM    <DIR>          .

09/19/2012  02:31 AM    <DIR>          ..

09/19/2012  02:21 AM    <DIR>          archived_logs

09/19/2012  02:19 AM         7,061,504 CONTROL.STB

09/19/2012  02:12 AM         7,061,504 CONTROL01.CTL

09/19/2012  02:12 AM         7,061,504 CONTROL02.CTL

09/19/2012  02:12 AM         7,061,504 CONTROL03.CTL

09/19/2012  02:05 AM       251,666,432 SYSAUX01.DBF

09/19/2012  01:04 AM        20,979,712 TEMP01.DBF

09/19/2012  02:05 AM        26,222,592 UNDOTBS01.DBF

09/19/2012  02:05 AM         5,251,072 USERS01.DBF

               8 File(s)    332,365,824 bytes

               3 Dir(s)   1,077,878,784 bytes free

 

Ø  Rename the newcreated standby control file and multiplex it

 

C:\database\oradata\mruhtel2>MOVE CONTROL.STB CONTROL01.CTL

Overwrite C:\database\oradata\mruhtel2\CONTROL01.CTL? (Yes/No/All): YES

 

C:\database\oradata\mruhtel2>COPY CONTROL01.CTL CONTROL02.CTL

Overwrite CONTROL02.CTL? (Yes/No/All): YES

        1 file(s) copied.

 

C:\database\oradata\mruhtel2>COPY CONTROL02.CTL CONTROL03.CTL

Overwrite CONTROL03.CTL? (Yes/No/All): Y

        1 file(s) copied.

 

C:\database\oradata\mruhtel2>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\database\oradata\mruhtel2

 

09/19/2012  02:50 AM    <DIR>          .

09/19/2012  02:50 AM    <DIR>          ..

09/19/2012  02:21 AM    <DIR>          archived_logs

09/19/2012  02:19 AM         7,061,504 CONTROL01.CTL

09/19/2012  02:19 AM         7,061,504 CONTROL02.CTL

09/19/2012  02:19 AM         7,061,504 CONTROL03.CTL

09/19/2012  02:05 AM       251,666,432 SYSAUX01.DBF

09/19/2012  01:04 AM        20,979,712 TEMP01.DBF

09/19/2012  02:05 AM        26,222,592 UNDOTBS01.DBF

09/19/2012  02:05 AM         5,251,072 USERS01.DBF

               7 File(s)    325,304,320 bytes

               3 Dir(s)   1,084,792,832 bytes free

 

  1. Prepare the standby database

 

Ø  Create pfile for the standby database

 

C:\oracle\product\10.2.0\db_1\database>copy initmruhtel.ora initmruhtel2.ora

        1 file(s) copied.

 

C:\oracle\product\10.2.0\db_1\database>edit initmruhtel2.ora

 

C:\oracle\product\102~1.0\db_1\database>

 

C:\database\oradata\mruhtel2>cd c:\oracle\product

 

C:\oracle\product>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product

 

09/05/2012  12:52 AM    <DIR>          .

09/05/2012  12:52 AM    <DIR>          ..

09/05/2012  06:45 AM    <DIR>          10.2.0

               0 File(s)              0 bytes

               3 Dir(s)   1,084,735,488 bytes free

 

C:\oracle\product>cd 10.2.0

 

C:\oracle\product\10.2.0>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0

 

09/05/2012  06:45 AM    <DIR>          .

09/05/2012  06:45 AM    <DIR>          ..

09/19/2012  02:25 AM    <DIR>          admin

09/19/2012  01:04 AM    <DIR>          db_1

09/19/2012  01:02 AM    <DIR>          flash_recovery_area

09/19/2012  01:00 AM    <DIR>          oradata

               0 File(s)              0 bytes

               6 Dir(s)   1,084,735,488 bytes free

 

C:\oracle\product\10.2.0>cd db_1

 

C:\oracle\product\10.2.0\db_1>cd database

 

C:\oracle\product\10.2.0\db_1\database>dir

 Volume in drive C has no label.

 Volume Serial Number is 9845-FB4A

 

 Directory of C:\oracle\product\10.2.0\db_1\database

 

09/19/2012  02:32 AM    <DIR>          .

09/19/2012  02:32 AM    <DIR>          ..

09/05/2012  01:05 AM    <DIR>          archive

09/19/2012  01:00 AM             2,048 hc_mruhtel.dat

09/18/2012  08:43 PM             2,048 hc_miki.dat

09/18/2012  11:51 PM             2,048 hc_mruhtel.dat

09/19/2012  12:17 AM             2,048 hc_mruhtel2.dat

09/05/2012  04:15 AM             2,048 hc_orcl.dat

09/05/2012  05:10 AM             2,048 hc_orcl1.dat

09/18/2012  08:46 PM             2,048 hc_stbymiki.dat

09/05/2012  05:01 AM             2,048 hc_test.dat

09/19/2012  01:04 AM                62 initmruhtel.ora

09/19/2012  02:32 AM                25 initmruhtel2.ora

06/25/2005  03:18 AM            31,744 oradba.exe

09/19/2012  12:59 AM             3,608 oradim.log

09/19/2012  01:04 AM             1,536 PWDmruhtel.ora

09/19/2012  01:04 AM             1,536 PWDmruhtel2.ora

09/19/2012  12:24 AM             1,536 PWDmruhtel2.ora

              15 File(s)         56,431 bytes

               3 Dir(s)   1,084,735,488 bytes free

 

Ø  Make sure the db_unique_name in the standby database is setup to the standby database

 

C:\oracle\product\10.2.0\db_1\database>type initmruhtel2.ora

db_unique_name=mruhtel2

 

Ø  Create the service for the standby database

 

C:\oracle\product\10.2.0\db_1\database>oradim -new -sid mruhtel2

Instance created.

 

C:\oracle\product\10.2.0\db_1\database>net start oracleservicemruhtel2

The requested service has already been started.

 

More help is available by typing NET HELPMSG 2182.

 

Ø  Connect to the standby database and start it in nomount state

 

C:\oracle\product\10.2.0\db_1\database>set oracle_sid=mruhtel2

 

C:\oracle\product\10.2.0\db_1\database>sqlplus

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 02:59:01 2012

 

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

 

Enter user-name: sys /as sysdba

Enter password:

Connected to an idle instance.

 

SQL> startup nomount

ORACLE instance started.

 

Total System Global Area  293601280 bytes

Fixed Size                  1248624 bytes

Variable Size              92275344 bytes

Database Buffers          197132288 bytes

Redo Buffers                2945024 bytes

 

Ø  Change the database parameters on the standby database

 

SQL> alter system set db_unique_name=mruhtel2 scope=spfile;

 

System altered.

 

SQL> alter system set log_archive_config='dg_config=(mruhtel,mruhtel2)' scope=sp

file;

 

System altered.

 

 

SQL> alter system set db_unique_name=mruhtel2 scope=spfile;

 

System altered.

 

SQL> alter system set log_archive_config='dg_config=(mruhtel,mruhtel2)' scope=sp

file;

 

System altered.

 

 

SQL> alter system set log_archive_dest_1='location=c:\database\oradata\mruhtel2\

archived_logs\ valid_for=(all_logfiles,all_roles) db_unique_name=mruhtel2' scope

=spfile;

 

System altered.

 

SQL>

 

SQL> alter system set log_archive_dest_2='location=mruhtel valid_for=(online_log

files,primary_role) db_unique_name=mruhtel2' scope=spfile ;

 

System altered.

 

SQL> alter system set log_file_name_convert='mruhtel','mruhtel2' scope=spfile;

 

System altered.

 

 

SQL> shutdown immediate

ORA-01507: database not mounted

 

Ø  change location of the control files to the correct destination in the pfile

 

 

ORACLE instance shut down.

SQL> create pfile from spfile;

 

 

 

SQL> recover standby database until cancel using backup controlfile;

ORA-00279: change 591334 generated at 09/19/2012 02:10:12 needed for thread 1

ORA-00289: suggestion :

C:\DATABASE\ORADATA\MRUHTEL\ARCHIVED\LOGS\ARC00005_0794365370.001

ORA-00280: change 591334 for thread 1 is in sequence #5

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\MRUHTEL2\SYSTEM01.DBF'

 

 

ORA-01112: media recovery not started

 

SQL>   recover standby database until cancel using backup controlfile;

ORA-00279: change 591334 generated at 09/19/2012 02:10:12 needed for thread 1

ORA-00289: suggestion :

C:\DATABASE\ORADATA\MRUHTEL\ARCHIVED_LOGS\ARC00005_0794365370.001

ORA-00280: change 591334 for thread 1 is in sequence #5

 

 

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below

ORA-01194: file 1 needs more recovery to be consistent

ORA-01110: data file 1:

'C:\ORACLE\PRODUCT\10.2.0\ORADATA\MRUHTEL2\SYSTEM01.DBF'

 

 

ORA-01112: media recovery not started

 

 

C:\oracle\product\102~1.0\db_1\database>sqlplus

 

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 19 04:19:40 2012

 

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

 

Enter user-name: sys /as sysdba

Enter password:

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

 

 

 

SQL> recover managed standby database disconnect from session;

Media recovery complete.

 

  1. Test the standby database