Oracle10g physical standby database notes

 

Test Environment
Primary Server:
Windows2000 Server + Oracle 10.0.1.0.2
Oracle_sid: dgtest
ORACLE_HOME: D: \ oracle \ product \ 10.1.0 \ db_1
Standby server:
Windows XP Pro + Oracle 10.0.1.0.2
Oracle_sid: dgtest
ORACLE_HOME: D: \ oracle \ product \ 10.1.0 \ db_1

Procedure
First, install oracle on primary and standby, and create a database with the same SID as dgtest. All the settings are the same. Then stop oracle on the shutdown platform and copy all data files, control files, redo files, and password files on the primary to the corresponding location of the standby machine. (You can also use hot standby and RMAN to create the initial standby database.) When I feel that the configuration is complete, it is better to copy the file when using the new parameter file to start standby, we have two identical Oracle servers.

1. Set force logging on primary database
SQL> alter database force logging;

2. Modify the initialization parameters of primary Databse.
Create pfile SQL> Create pfile = 'd: \ oracle \ product \ pfdgtest. ora 'from spfile;

Modify parameters:
# Db_unique_name = 'primary'
Control_files = 'd: \ oracle \ product \ oradata \ control01.ctl ', 'd: \ oracle \ product \ oradata \ control02.ctl', 'd: \ oracle \ product \ oradata \ control03.ctl'
Log_archive_dest_1 = 'location = D: \ oracle \ product \ arch'
Log_archive_dest_2 = 'service = standby'
Log_archive_dest_state_1 = Enable
Log_archive_dest_state_2 = Enable
Remote_login_passwordfile = exclusive
Log_archive_format = 'arc % S % T % R. arc'
Fal_server = standby
Fal_client = primary
Standby_file_management = auto
Lock_name_space --- this parameter must be set when the master database and backup data are on the same machine and set as the SID of the backup database.

3. Set primary database to archivelog Mode
SQL> shutdown immediate;
SQL> startup Mount;
SQL> alter database archivelog;
SQL> alter database open:

4. Create a control file for standby database on primary database
SQL> shutdown immedaite;
SQL> startup Mount;
SQL> alter database create standby controlfile as 'd: \ oracle \ product \ oradata \ controlstandby. CTL'
SQL> alter database open;
Copy the controlfile to the corresponding location of the standby server.

5. Modify the initialization parameters of standby Databse
# Db_unique_name = 'standby'
Control_files = 'd: \ oracle \ product \ oradata \ controlstandby. CTL'
Log_archive_dest_1 = 'location = D: \ oracle \ product \ arch'
Log_archive_dest_2 = 'service = primary'
Log_archive_dest_state_1 = Enable
Log_archive_dest_state_2 = Enable
# Standby_archive_dest = 'd: \ oracle \ product \ arch '-- this parameter is not required after Oracle 10gr2, and log_archive_d is used
Remote_login_passwordfile = exclusive
Fal_server = primary
Fal_client = standby
Standby_file_management = auto
Db_file_name_convert ---- used when the data file paths of primary and standby are inconsistent
Log_file_name_convert ---- used when the data file paths of primary and standby are inconsistent
Lock_name_space ---- SID of the backup database when the master database and backup data are on the same machine
For more information about the parameters, see Oracle online documentation.

6. Set tnsnames. ora for primary database and standby Database
Primary =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = primary_server) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dgtest)
)
)

Standby =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = standby_server) (Port = 1521 ))
)
(CONNECT_DATA =
(SERVICE_NAME = dgtest)
)
)

-- Use tnsping to test listener.

-- Copy database files

7. Start standby Database
SQL> startup nomount pfile = 'd: \ oracle \ product \ initdgtest. ora'
SQL> alter database Mount standby database;

8. Start primary database
SQL> startup pfile = 'd: \ oracle \ product \ initdgtest. ora'

9. Start standby database to recover Manage Mode
SQL> alter database recover managed standby database disconnect from session;

Switchover

On Primary

1. SQL> select switchover_status from V $ Database
The value of switchover_status is to standby. switchover can be used directly. If it is sessions active,
Add the with Session shutdown command after the switchover command.

2. SQL> alter database commit to switchover to physical standby;

3. SQL> shutdowm immediate;

4. SQL> startup Mount;

On standby

1. SQL> select switchover_status from V $ Database

The value of switchover_status is to primary. switchover can be used directly. If it is sessions active,

Add the with Session shutdown command after the switchover command.

2. SQL> alter database commit to switchover to primary;

3. SQL> shutdown immediate;

4. SQL> startup;

To achieve switchover smoothly, it is best to set some initialization parameters of primary and standby on each server at the same time, although some parameters only work on one of primary or standby

Failover

On standby

1. SQL> alter database recover managed standby database finish;
Or SQL> alter database recover managed standby database finish Skip standby logfile;

2. SQL> alter database commit to switchover to primary;

3. SQL> shutdown immediate;

4. SQL> startup;

Related View

V $ archive_dest
V $ archive_dest_status
V $ log_history
V $ archvied_log
V $ managed_standby
V $ archive_gap

FAQs

1. Logs cannot be transferred
SQL> select dest_name, status, error from V $ archive_dest;
Check whether the status of the corresponding archive path is valid; otherwise, process the path based on error information.

2. You cannot use alter database to rename data file.
In STANDBY, when standby_file_management is set to Auto, the following operations are not allowed
Alter database rename
Alter database add/drop logfile
Alter database add/drop standby logfile Member
Alter database create datafile

Failed
SQL> alter database commit to switchover to physical standby
ORA-01093: Alter database close only permitted with no session connected
View the Active session that caused the error
SQL> select Sid, process, program from V $ session where type = 'USER' and
Sid <> (select distinct Sid from V $ mystat );
Disconnect the session, or use the following command for switchover
SQL> LTER database commit to switchover to physical standby with Session shutdown;

--------------------------------------------

My test environment:
Windows XP SP2 + Oracle 10.2.0
Oracle_sid: dgtest
ORACLE_HOME: D: \ oracle \ product \ 10.2.0 \ db_1

I have read the chapter "Oracle high availability environment", and I have experienced many O (strong _ strong) O... Mainly some parameters!

Validate whether received:
SQL> select sequence #, first_time, next_time
From v $ archived_log order by sequence #

Validate whethere new archive redo log have been applied.
SQL> select sequence #, dest_id, archived, applied, deleted,
Status from V $ archived_log order by sequence #

Query database status:
SQL> select name, open_mode, protection_mode, database_role from V $ database;

------------------------
Network Data Collection

Conclusion: when the slave database is up:
1. nomount: The RFS service does not start, so the master database file will not be uploaded to the slave database; the MRP process does not start, so no recover will be performed, and archive will not

Applied.
2. Mount: When the RFS service is started, archive of the master database can be transferred to the slave database (several times in the switchlogfile of the master database, you will find that the archive has been transferred

); The MRP process is not started and archive is not applied.
3. Recover managed: RFS has been started in the previous step. archive can be transferred to the slave database. The MRP process is up and archive logs are applied!

Http://www.oracleblog.cn/study-note/different-stage-on-dataguard-startup/

Master/Slave switchover:
1. Primary --> Phsical Standby:
First confirm whether the conversion is possible:
Select switchover_status from V $ database;
Conversion:
Alter database commit to switchover to physical standby;
Shutdown immediate;
Startup nomount;
Alter database Mount standby database;
2. Phsical standby --> Primary:
First confirm whether the conversion is possible:
Select switchover_status from V $ database;
Conversion:
Alter database commit to switchover to primary;
Shutdown;
Startup;
3. New Primary:
Alter database recover managed standby database disconnect from session;
Alter system archive log current;