Oracle DG Linux Platform logical standby creation instance

Oracle, platform, Linux, database, Archive,sql

Operating system: Linux Redhat 4.7

oracle:10.2.0.1

Main Library: ORCL_PD

Prepare library: LGDG

A. Logical standby creation Process

1. Create Physical standby

Specific reference:

Oracle Data Guard Linux Platform Physical Standby Build example

Simply make the following tips:

(1) initialization parameter configuration

The modification of initialization parameters is not just created on the standby database to be created, and the initialization parameters of the current primary database, or even the other standby databases in the same data guard configuration, may need to be modified.

For primary databases, you need to add at least one log_archive_dest_n parameter to send redo data to the new standby, while other parameters related to the standby environment will involve adjustments, such as Log_archive_ Config and fal_* parameters.

And for the existing standby database (if any), mainly based on role conversion considerations, it is necessary to set some parameters in advance, set the parameters of the current function will not have any impact or promotion, if the determination does not perform role conversion, then the same data Other standby database initialization parameters in the guard configuration can also be adjusted without any adjustments.

(2) Monitoring and Netservice configuration

It is recommended that you use the Net Manager tool to configure it so that errors do not occur easily.

(3) Create key file

You must ensure that the SYS user of all databases has the same password in the same data guard environment, it is recommended that you copy the key file from another server to the local, and then modify the file name in the format of the key file name.

Note The Windows platform and Linux/unix platform, the name of the key file name format is not the same, the Windows platform under the key file name format is Pwd[sid].ora, and Linux/unix platform under the format of the key file name Orapw[sid], Note the capitalization of the file name.

2, primary database generation data dictionary

Perform the following procedure to generate Logminer dictionary information:

Sql> EXECUTE Dbms_logstdby. Build;

Pl/sql procedure successfully completed.

Note: Before primary generates a data dictionary, be sure to ensure that the physical standby database being converted has stopped redo applications. If the Redo application is enabled, execute the following statement to stop the redo application:

Sql>alter database RECOVER MANAGED STANDBY database CANCEL;

Database altered.

The application state of the log file is recorded in the control file, and normally a log file will only be applied once, if the data dictionary information generated by primary is applied by the physical standby database, When the physical standby is converted into a logical standby database (not applying the data dictionary does not mean that you cannot perform transformations), you will not be able to apply these files, and naturally there is no metadata for the primary database object, which may cause the modification of this part of the object not to be properly applied by logical standby.

The fundamental purpose of our operation is to allow the logic standby to apply to this part of the data dictionary information, as long as it is possible to implement this, whether to suspend redo application or pause redo application is irrelevant. This also we understand another question, if primary generates Logminer dictionary information, the physical standby database to be converted does not suspend redo application how to do? OK, immediately suspend redo application, and then primary the database to regenerate Logminer dictionary information is.

3. Convert physical standby to logical standby

The following statement is executed to convert the physical standby to the logical standby:

Sql> Show PARAMETER db_name

NAME TYPE VALUE

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

Db_name string ORCL

Sql> ALTER DATABASE RECOVER to LOGICAL STANDBY LGDG;

Database altered.

After you finish executing the statement, close the database and restart to mount state:

Sql> SHUTDOWN IMMEDIATE

Ora-01507:database not mounted

ORACLE instance shut down.

Sql> STARTUP MOUNT;

ORACLE instance started.

Total System Global area 167772160 bytes

Fixed Size 1218316 bytes

Variable Size 79694068 bytes

Database buffers 83886080 bytes

Redo buffers 2973696 bytes

Database mounted.

Why should I reboot? Because the above operation involves logical standby database renaming, including dbid, incarnation, etc. have been reinitialized.

View db_name parameters and database roles again:

Sql> show PARAMETER db_name;

NAME TYPE VALUE

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

Db_name string LGDG

Sql> SELECT database_role from V$database;

Database_role

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

LOGICAL STANDBY

Now the db_name and database roles have been modified.

4, adjust the logical standby database initialization parameters

Set the Redo log file path, separate the archived files sent from the locally generated archive and the primary database, and store them in different directories, note that the archive path does not conflict, and modify the following parameters:

sql> ALTER SYSTEM SET log_archive_dest_1= ' location=/u01/archive valid_for= (online_logfiles, All_roles) DB_UNIQUE_    NAME=LGDG '; # LGDG is configured in Tnsnames.ora

System altered.

sql> ALTER SYSTEM SET log_archive_dest_3= ' location=/u01/std

Valid_for= (Standby_logfiles, standby_role) DB_UNIQUE_NAME=LGDG ';

System altered.

5, open the logic standby

Because the logical standby is not consistent with the primary database transaction, the RESETLOGS clause must be specified the first time it is opened, executing the following statement:

sql> ALTER DATABASE OPEN resetlogs;

Database altered.

Then execute the following SQL command to start applying the redo data:

sql> ALTER DATABASE START LOGICAL STANDBY APPLY;

Database altered.

If you want to enable live applications, it is recommended that you first create standby redologs, for example, to create several sets of standby redologs for that logical standby:

sql> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 '/u01/app/oracle/oradata/orcl/redo04.log ' SIZE 50m;

Database altered.

Re-execute the command that started the redo application and attach the Apply immediate clause to open the live application (since the current redo application is already started, we first stop the redo application):

sql> ALTER DATABASE STOP LOGICAL STANDBY APPLY;

Database altered.

sql> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

6, verify the environment

All configurations are complete, and then try to perform modifications on the primary database side to see if they can be applied separately at the logical standby and physical standby end.

First, execute the following statement on the primary database side and insert a new record into the TMP1 table and submit:

sql> INSERT INTO scott.dept values (1, ' Dave ', ' DMM ');

1 row created.

Sql> commit;

Commit complete.

sql> alter system switch logfile;

System altered.

Next look at the synchronization of the logical standby:

Sql> select * from Scott.dept;