18.3.1 create a physical standby database using RMAN backup on the same host

(1) Create an OS directory for storing backup database-Related Files

(2) create a routine service

(3) configure the listener and Network Service name

(4) Prepare the parameter file of the primary database

(5) Prepare backup database parameter files

(6) Enable the standby routine and establish the standby Database

(7) Enable the standby Database

Back up the database first:

C: \> RMAN target sys/orcl @ demo nocatalog

Recovery MANAGER: Release 10.2.0.1.0-production on Monday February 16 18:11:41 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect to the target database: Demo (dbid = 3411805505)
Use the target database control file to replace the recovery directory

RMAN> Backup Database Plus archivelog
2> Format = 'd: \ backup \ % d _ % S. Bak ';

Start backup from 16-09
The current log is archived.
Allocated channel: ora_disk_1
Channel ora_disk_1: SID = 154 devtype = Disk
Channel ora_disk_1: archiving log backup set is being started
Channel ora_disk_1: archiving logs in the specified backup set
Input archiving log thread = 1 sequence = 93 Record ID = 241 timestamp = 678383978
Input archiving log thread = 1 sequence = 94 Record ID = 243 timestamp = 678968789
Input archiving log thread = 1 sequence = 95 Record ID = 246 timestamp = 678969070
Input archiving log thread = 1 sequence = 96 record ID = 247 timestamp = 678974456
Input archiving log thread = 1 sequence = 97 record ID = 250 timestamp = 678974463
Input archiving log thread = 1 sequence = 98 Record ID = 253 timestamp = 678974698
Input archiving log thread = 1 sequence = 99 Record ID = 256 timestamp = 678974716
Input archiving log thread = 1 sequence = 100 Record ID = 259 timestamp = 678991521
Input archiving log thread = 1 sequence = 101 Record ID = 270 timestamp = 678991647
Input archiving log thread = 1 sequence = 102 Record ID = 274 timestamp = 678997062
Channel ora_disk_1: Starting segment 1 from 16-09
Channel ora_disk_1: Completed segment 1 from 16-09
Segment handle = D: \ backup \ demo_45.bak mark = tag20090216t181743 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 09
Backup completed at 16-09

Start backup from 16-09
Use channel ora_disk_1
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
Enter data file fno = 00001 name = D: \ demo \ system01.dbf
Enter the data file fno = 00004 name = D: \ demo \ users01.dbf
Enter the data file fno = 00003 name = D: \ demo \ sysaux01.dbf
Enter the data file fno = 00002 name = D: \ demo \ undotbs01.dbf
Channel ora_disk_1: Starting segment 1 from 16-09
Channel ora_disk_1: Completed segment 1 from 16-09
Segment handle = D: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ demo \ backupset \ 2009_02_16 \ O1
_ Mf_nnndf_tag20090216t181755_4slhbnw6_.bkp tag = tag20090216t181755 annotation = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 01: 55
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
The backup set includes the current control file
Include the current spfile in the backup set
Channel ora_disk_1: Starting segment 1 from 16-09
Channel ora_disk_1: Completed segment 1 from 16-09
Segment handle = D: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ demo \ backupset \ 2009_02_16 \ O1
_ Mf_ncsnf_tag20090216t181755_4slhg91t_.bkp mark = tag20090216t181755 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 06
Backup completed at 16-09

Start backup from 16-09
The current log is archived.
Use channel ora_disk_1
Channel ora_disk_1: archiving log backup set is being started
Channel ora_disk_1: archiving logs in the specified backup set
Input archiving log thread = 1 sequence = 103 Record ID = 279 timestamp = 678997197
Channel ora_disk_1: Starting segment 1 from 16-09
Channel ora_disk_1: Completed segment 1 from 16-09
Segment handle = D: \ backup \ demo_48.bak flag = tag20090216t181957 annotation = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 02
Backup completed at 16-09

RMAN> Backup current controlfile for standby
2> Format = 'd: \ backup \ % d _ % S. Bak ';

Start backup from 16-09
Use channel ora_disk_1
Channel ora_disk_1: Start all data file backup Sets
Channel ora_disk_1: Specifies the data file in the backup set.
Backup set includes backup control files
Channel ora_disk_1: Starting segment 1 from 16-09
Channel ora_disk_1: Completed segment 1 from 16-09
Segment handle = D: \ backup \ demo_49.bak flag = tag20090216t182153 annotation = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 02
Backup completed at 16-09

RMAN> exit

The recovery manager is complete.

Create an OS directory for storing backup database related files:

C: \> mkdir D: \ standby3 \ adump

C: \> mkdir D: \ standby3 \ bdump

C: \> mkdir D: \ standby3 \ cdump

C: \> mkdir D: \ standby3 \ udump

C: \> mkdir D: \ standby3 \ archive

C: \> oradim-New-Sid standby3-syspwd orcl
The instance has been created.

C: \> sqlplus sys/orcl @ demo as sysdba

SQL * Plus: Release 10.2.0.1.0-production on Monday February 16 18:25:22 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options

SQL> Create pfile from spfile;

The file has been created.

Modify the parameter file initstandby1.ora:

Fal_client = 'Demo'
Fal_server = 'standby1, standby2, standby3'
Log_archive_config = 'dg _ Config = (demo, standby1, standby2, standby3 )'
Log_archive_dest_5 = 'service = standby3 valid_for = (online_logfiles, primary_role) db_unique_name = standby3'
Standby_file_management = 'auto'

SQL> shutdown immediate
The database has been closed.
The database has been detached.
The Oracle routine has been disabled.
SQL> Create spfile from pfile;

The file has been created.

SQL> startup
The Oracle routine has been started.

Total system global area 603979776 bytes
Fixed size 1250380 bytes
Variable Size 272632756 bytes
Database buffers 322961408 bytes
Redo buffers 7135232 bytes
The database has been loaded.
The database has been opened.
SQL> Create pfile = '% ORACLE_HOME % \ database \ initstandby3.ora'
2 from spfile;

The file has been created.

Modify the parameter file initstandby3.ora:

Audit_file_dest = 'd: \ standby3 \ adump'
Background_dump_dest = 'd: \ standby3 \ bdump'
Control_files = 'd: \ standby3 \ control01.ctl'
Core_dump_dest = 'd: \ standby3 \ cdump'
User_dump_dest = 'd: \ standby3 \ udump'
Standby_file_management = 'auto'
Db_unique_name = 'standby3'
Service_names = standby3
Instance_name = standby3
Fal_client = 'standby3'
Fal_server = 'Demo'
Log_archive_config = 'dg _ Config = (demo, standby1, standby2, standby3 )'
Log_archive_dest_1 = 'location = D: \ standby3 \ archive valid_for = (all_logfiles, all_roles) db_unique_name = standby3'
Log_archive_dest_2 = 'service = demo valid_for = (online_logfiles, primary_role) db_unique_name = demo'
Db_file_name_convert = 'd: \ demo ', 'd: \ standby3'
Log_file_name_convert = 'd: \ demo \ ', 'd: \ standby3', 'c: \ demo', 'standby3'
Standby_archive_dest = 'd: \ standby3 \ archive'

SQL> Create spfile = '% ORACLE_HOME % \ database \ spfilestandby3.ora'
2 from pfile = '% ORACLE_HOME % \ database \ initstandby3.ora ';

The file has been created.

SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options disconnected

C: \> sqlplus sys/orcl @ standby3 as sysdba

SQL * Plus: Release 10.2.0.1.0-production on Tuesday February 17 10:08:37 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Already connected to the idle routine.

SQL> startup nomount
The Oracle routine has been started.

Total system global area 603979776 bytes
Fixed size 1250380 bytes
Variable Size 163580852 bytes
Database buffers 432013312 bytes
Redo buffers 7135232 bytes
SQL> exit
From Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options disconnected

C: \> RMAN target sys/orcl @ demo auxiliary sys/orcl @ standby3

Recovery MANAGER: Release 10.2.0.1.0-production on Tuesday February 17 10:09:38 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect to the target database: Demo (dbid = 3411805505)
Connected to secondary Database: Demo (not loaded)

RMAN> duplicate target database for standby dorecover;

Start duplicate DB from 17-2 to 09
Use the target database control file to replace the recovery directory
Allocated channel: ora_aux_disk_1
Channel ora_aux_disk_1: SID = 157 devtype = Disk

Memory script content:
{
Set until SCN 1869662;
Restore clone standby controlfile;
SQL clone 'alter database Mount standby database ';
}
Executing memory script

Executing command: set until clause

Start restore from 17-2 to 09
Use channel ora_aux_disk_1

Channel ora_aux_disk_1: recovering the data file backup set
Channel ora_aux_disk_1: restoring the control file
Channel ora_aux_disk_1: Reading the backup segment D: \ backup \ demo_49.bak
Channel ora_aux_disk_1: recovered backup segment 1
Segment handle = D: \ backup \ demo_49.bak flag = tag20090216t182153
Channel ora_aux_disk_1: Recovery completed, time: 00:00:02
Output file name = D: \ standby3 \ control01.ctl
Complete the restore from 17-2 to 09

SQL statement: Alter database Mount standby Database
Released channel: ora_aux_disk_1

Memory script content:
{
Set until SCN 1869662;
Set newname for tempfile 1
"D: \ standby3 \ temp01.dbf ";
Switch clone tempfile all;
Set newname for datafile 1
"D: \ standby3 \ system01.dbf ";
Set newname for datafile 2
"D: \ standby3 \ undotbs01.dbf ";
Set newname for datafile 3
"D: \ standby3 \ sysaux01.dbf ";
Set newname for datafile 4
"D: \ standby3 \ users01.dbf ";
Restore
Check readonly
Clone Database
;
}
Executing memory script

Executing command: set until clause

Executing command: Set newname

Temporary File 1 has been renamed to D: \ standby3 \ temp01.dbf in the control file

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Executing command: Set newname

Start restore from 17-2 to 09
Allocated channel: ora_aux_disk_1
Channel ora_aux_disk_1: SID = 156 devtype = Disk

Channel ora_aux_disk_1: recovering the data file backup set
Channel ora_aux_disk_1: Specifies the data file to be recovered from the backup set.
Restoring data file 00001 to D: \ standby3 \ system01.dbf
Restoring data file 00002 to D: \ standby3 \ undotbs01.dbf
Restoring data file 00003 to D: \ standby3 \ sysaux01.dbf
Restoring data file 00004 to D: \ standby3 \ users01.dbf
Channel ora_aux_disk_1: Reading the backup segment D: \ oracle \ product \ 10.2.0 \ flash_recovery_area
\ Demo \ backupset \ 2009_02_16 \ o1_mf_nnndf_tag20090216t181755_4slhbnw6_.bkp
Channel ora_aux_disk_1: recovered backup segment 1
Segment handle = D: \ oracle \ product \ 10.2.0 \ flash_recovery_area \ demo \ backupset \ 2009_02_16 \
O1_mf_nnndf_tag20090216t181755_4slhbnw6_.bkp tag = tag20090216t181755
Channel ora_aux_disk_1: Recovery completed, time: 00:02:06
Complete the restore from 17-2 to 09

Memory script content:
{
Switch clone datafile all;
}
Executing memory script

Data File 1 has been converted to a data file copy
Input data file copy recid = 30 stamp = 679054458 file name = D: \ standby3 \ system01.dbf
Data File 2 has been converted to a data file copy
Input data file copy recid = 31 stamp = 679054459 file name = D: \ standby3 \ undotbs01.dbf
Data File 3 has been converted to a data file copy
Input data file copy recid = 32 stamp = 679054459 file name = D: \ standby3 \ sysaux01.dbf
Data File 4 has been converted to a data file copy
Input data file copy recid = 33 stamp = 679054459 file name = D: \ standby3 \ users01.dbf

Memory script content:
{
Set until SCN 1869662;
Recover
Standby
Clone Database
Delete archivelog
;
}
Executing memory script

Executing command: set until clause

Start recover from 17-2 to 09
Use channel ora_aux_disk_1

Restoring media...

Archive log thread 1 sequence 103 already exists as a file D: \ demo \ archive \ arc00103_0674493001.001
On disk
Archive log thread 1 sequence 104 already exists as a file D: \ demo \ archive \ arc001__0674493001.001
On disk
Archive log file name = D: \ demo \ archive \ arc00103_0674493001.001 thread = 1 sequence = 103
Archive log file name = D: \ demo \ archive \ arc001__0674493001.001 thread = 1 sequence = 104
Media Recovery completed. Time: 00:00:05
Complete the recover in month-09
The duplicate dB is completed from 17-2 to 09.

RMAN> exit

The recovery manager is complete.

C: \> sqlplus sys/orcl @ standby3 as sysdba

SQL * Plus: Release 10.2.0.1.0-production on Tuesday February 17 10:15:48 2009

Copyright (c) 1982,200 5, Oracle. All rights reserved.

Connect:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Production
With the partitioning, OLAP and Data Mining options

SQL> select status from V $ instance;

Status
------------
Mounted

SQL> conn sys/orcl @ demo as sysdba
Connected.
SQL> alter system switch logfile;

The system has been changed.

SQL> conn sys/orcl @ standby3 as sysdba
Connected.
SQL> select name from V $ archived_log;

Name
--------------------------------------------------------------------------------

D: \ standby3 \ archive \ arc00093_0674493001.001
D: \ standby3 \ archive \ arc00094_0674493001.001
D: \ standby3 \ archive \ arc00095_0674493001.001
D: \ standby3 \ archive \ arc00096_0674493001.001
D: \ standby3 \ archive \ arc00097_0674493001.001
D: \ standby3 \ archive \ arc00098_0674493001.001
D: \ standby3 \ archive \ arc00099_0674493001.001
D: \ standby3 \ archive \ arc00100_0674493001.001
D: \ standby3 \ archive \ arc00105_0674493001.001

Nine rows have been selected.

SQL> alter database open;

The database has been changed.

SQL> select name from V $ tempfile;

Name
--------------------------------------------------------------------------------

D: \ standby3 \ temp01.dbf