18.3.2 create a physical standby database using RMAN backup on different hosts

(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) copy relevant files to the backup host

(7) Enable the backup routine and create a backup database

(8) Start the backup database

Back up the database:

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

Recovery MANAGER: Release 10.2.0.1.0-production on Tuesday February 17 11:56:38 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 format = 'd: \ backup \ % d _ % S. Bak'
2> plus archivelog format = 'd: \ backup \ % d _ % S. Bak ';

Start backup from 17-2 to 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 = 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
Input archiving log thread = 1 sequence = 103 Record ID = 278 timestamp = 678997197
Input archiving log thread = 1 sequence = 104 Record ID = 282 timestamp = 679053757
Input archiving log thread = 1 sequence = 105 record ID = 292 timestamp = 679054584
Input archiving log thread = 1 sequence = 106 Record ID = 295 timestamp = 679055783
Input archiving log thread = 1 sequence = 107 Record ID = 298 timestamp = 679055906
Input archiving log thread = 1 sequence = 108 Record ID = 301 timestamp = 679060674
Channel ora_disk_1: Starting segment 1 from 17-2 to 09
Channel ora_disk_1: Completed segment 1 from 17-2 to 09
Segment handle = D: \ backup \ demo_55.bak flag = tag20090217t115755 annotation = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 10
Backup completed from 17-2 to 09

Start backup from 17-2 to 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 17-2 to 09
Channel ora_disk_1: Completed segment 1 from 17-2 to 09
Segment handle = D: \ backup \ demo_56.bak flag = tag20090217t115808 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 17-2 to 09
Channel ora_disk_1: Completed segment 1 from 17-2 to 09
Segment handle = D: \ backup \ demo_57.bak mark = tag20090217t115808 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 03
Backup completed from 17-2 to 09

Start backup from 17-2 to 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 = 109 Record ID = 305 timestamp = 679060807
Channel ora_disk_1: Starting segment 1 from 17-2 to 09
Channel ora_disk_1: Completed segment 1 from 17-2 to 09
Segment handle = D: \ backup \ demo_58.bak mark = tag20090217t120007 comment = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 02
Backup completed from 17-2 to 09

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

Start backup from 17-2 to 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 17-2 to 09
Channel ora_disk_1: Completed segment 1 from 17-2 to 09
Segment handle = D: \ backup \ demo_59.bak flag = tag20090217t120044 annotation = none
Channel ora_disk_1: Backup set completed. elapsed time: 00: 00: 02
Backup completed from 17-2 to 09

RMAN> exit

The recovery manager is complete.

 

Operations on the host where the database is copied:

C: \> mkdir D: \ standby4

C: \> mkdir D: \ standby4 \ adump

C: \> mkdir D: \ standby4 \ bdump

C: \> mkdir D: \ standby4 \ cdump

C: \> mkdir D: \ standby4 \ udump

C: \> mkdir D: \ standby4 \ archive

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

Modify listener. ora:

(Sid_desc =
(Global_dbname = standby4)
(ORACLE_HOME = D: \ oracle \ product \ 10.2.0 \ db_1)
(Sid_name = standby4)
)

Modify tnsnames. ora:

Standby4 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = testpc) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = standby4)
)
)

 

Operations on the host where the primary database is located:

Modify tnsnames. ora:

Standby4 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = testpc) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = standby4)
)
)

 

SQL> conn sys/orcl @ demo as sysdba
Connected.
SQL> Create pfile from spfile;

The file has been created.

Modify initdemo. ora:

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

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> Create pfile = '% ORACLE_HOME % \ database \ initstandby4.ora'
2 from spfile;

The file has been created.

Modify initstandby4.ora:

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

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

The file has been created.

SQL> host Copy D: \ backup \ *. * \ testpc \ backup \*.*
D: \ backup \ demo_50.bak
D: \ backup \ demo_51.bak
D: \ backup \ demo_52.bak
D: \ backup \ demo_53.bak
D: \ backup \ demo_54.bak
Five files have been copied.

SQL> host copy % ORACLE_HOME % \ database \ spfilestandby4.ora \ testpc \ ORACLE_HOME \ da
Tabase \ spfilestandby4.ora
1 file has been copied.
SQL> conn sys/orcl @ standby4 as sysdba
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: \> sqlplus sys/orcl @ demo as sysdba

SQL * Plus: Release 10.2.0.1.0-production on Tuesday February 17 12:15:49 2009

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

Already connected to the idle routine.

SQL> startup
The Oracle routine has been started.

Total system global area 603979776 bytes
Fixed size 1250380 bytes
Variable Size 281021364 bytes
Database buffers 314572800 bytes
Redo buffers 7135232 bytes
The database has been loaded.
The database has been opened.
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 @ standby4

Recovery MANAGER: Release 10.2.0.1.0-production on Tuesday February 17 12:08:04 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 = 155 devtype = Disk

Memory script content:
{
Set until SCN 1873968;
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: restoring the control file

Failover to the previous backup

......................................................................................................

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
D: \ standby3 \ archive \ arc001__0674493001.001
D: \ standby3 \ archive \ arc001__0674493001.001

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

D: \ standby3 \ archive \ arc001__0674493001.001
D: \ standby3 \ archive \ arc001__0674493001.001
D: \ standby3 \ archive \ arc00110_0674493001.001

14 rows have been selected.

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
D: \ standby3 \ archive \ arc001__0674493001.001
D: \ standby3 \ archive \ arc001__0674493001.001

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

D: \ standby3 \ archive \ arc001__0674493001.001
D: \ standby3 \ archive \ arc001__0674493001.001
D: \ standby3 \ archive \ arc00110_0674493001.001
D: \ standby3 \ archive \ arc00111_0674493001.001

15 rows have been selected.