18.2.1 using user management backup on the same host to create a physical standby Database

Last Update:2018-12-06 Source: Internet

Author: User

Tags time 0

Developer on Alibaba Coud: Build your first app with APIs, SDKs, and tutorials on the Alibaba Cloud. Read more ＞

(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 related files to the directory of the standby Database

(7) Enable the standby Database

(8) add temporary files to the standby Database

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

SQL * Plus: Release 10.2.0.1.0-production on Saturday February 14 09:33:26 2009

Connect:
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 $ datafile;

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

D: \ demo \ system01.dbf
D: \ demo \ undotbs01.dbf
D: \ demo \ sysaux01.dbf
D: \ demo \ users01.dbf

SQL> host mkdir D: \ backup

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 system archive log current;

The system has been changed.

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

The database 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: \> mkdir c: \ standby1

C: \> mkdir c: \ standby1 \ adump

C: \> mkdir c: \ standby1 \ bdump

C: \> mkdir c: \ standby1 \ cdump

C: \> mkdir c: \ standby1 \ udump

C: \> mkdir c: \ standby1 \ archive

C: \> mkdir D: \ standby1

C: \> mkdir D: \ standby1 \ archive

C: \> oradim-New-Sid standby1-syspwd Oracle
The instance has been created.

Modify the listener. ora file:

Listener. ora
(Sid_desc =
(Global_dbname = standby1)
(ORACLE_HOME = D: \ oracle \ product \ 10.2.0 \ db_1)
(Sid_name = standby1)
)

Modify the tnsnames. ora file:

Tnsnames. ora
Standby1 =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = Colin-Dell) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = standby1)
)
)

C: \> LSNRCTL

LSNRCTL for 32-bit windows: Version 10.2.0.1.0-production on month-2009
2: 54

Welcome to LSNRCTL. Enter "help" for information.

LSNRCTL> stop
Connecting to (description = (address = (Protocol = TCP) (host = Colin-Dell) (Port = 1521 )))
Command executed successfully
LSNRCTL> Start
Start tnslsnr: Please wait...

Tnslsnr for 32-bit windows: Version 10.2.0.1.0-Production
The system parameter file is D: \ oracle \ product \ 10.2.0 \ db_1 \ Network \ admin \ listener. ora.
Write the log information of D: \ oracle \ product \ 10.2.0 \ db_1 \ Network \ log \ listener. Log
Listener: (description = (address = (Protocol = TCP) (host = Colin-Dell) (Port = 1521 )))
Listener: (description = (address = (Protocol = IPC) (pipename = \. \ PIPE \ extproc0ipc )))

Connecting to (description = (address = (Protocol = TCP) (host = Colin-Dell) (Port = 1521 )))
Listener status
------------------------
Alias listener
Version tnslsnr for 32-bit windows: Version 10.2.0.1.0-produ
Ction
Start Date: 14-2-2009 09:43:02
Normal operation time 0 days 0 hours 0 minutes 3 seconds
Tracking level off
Security on: Local OS Authentication
SNMP off
Listener parameter file D: \ oracle \ product \ 10.2.0 \ db_1 \ Network \ admin \ listener. o
RA
Listener log file D: \ oracle \ product \ 10.2.0 \ db_1 \ Network \ log \ listener. Log

Listener endpoint overview...
(Description = (address = (Protocol = TCP) (host = Colin-Dell) (Port = 1521 )))
(Description = (address = (Protocol = IPC) (pipename = \. \ PIPE \ extproc0ipc )))
Service summary ..
The Service "plsextproc" contains one routine.
Routine "plsextproc", status unknown, contains one handler of this service...
The Service "Demo" contains one routine.
Routine "Demo", status unknown, contains 1 handler of this service...
The Service "standby1" contains one routine.
Routine "standby1", status unknown, contains 1 handler of this service...
Command executed successfully
LSNRCTL> exit

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

SQL * Plus: Release 10.2.0.1.0-production on Saturday February 14 09:45:09 2009

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 following parameter values:

Db_unique_name = 'Demo'
Log_archive_dest_1 = 'location = D: \ demo \ archive valid_for = (all_logfiles, all_roles) db_unique_name = demo'
Log_archive_dest_2 = 'location = c: \ demo \ archive valid_for = (all_logfiles, all_roles) db_unique_name = demo'
Log_archive_dest_3 = 'service = standby1 valid_for = (online_logfiles, primary_role) db_unique_name = standby1'
Log_archive_config = 'dg _ Config = (demo, standby1 )'
Fal_server = 'standby1'
Fal_client = 'Demo'

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 \ initstandby1.ora' from spfile;

The file has been created.

Modify the following parameter values:

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

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

The file has been created.

SQL> host Copy D: \ backup \ *. DBF c: \ standby1
D: \ backup \ sysaux01.dbf
D: \ backup \ system01.dbf
D: \ backup \ undotbs01.dbf
D: \ backup \ users01.dbf
Four files have been copied.

SQL> host Copy D: \ backup \ *. CTL c: \ standby1
D: \ backup \ control01.ctl
1 file has been copied.

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/Oracle @ standby1 as sysdba

SQL * Plus: Release 10.2.0.1.0-production on Saturday February 14 11:07:14 2009

Already connected to the idle routine.
SQL> startup Mount
The Oracle routine has been started.

Total system global area 209715200 bytes
Fixed size 1248116 bytes
Variable Size 67110028 bytes
Database buffers 134217728 bytes
Redo buffers 7139328 bytes
The database has been loaded.

SQL> recover standby database until cancel;
ORA-00279: Change 604870 (generated at 02/14/2009 09:35:36) is required for thread 1
ORA-00289: Recommended: C: \ standby1 \ archive \ arc00028_0678192909.001
ORA-00280: Change 604870 (for thread 1) in sequence #28