To create a standby database under 9i

Create a standby database under 9I (zz)
Last edited by sail on

Recently, we tested data guard in Oracle 9i and found that 9i is more convenient for standby.
Sort it out first, as shown below:

Operating Environment: Windows 2000 Professional + serveice Pack 4
Database: Oracle 9201
Primary database Sid: pormals
Slave database Sid: pormals
Oracle installation adopts the OMF Structure

1. the operating system and the same pack of the master and slave hosts

2. Create a database on the Master/Slave database and select to install software only

3. On the master database, use dbca to create a database. In addition to the zhs16gbk character set, all use the default method.

4. manually create the same directory as the master database on the slave Database

5. Close the master database and back up data files to the slave database.
Are you sure you want to copy the past data files and redo logs?
Sqlplus/nolog
SQL> Conn/As sysdba;
SQL> select file_name from dba_data_files;
Are you sure you want to copy the data file?
SQL> select Member from V $ logfile;
Are you sure you want to copy the online date to the file
SQL> shutdown immediate;
Copy all the files selected above to the corresponding directory on the slave database.

6. Open the master database and change it to the archive mode (Oracle9i user operation)
Manually create an archive directory c:/Oracle/Arch
Sqlplus/nolog
SQL> Conn/As sysdba;
Connected.
SQL> startup Mount;
SQL> alter database archivelog;
SQL> archive log start;
SQL> archive log list;
SQL> alter database open;
SQL> alter system set log_archive_dest_1 = 'location = C:/Oracle/arch ';
SQL> alter system set log_archive_format = '% T _ % S. dbf' scope = spfile;
SQL> alter system set log_archive_start = true scope = spfile;
Restart the database to make the modification take effect.
SQL> shutdown immediate;
View archiving Mode
SQL> startup
SQL> archive log list;

7. Create a slave Database Control file on the master database
Sqlplus/nolog
SQL> Conn/As sysdba;
SQL> alter database create standby controlfile as 'C:/Oracle/control01.ctl ';
Copy the master database C:/Oracle/control01.ctl to the slave database C:/Oracle/oradata/poramals/directory

8. Configure the listener. ora and tnsnames. ora files of the master database.
You can use net manager to configure or directly edit the C:/Oracle/ora92/Network/admin/listener. ora and tnsname. ora files.
That is, the network service name from the master database to the slave database (standby ).

9. Configure the slave library listener. ora and tnsnames. ora files
You can use net manager to configure or directly edit the C:/Oracle/ora92/Network/admin/listener. ora and tnsname. ora files.
That is, the network service name that normally listens to and connects from the slave database to the master database (primary) and directly to the slave database (standby)

10. Start the listener of the Master/Slave database.
Run the following commands on the master and slave databases respectively.
LSNRCTL start
Check whether the listener of the Master/Slave database is started properly

11 check the configuration of the Master/Slave database tnsnames. ora
Master database tnsping standby
Slave database tnsping primary
Check whether the service names of the Master/Slave databases are correctly configured.

12. Create pfile on the master database because 9i uses spfile by default.
Sqlplus/nolog
SQL> Conn/As sysdba;
Connected.
SQL> Create pfile = 'C:/Oracle/ora92/database/initporamls. ora 'from spfile;

13. Copy the created C:/Oracle/ora92/database. ora/initpormals. ora to the corresponding directory of the slave database.

14. Modify the initpormals. ora file copied from the database.
Add major changes:
Modify control_files to make it conform to the actual path of your control file
Add:
Standby_archive_dest = 'location = C:/Oracle/starch'
Fal_server = 'Primary'
Fal_client = 'standby'
Standby_file_management = 'auto'
Standby_file_management is not mandatory, but it can be automatically processed by the slave database to add files to the next master database.
Meanwhile, the archive log path for the slave database is C:/Oracle/starch.

15. Copy the C:/Oracle/ora92/database/pwdpormals. ora of the master database to the corresponding directory of the slave database.
16. Create a slave database instance named dos> oradim-New-Sid pormals
17. Start slave Database
Sqlplus/nolog
SQL> startup nomount;
SQL> alter database Mount standby database;
Set slave database to standby mode
SQL> alter database recover managed standby database disconnect from session;

18. Set the archive to slave database on the master database
SQL> alter system set log_archive_dest_2 = 'service = standby mandatory reopen = 60 ';
SQL> select * from V $ log;
Switch current online log
SQL> alter system switch logfile;
SQL> select * from V $ log;
View the background log of the slave Database
C:/Oracle/admin/pormals/bdump/pormals_arler.
Check whether the archived log has been properly restored.
Now the data guard configuration is complete.

19. Test it.
On the master database
SQL> create user test identified by password;
SQL> grant connect, resource to test;
SQL> conn test/password @ primary;
SQL> Create Table Test (name varchar2 (20 ));
SQL> insert into test values ('Hi, data guard ');
SQL> commit;
SQL> Conn/As sysdba
SQL> alter system switch logfile;

View slave database logs
C:/Oracle/admin/poramls/bdump/pormals_alert.ora
Check whether the archived log has been properly restored.

Open the slave database in read-only mode to check whether the insert into test values ('Hi, data guard ') has taken effect.
On the slave Database
SQL> Conn/As sysdba;
SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;
SQL> conn test/Password
SQL> select * from test;
Check whether insert into test values ('Hi, data guard '); has taken effect.
If the configuration takes effect, data guard is fully configured.
Reset slave database in recovery mode
SQL> alter database recover managed standby database disconnect from session;
Note:
1. About the order of startup and Shutdown
When starting, first start the listener of the database, then start the slave database, then start the listener of the master database. Then, when the master database is closed, close the master database first, and then the slave database.
2. Some common views
V $ archive_dest_status records the standby status and the log that is restored.
V $ archive_gap records the courage required to restore the MRP process from the database to the slave database.
V $ archived_log records all logs archived to the slave database and records whether the log has been recovered.
V $ database records the system's protection status and whether it is in force logging status.
V $ managed_standby (physical standby databases only) records some processes of the current slave database and the process ID of the process, such as RFs and MRP.
V $ standby_log (physical standby databases only) records some situations of the current slave database's standby redo log.