Create a physical oracle9i standby database on a stand-alone computer

oracle| Create | data | database

Description: The data Guard characteristics of the Oracle9i database ensure the complete protection of the information, which is one of the key features of Oracle 9i. The data Guard can create a physical standby database, or create a logical standby database, and can be mixed, and with greater flexibility. This document is the Fenng practice time to write down things, not to mention what the experience. * It's just a note from the installation process * If you have higher expectations for this, or want more information about the Oracle database data guard and Standby, please refer to the official documentation.

System Environment: Windows 2000 Professional Edition SP3 512M memory

Existing database instance (Primary) name: Demo
Pre-created Standby database instance name: Pstandby

Database version information:

Sql> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0-production
Pl/sql Release 9.2.0.1.0-production
CORE 9.2.0.1.0 Production
TNS for 32-bit windows:version 9.2.0.1.0-production
Nlsrtl Version 9.2.0.1.0-production

Preparatory work

First verify that the primary database is in archive mode
Sql> Show User
USER is "SYS"
Sql>
Sql> Archive Log List
Database Log Mode Archive mode
Automatic Archival Enabled
Archive Destination D:\oracle\oradata\demo\archive
Oldest online log sequence 42
Next Log sequence to archive 44
Current Log Sequence 44


If you are not in archive mode, adjust the database.
First commit the command to modify the SPFile:

Sql>alter system set Log_archive_start=true scope=spfile;

Then close the database instance

Sql>shutdown

Backing up a database

Sql>startup MOUNT

Sql>alter DATABASE Archivelog;
Sql>alter DATABASE OPEN;

Sql>shutdown IMMEDIATE
Backup

1. Primary Database needs to do the preparatory work:

1.1 Activate forced Logging

sql> ALTER DATABASE FORCE LOGGING;

1.2 Set Local archive target

sql> ALTER SYSTEM SET log_archive_dest_1= ' location=d:\oracle\oradata\demo\archive ' Scope=both;
This action takes effect directly


2. Create a physical standby database

2.1 Data files marked out of the primary database

Sql> select name from V$datafile;

NAME
-----------------------------------------------------

D:\ORACLE\ORADATA\DEMO YSTEM01. Dbf
D:\ORACLE\ORADATA\DEMO\UNDOTBS01. Dbf
D:\ORACLE\ORADATA\DEMO\CWMLITE01. Dbf
D:\ORACLE\ORADATA\DEMO\DRSYS01. Dbf
D:\ORACLE\ORADATA\DEMO\EXAMPLE01. Dbf
D:\ORACLE\ORADATA\DEMO\INDX01. Dbf
D:\ORACLE\ORADATA\DEMO\ODM01. Dbf
D:\ORACLE\ORADATA\DEMO\TOOLS01. Dbf
D:\ORACLE\ORADATA\DEMO\USERS01. Dbf
D:\ORACLE\ORADATA\DEMO\XDB01. Dbf
D:\ORACLE\ORADATA\DEMO\OEM_REPOSITORY. Dbf

Rows selected.

Sql>

2.2 Close instance copy data file to the established destination

Sql> SHUTDOWN IMMEDIATE;

2.3 Creating a control file for the standby database

sql> ALTER DATABASE CREATE STANDBY controlfile
As ' C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY. CTL ';

Note that the name of this control file does not duplicate the name of the primary control file.

2.4 Prepare initialization parameter file name for standby database:

sql> CREATE pfile= ' C:\DataGuard\Pstandby\DEMO\initpstandby ' from SPFILE;

2.5 Set initialization physical Standby database parameters

*.aq_tm_processes=1
*.background_dump_dest= ' D:\oracle\admin\DEMO\bdump '
*.compatible= ' 9.2.0.0.0 '
*.control_files= ' C:\DataGuard\Pstandby\DEMO\DEMOSTANDBY. CTL '
*.core_dump_dest= ' D:\oracle\admin\DEMO\cdump '
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain= '
*.db_file_multiblock_read_count=32
*.db_name= ' DEMO '
*.dispatchers= ' (protocol=tcp) (SERVICE=DEMOXDB) '
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=true
*.instance_name= ' Pstandby '
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1= ' location=d:\oracle\oradata\demo\archive '
*.log_archive_start=true
*.open_cursors=300
*.optimizer_mode= ' First_rows '
*.pga_aggregate_target=17825792
*.processes=150
*.query_rewrite_enabled= ' TRUE '
*.remote_login_passwordfile= ' EXCLUSIVE '
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.star_transformation_enabled= ' TRUE '
*.timed_statistics=true
*.undo_management= ' AUTO '
*.undo_retention=10800
*.undo_tablespace= ' UNDOTBS1 '
*.user_dump_dest= ' D:\oracle\admin\DEMO\udump '

Lock_name_space=pstandby
Standby_file_management=auto
Remote_archive_enable=true
Standby_archive_dest= ' C:\DataGuard\Pstandby\DEMO\Archive '
db_file_name_convert= (' D:\oracle\oradata\DEMO ', ' C:\DataGuard\Pstandby\DEMO\ ')
log_file_name_convert= (' D:\oracle\oradata\DEMO ', ' C:\DataGuard\Pstandby\DEMO\ ')
log_archive_dest_1= (' location=c:\dataguard\pstandby\demo\archive ')

Almost all of the things that are prone to errors are concentrated in the process. The document must be treated carefully. The place marked black is modified.

2.6 Creating a Windows service

winnt> Oradim-new-sid Pstandby-startmode Manual

2.7 Create a Server Parameter File for the Standby Database

You can refer to the following actions:
C:\>set Oracle_sid=pstandby
C:\>sqlplus/nolog
Sql> Connect/as SYSDBA
Sql> CREATE SPFILE from pfile= ' C:\DataGuard\Pstandby\DEMO\initPstandby.ora ';

2.8 Starting the physical standby database

C:\>set Oracle_sid=pstandby
C:\>sqlplus/nolog
Sql> Connect/as SYSDBA
Sql> STARTUP Nomount;
sql> ALTER database MOUNT STANDBY database;

2.9 On the standby database, initialize the log Apply service:

sql> ALTER database RECOVER MANAGED STANDBY database DISCONNECT from session;


2.10 Activation to the physical standby database archive

sql> ALTER SYSTEM SET log_archive_dest_2= ' Service=pstandby ' Scope=both;
sql> ALTER SYSTEM SET log_archive_dest_state_2=enable scope=both;

2.11 Start Remote archiving

sql> ALTER SYSTEM ARCHIVE LOG current;


3. Installation of the verification:

3.1 in database, query V$archived_log
(You can actually see if log is created directly to the relevant directory):

Sql> SELECT sequence#, First_time, Next_time
2 from V$archived_log-sequence#;

sequence# First_time Next_time
---------- ---------- ----------
38 23-7?  -02 23-7? -02
39 23-7?  -02 23-7? -02
40 23-7?  -02 23-7? -02
41 23-7?  -02 23-7? -02
42 23-7?  -02 23-7? -02

3.2 On the primary database, archive the current log

sql> ALTER SYSTEM ARCHIVE LOG current;

3.3 Verify that you receive:

Sql> SELECT sequence#, First_time, Next_time
2> from V$archived_log order by sequence#;

sequence# First_time Next_time
---------- ---------- ----------
38 23-7?  -02 23-7? -02
39 23-7?  -02 23-7? -02
40 23-7?  -02 23-7? -02
41 23-7?  -02 23-7? -02
42 23-7?  -02 23-7? -02
43 23-7?  -02 23-7? -02


3.4 Verify that the new archive redo log has been applied:

Sql> Select sequence#,applied from V$archived_log
2 order by sequence#;

sequence# APP
---------- ---
YES
YES
YES
YES
YES
YES

OK. Shows that we are still successful. For the time being.


Reference documentation

Oracle Data Guard Concepts and administration
Release 2 (9.2)
Part number a96653-02

Additional content:

Pfile content of the primary database:

*.aq_tm_processes=1
*.background_dump_dest= ' D:\oracle\admin\DEMO\bdump '
*.compatible= ' 9.2.0.0.0 '
*.control_files= ' D:\oracle\oradata\DEMO\CONTROL01. CTL ',
' D:\oracle\oradata\DEMO\CONTROL02. CTL ', ' D:\oracle\oradata\DEMO\CONTROL03. CTL '
*.core_dump_dest= ' D:\oracle\admin\DEMO\cdump '
*.db_block_size=8192
*.db_cache_size=19922944
*.db_domain= '
*.db_file_multiblock_read_count=32
*.db_name= ' DEMO '
*.dispatchers= ' (protocol=tcp) (SERVICE=DEMOXDB) '
*.fast_start_mttr_target=300
*.hash_area_size=1048576
*.hash_join_enabled=true
*.instance_name= ' DEMO '
*.java_pool_size=20971520
*.job_queue_processes=10
*.large_pool_size=7340032
*.log_archive_dest_1= ' location=d:\oracle\oradata\demo\archive '
*.log_archive_dest_2= ' Service=pstandby '
*.log_archive_dest_state_2= ' ENABLE '
*.log_archive_start=true
*.open_cursors=300
*.optimizer_mode= ' First_rows '
*.pga_aggregate_target=17825792
*.processes=150
*.query_rewrite_enabled= ' TRUE '
*.remote_login_passwordfile= ' EXCLUSIVE '
*.shared_pool_size=33554432
*.sort_area_size=1048576
*.star_transformation_enabled= ' TRUE '
*.timed_statistics=true
*.undo_management= ' AUTO '
*.undo_retention=10800
*.undo_tablespace= ' UNDOTBS1 '
*.user_dump_dest= ' D:\oracle\admin\DEMO\udump '

Possible errors in the creation process: to Be continued