Mruhtel

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

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:\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

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

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

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

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

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

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.

Test the standby database

Mruhtel

Pages

Thursday, September 20, 2012