18.2.2 use user management backup on different hosts to create a physical backup database

(1) Create an OS directory for storing backup Databases

(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 standby Database

 

Operations on the host where the database is copied:

C: \> mkdir D: \ standby2

C: \> mkdir D: \ standby2 \ adump

C: \> mkdir D: \ standby2 \ bdump

C: \> mkdir D: \ standby2 \ cdump

C: \> mkdir D: \ standby2 \ udump

C: \> mkdir D: \ standby2 \ archive

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

Modify listener. ora:

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

Modify tnsnames. ora:

Standby2 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = testpc) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = standby2)
)
)

 

Operations on the host where the primary database is located:

Modify tnsnames. ora:

Standby2 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = testpc) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = standby2)
)
)

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

The file has been created.

Modify the parameter file of the primary database:

*. Db_unique_name = 'Demo'
*. Fal_client = 'Demo'
*. Fal_server = 'standby1, standby2'
*. Log_archive_dest_4 = 'service = standby2 valid_for = (online_logfiles, primary_role) db_unique_name = standby2'
*. Log_archive_config = 'dg _ Config = (demo, standby1, standby2 )'
*. 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> Create pfile = '% ORACLE_HOME % \ database \ initstandby2.ora' from spfile;

The file has been created.

Modify the parameter file of the copied database:

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

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

The file has been created.

SQL> alter database begin backup;

The database has been changed.

SQL> host Copy D: \ demo \ system01.dbf D: \ backup
1 file has been copied.

SQL> host Copy D: \ demo \ undotbs01.dbf D: \ backup
1 file has been copied.

SQL> host Copy D: \ demo \ sysaux01.dbf D: \ backup
1 file has been copied.

SQL> host Copy D: \ demo \ users01.dbf D: \ backup
1 file has been copied.

SQL> alter Database End backup;

The database has been changed.

SQL> alter database create standby controlfile as 'd: \ backup \ control01.ctl ';

The database has been changed.

SQL> host Copy D: \ backup \ *. * \ testpc \ standby2
D: \ backup \ control01.ctl
D: \ backup \ sysaux01.dbf
D: \ backup \ system01.dbf
D: \ backup \ undotbs01.dbf
D: \ backup \ users01.dbf
Five files have been copied.

SQL> host copy % ORACLE_HOME % \ database \ spfilestandby2.ora \ testpc \ ORACLE_HOME \ da
Tabase \*.*
1 file has been copied.

SQL> alter system switch logfile;

The system has been changed.

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 @ standby2 as sysdba

SQL * Plus: Release 10.2.0.1.0-production on Monday February 16 16:47:05 2009

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

Already connected to the idle routine.

SQL> startup Mount
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
The database has been loaded.
SQL> select name from V $ archived_log;

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

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

Eight rows have been selected.

SQL> recover standby database until cancel
ORA-00279: Change 1844242 (generated at 02/16/2009 16:29:36) is required for thread 1
ORA-00289: Recommended: D: \ standby2 \ archive \ arc00100_0674493001.001
ORA-00280: Change 1844242 (for thread 1) in sequence #100

Specified log: {<RET> = suggested | filename | auto | cancel}

ORA-00279: Change 1844848 (generated at 02/16/2009 16:45:19) is required for thread 1
ORA-00289: Recommended: D: \ standby2 \ archive \ arc00101_0674493001.001
ORA-00280: Change 1844848 (for thread 1) in sequence #101
ORA-00278: This recovery no longer requires the log file 'd: \ standby2 \ archive \ arc00100_0674493001.001'

Specified log: {<RET> = suggested | filename | auto | cancel}
Cancel
Media recovery has been canceled.
SQL> alter database open;

The database has been changed.

SQL> select name from V $ tempfile;

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

D: \ standby2 \ temp01.dbf