Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "Analysis of Real-time query characteristics of Oracle 11g physical Active Data Guard". In the operation of actual cases, many people will encounter this dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

In the Data Guard physical standby database in the previous version of Oracle 11g, the database can be opened as read-only, but at this time, the process of data synchronization using logs by Media Recovery stops. If the physical standby database is in the process of recovery, the database cannot open the query, that is to say, the two states of log application and read-only open are mutually exclusive, and the Oracle 11g Active Data Guard function solves this contradiction. While using the log to recover data, the database can be opened in a read-only way, and users can query, report and other operations on the standby database, which is similar to the function of logical Data Guard standby database (query function). However, data synchronization is more efficient and requires less hardware resources. In this way, the hardware resources of the physical standby database can be used to a greater extent.

Take the creation of tablespaces, users, and tables as examples to experience the Oracle 11g physical Active Data Guard real-time query (Real-time query) features.

1. Adjust the standby library to "READ ONLY WITH APPLY" status

This reflects the true meaning of "Active" in the Oracle 11g physical Active Data Guard function.

1) View the current status of the slave database

Ora11gdg@secdb / home/oracle$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 16 11:54:52 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Sys@ora11gdg@ > select open_mode from v$database

OPEN_MODE

-

MOUNTED

At this time, the slave database is in MOUNT state.

2) cancel the automatic recovery of the reserve database

Sys@ora11gdg@ > alter database recover managed standby database cancel

Database altered.

3) adjust the OPEN repository to "READ ONLY" status

Sys@ora11gdg@ > alter database open

Database altered.

Sys@ora11gdg@ > select open_mode from v$database

OPEN_MODE

-

READ ONLY

4) further start the recovery of the slave database in the "READ ONLY" state

Sys@ora11gdg@ > alter database recover managed standby database using current logfile disconnect

Database altered.

The option "USING CURRENT LOGFILE" means that the recovery is completed as soon as the slave library receives the log.

Sys@ora11gdg@ > select open_mode from v$database

OPEN_MODE

-

READ ONLY WITH APPLY

The status "READ ONLY WITH APPLY" means that the slave database can accept the logs passed by the master database for recovery while the slave database is in the Read Only state, so that the standby database can view the changes of the master database immediately.

two。 Experience real-time query (Real-time query) features

1) create tablespaces, users, and tables on the main library and initialize data

(1) create a tablespace and view the result and status of tablespace creation

Sys@ora11g > create tablespace secooler_tbs datafile'/ u01 size size 10m

Tablespace created.

Sys@ora11g > select * from v$tablespace where name = 'SECOOLER_TBS'

TS# NAME INC BIG FLA ENC

-

8 SECOOLER_TBS YES NO YES

Sys@ora11g > select ts#,status,bytes,name from v$datafile where ts# = 8

TS# STATUS BYTES NAME

8 ONLINE 10485760 / u01/app/oracle/oradata/ora11g

/ secooler_tbs01.dbf

(2) create users and authorize them

Sys@ora11g > create user secooler identified by secooler default tablespace secooler_tbs

User created.

Sys@ora11g > grant dba to secooler

Grant succeeded.

(3) create a table and initialize the data

Sys@ora11g > conn secooler/secooler

Connected.

Secooler@ora11g > create table t (x varchar2 (8))

Table created.

Secooler@ora11g > insert into t values ('Secooler')

1 row created.

Secooler@ora11g > commit

Commit complete.

Secooler@ora11g > select * from t

X

-

Secooler

2) at this point, the alert log of the ora11g instance records the following information

Fri Mar 16 11:56:36 2012

Create tablespace secooler_tbs datafile'/ u01 size size 10m

Fri Mar 16 11:56:48 2012

Completed: create tablespace secooler_tbs datafile'/ u01 size size 10m

3) almost at the same time (1 second) the following information is recorded in the warning log of the ora11gdg instance of the standby database

Fri Mar 16 11:56:37 2012

Recovery created file / u01/app/oracle/oradata/ora11gdg/secooler_tbs01.dbf

Successfully added datafile 5 to media recovery

Datafile # 5:'/ u01 *

It can be seen that the standby library has accepted the change of the main library, and the change has been applied to the standby library.

4) verify whether the tablespace, users and tables created by the main database and initialize the data are successfully applied in the slave database

(1) View the database tablespace

Sys@ora11gdg > select * from v$tablespace where name = 'SECOOLER_TBS'

TS# NAME INC BIG FLA ENC

-

8 SECOOLER_TBS YES NO YES

Sys@ora11gdg > select ts#,status,bytes,name from v$datafile where ts# = 8

TS# STATUS BYTES NAME

8 RECOVER 10485760 / u01/app/oracle/oradata/ora11g

Dg/secooler_tbs01.dbf

Created successfully, but at this time the status of the repository data file is "RECOVER".

(2) check whether the database users and data exist.

Sys@ora11gdg > conn secooler/secooler

Connected.

Secooler@ora11g > set lines 80

Secooler@ora11g > desc t

Name Null? Type

X VARCHAR2 (8)

Secoolerdg@ora11g > select * from t

X

-

Secooler

The changes on the main library have been applied even on the standby library.

This is the embodiment of the real-time query (Real-time query) feature of Oracle 11g physical Active Data Guard.

This is the end of "Analysis of Real-time query characteristics of Oracle 11g physical Active Data Guard". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.