[DG experiment] Build physical standby -- copy files

Build physical standby -- Copy Data File

I. DG Environment Description

Database Version: 11.2.0.1.0

Operating System: RedHat Linux 5

Master Database: 192.168.88.3 Sharon

Slave Database: 192.168.88.2 Sharon

When using static IP addresses, make sure the IP addresses are fixed. Do not use DHCP to allocate them!

 

Ii. Steps

1. Start force Logging

SQL> alter database force logging;

Check:

SQL> select force_logging from V $ database;

Force_log

---------

Yes

 

2. Start the archive Mode

SQL> archive log list;

SQL> shutdown immediate

SQL> startup Mount

SQL> alter database archivelog;

SQL> alter system setlog_archive_dest_1 = 'location =/u01/ARCH/'scope = spfile;

SQL> archive log list;

Database Log mode archive Mode

Automatic Archival Enabled

Archive destination/u01/ARCH/

Oldest online log sequence 91

Next log sequence to archive 92

Current Log sequence 92

 

3. Check the password file

[Oracle @ Sharon ~] $ CD $ ORACLE_HOME/dbs

[Oracle @ Sharon DBS] $ LL

Total 44

-RW ---- 1 Oracle oinstall 1544 Apr 21 hc_sharon.dat

-RW-r ----- 1 Oracle oinstall12920 May 3 2001 initdw. ora

-RW-r ----- 1 Oracle oinstall 8385 sep 11 1998 init. ora

-RW ---- 1 Oracle oinstall 24 Apr 21 :43 lksharon

-RW-r ----- 1 oracleoinstall 1536 Apr 21 orapwsharon

-RW-r ----- 1 Oracle oinstall 3584 Apr 22 spfilesharon. ora

If it does not exist, create it manually and use the orapwd command. I will not explain how to use this command. If you are not clear about it, Google:

[Oracle @ DG1/] $ orapwd file =/u01/APP/Oracle/product/11.2.0/db_1/dbs/orapwdave Password = admin entries = 30

 

4. Create listener in the Master/Slave database and start

Use the netca command to call the graphical interface for creation!

 

5. Add Oracle Net service (tnsnames. ora) to the Master/Slave database and configure static listeners.

Use the netmgr command to call the graphical interface for creation.

Configure static listening:

Note the case sensitivity. At one time, the Instance name was in upper case, and the SID in the dynamic listener of the configuration was written in lower case, so that the RMAN duplicate could not be connected!

[Description]

* Steps 4 and 5 can be done with netmgr at one time, which is convenient!

* You can directly modify the listener. ora, tnsnames. ora is used to configure the listener and oraclenet service. However, it is not recommended to do so and it is prone to errors. If you have one more space and one less space, the Service may become unavailable. We recommend that you use a tool to configure the service!

* The default port number for configuring static listening is 1521. If other port numbers are configured in conflict, you must manually register the following.

[Oracle @ Sharon admin] $ netmgr

[Oracle @ Sharon admin] $ cat tnsnames. ora

# Tnsnames. ora networkconfiguration file:/u01/APP/Oracle/product/11.2.0/db_1/Network/admin/tnsnames. ora

# Generatedby Oracle configuration tools.

-- Note the prompt here, which is generated using a tool.

 

Sharon_pd =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 192.168.88.3) (Port = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = Sharon)

)

)

 

Sharon_st =

(Description =

(Address_list =

(Address = (Protocol = TCP) (host = 192.168.88.2) (Port = 1521 ))

)

(CONNECT_DATA =

(SERVICE_NAME = Sharon)

)

)

 

[Oracle @ Sharon admin] $ cat listener. ora

# Listener. ora networkconfiguration file:/u01/APP/Oracle/product/11.2.0/dbhome_1/Network/admin/listener. ora

# Generatedby Oracle configuration tools.

 

Sid_list_listener =

(Sid_list =

(Sid_desc =

(Global_dbname = Sharon)

(ORACLE_HOME =/u01/APP/Oracle/product/11.2.0/DBS)

(Sid_name = Sharon)

)

)

 

Listener =

(Description_list =

(Description =

(Address = (Protocol = TCP) (host = 192.168.88.3) (Port =
1521 ))

)

)

 

Configure and restart the listener:

[Oracle @ Sharon admin] $ LSNRCTL stop

[Oracle @ Sharon admin] $ LSNRCTL start

[Oracle @ Sharon admin] $ LSNRCTL status

LSNRCTL for Linux: version11.2.0.1.0-production on 24-apr-2013 10:40:28

 

Copyright (c) 1991,200 9, Oracle. All rights reserved.

 

Connecting to (description = (address = (Protocol = TCP) (host = 192.168.88.2) (Port = 1521 )))

Status of the listener

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

Alias listener

Version tnslsnr for Linux: version11.2.0.1.0-Production

Start date 24-apr-2013 10:40:03

Uptime 0 days 0 HR. 0 min. 26 Sec

Trace Level off

Security on: Local OS Authentication

SNMP off

Listener parameter file/u01/APP/Oracle/product/11.2.0/db_1/Network/admin/listener. ora

Listener log file/u01/APP/Oracle/diag/tnslsnr/Sharon/listener/alert/log. xml

Listening endpoints summary...

(Description = (address = (Protocol = TCP) (host = 192.168.88.2) (Port = 1521 )))

Services summary...

Service "Sharon" has 1 instance (s ).

Instance "Sharon", status unknown, has 1 handler (s) for this service...

The command completedsuccessfully

* When the status is unknown, it indicates a static listener.

 

Check whether all services in tnsping are accessible

[Oracle @ Sharon admin] $ tnsping sharon_pd

TNS Ping utility for Linux: Version 11.2.0.1.0-production on 26-apr-2013 09:52:51

 

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

 

Used parameter files:

 

 

Used tnsnames adapter to resolve the alias

Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 192.168.88.3) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = sharon_pd )))

OK (0 msec)

[Oracle @ Sharon admin] $ tnsping sharon_st

TNS Ping utility for Linux: Version 11.2.0.1.0-production on 26-apr-2013 09:52:51

 

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

 

Used parameter files:

 

 

Used tnsnames adapter to resolve the alias

Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host = 192.168.88.2) (Port = 1521) (CONNECT_DATA = (SERVICE_NAME = sharon_st )))

OK (0 msec)

 

6. Create a standbycontrol file in the master database

Here, you must start the database to the Mount state to create it:

SQL> alter database create standby controlfile as '/u01/control01.ctl ';

* Database instances constantly update control files, because if we copy data files directly to the standby database without shutting down the instance, this operation is correct, however, when we open the database, the following error will be prompted:

ORA-10458: standbydatabase requires recovery

ORA-01194: File 1 needsmore recovery to be consistent

ORA-01110: Data File 1: '/u01/APP/Oracle/oradata/Dave/system01.dbf'

The copy method is to directly copy the data files and online redo files to the slave database.

The master database is always mounted.
Status!

 

7. Create a pfile file in the master database and modify the pfile content.

SQL> Create pfile from spfile;

Add the following content to pfile:
# Add for primary DG
*. Db_unique_name = 'sharon _ PD'
*. Log_archive_config = 'dg _ Config = (sharon_pd, sharon_st )'
*. Log_archive_dest_1 = 'location =/u01/archlog valid_for = (all_logfiles, all_roles) db_unique_name = sharon_pd'
*. Log_archive_dest_2 = 'service = sharon_st reopen = 120 lgwr async valid_for = (online_logfiles, primary_role) db_unique_name = sharon_st'
*. Log_archive_dest_state_1 = Enable
*. Log_archive_dest_state_2 = Enable
*. Standby_file_management = 'auto'
*. Fal_server = 'sharon _ st'

*. Fal_client = 'sharon _ PD '-10g to write! 11g is not required!

Note:
(1) In Oracle 11g, The fal_client parameter is discarded, that is, no configuration is required.
(2) The Oracle Net service name in the log_archive_dest_n parameter has the same first place. Do not make a mistake.
(3) If the paths of the master and slave databases are different, add the following two parameters to the parameter file of the master database:
*. Log_file_name_convert = '/u02/oradata/orcl/', '/U03/oradata/orcl /'
*. Db_file_name_convert = '/u02/oradata/orcl/', '/U03/oradata/orcl /'

-- Use the modified pfile to regenerate the spfile:

Shutdown and create again

Start to mount with pfile

SQL> startup Mount pfile = '$ ORACLE_HOME/dbs/initsharon. ora ';
SQL> Create spfile from pfile;

 

8. Create related directories in the standby Database

Because no instance is created for the slave database, there is no related directory. We need to refer to the location of the master database to create.
To avoid omission, you can create a pfile that looks at the master database!

[Oracle @ Sharon Oracle] $ pwd

/U01/APP/Oracle

[Oracle @ Sharon Oracle] $ ls

Admin checkpoints diag fast_recovery_area oradata Product

-- FRA:

[Oracle @ Sharon] $ pwd

/U01/APP/Oracle/fast_recovery_area/Sharon

-- Datafile:

[Oracle @ Sharon] $ pwd

/U01/APP/Oracle/oradata/Sharon

[Oracle @ Sharon] $ pwd

/U01/APP/Oracle/admin/Sharon

[Oracle @ Sharon] $ mkdir-P/u01/APP/Oracle/oradata/Sharon

[Oracle @ Sharon] $ mkdir-P/u01/APP/Oracle/fast_recovery_area/Sharon

[Oracle @ Sharon] $ mkdir-P/u01/APP/Oracle/admin/Sharon/adump

[Oracle @ Sharon] $ mkdir-P/u01/APP/Oracle/admin/Sharon/dpdump

 

9. Copy the parameter file of the master database to the slave database and modify it.

[Oracle @ Sharon DBS] $ SCP initsharon. ora 192.168.88.2:/u01/APP/Oracle/product/11.2.0/db_1/dbs

The parameter file has been modified in the pfile of the master database. Here we only need the two parameters:

# Add for standby DG
*. Db_unique_name = 'sharon _ st'
*. Log_archive_config = 'dg _ Config = (sharon_pd, sharon_st )'
*. Log_archive_dest_1 = 'location =/u01/archivelog valid_for = (all_logfiles, all_roles) db_unique_name = sharon_st'
*. Log_archive_dest_2 = 'service = sharon_pd reopen = 120 lgwr async valid_for = (online_logfiles, primary_role) db_unique_name = sharon_pd'
*. Log_archive_dest_state_1 = Enable
*. Log_archive_dest_state_2 = Enable
*. Standby_file_management = 'auto'
*. Fal_server = 'sharon _ PD'
*. Fal_client = 'sharon _ st' -- 10 Gb to write! No need to write 11G!

 

10. Copy the password files, control files, data files, and log files of the master database to the slave database.

-- Password file:
[Oracle @ Sharon DBS] $ SCP orapwsharon 192.168.88.2:/u01/APP/Oracle/product/11.2.0/db_1/dbs

--Control file:

--Get from slave database:Note that the name of the control file is modified:

[Oracle @ Sharon] $ SCP 192.168.88.3:/u01/control01.ctl/u01/APP/Oracle/oradata/Sharon/control01.ctl
[Oracle @ Sharon] $ SCP 192.168.88.3:/u01/control01.ctl/u01/APP/Oracle/fast_recovery_area/Sharon/control02.ctl

Note:The 11g control file is in different directories, and control02.ctl is in FRA!

-- Data File
[Oracle @ Sharon] $ SCP 192.168.88.3:/U01/APP/Oracle/oradata/Sharon/*. DBF/u01/APP/Oracle/oradata/Sharon/

 

11. Start the slave database and apply applyservice

[Oracle @ Sharon] $ sqlplus/As sysdba

-- 11g startup

SQL> startup

SQL> select open_modefrom v $ database;

Open_mode

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

Readonly

 

-- 10g:
SQL> startup nomount;
SQL> alter database Mount standby database;
SQL> alter database recover managed standby database disconnect from session;

 

12. Start the master database to the open state and check whether the DG configuration is correct.

What we are creating the standby control file is to start the master database to the Mount state.
SQL> alter database open;
Database altered.
SQL> select open_mode from V $ database;
Open_mode
--------------------
Read Write
-- Query the status of DG:
SQL> Col error for A10
SQL> Col dest_name for A20
SQL> SElect dest_name, status, process, error, transmit_mode from V $ archive_dest where target = 'standby ';

Dest_name status process error transmit_mod
-------------------------------------------------------------
Log_archive_dest_2 valid lgwr asynchronous
If there is a problem with the DG configuration, the invalid will be displayed here, and the error will prompt the specific error cause. You can also check the Alert Log to determine the cause.

 

13. Add online redo log and standby redo log to the Master/Slave Database

-- Add standby redo log to the master database: Size and online redo
Same as online redo Group
More than one group.

SQL> alter database add standby logfile Group 4 ('/u01/APP/Oracle/Sharon/redo04.log') size 50 m;

SQL> alter database add standby logfile group 5 ('/u01/APP/Oracle/Sharon/redo05.log') size 50 m;

SQL> alter database add standby logfile group 6 ('/u01/APP/Oracle/Sharon/redo06.log') size 50 m;

SQL> alter database add standby logfile group 7 ('/u01/APP/Oracle/Sharon/redo07.log') size 50 m;

-- Verification:

SQL> Col member for A50

SQL> select group #, type, member from V $ logfile;

-- Slave database execution

SQL> Col member for A50

SQL> select group #, type, member from V $ logfile;

SQL> alter database add standby logfile Group 4 ('/u01/APP/Oracle/oradata/Sharon/redo04.log') size 50 m;

SQL> alter database add standby logfile group 5 ('/u01/APP/Oracle/oradata/Sharon/redo05.log') size 50 m;

SQL> alter database add standby logfile group 6 ('/u01/APP/Oracle/oradata/Sharon/redo06.log') size 50 m;

SQL> alter database add standby logfile group 7 ('/u01/APP/Oracle/oradata/Sharon/redo07.log') size 50 m;

-- Verification:

SQL> Col member for A50

SQL> select group #, type, member from V $ logfile;

 

-- The physical standby database of Oracle 11g is started to read-only.

SQL> alter database recover managed standbydatabase disconnect from session;

Database altered.

11g

SQL> select open_modefrom v $ database;

Open_mode

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

Read Only with apply

 

Test:

-- Master database:

SQL> SElect max (sequence #) from V $ archived_log;

Max (sequence #)

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

23

SQL> alter system switch logfile;
System altered.
SQL> SElect max (sequence #) from V $ archived_log;
Max (sequence #)
--------------
24

-- Query the status of DG:

SQL> select max (sequence #) from V $ archived_log;
Max (sequence #)
--------------
24

SQL> Col error for A10

SQL> Col dest_name for A20

SQL> select dest_name, status, process, error, transmit_modefrom v $ archive_dest where target = 'standby ';

Dest_name status process error transmit_mod target

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

Log_archive_dest_2 valid lgwr asynchronous
Standby

 

Note:

When the listener is not on port 1521, you need to set the local_listener parameter.

Add the listener information to the tnsnames. ora file. When registering a listener dynamically, pmon reads information from tnsnames. ora.

Listener =

(Description =

(Address = (Protocol = TCP) (host = IP) (Port = 1522 ))

)

Run the following command with SYS:

SQL> alter system setlocal_listener = listener;

SQL> alter system register;

Or:

SQL> alter system setlocal_listener = '(address = (Protocol = TCP) (host = IP) (Port = 1522 ))';

SQL> alter system register;