Categotry Archives: dataguard

kcbgtcr_13 on active dataguard

0

Posted on by

今天在acitve dataguard上报出一个错误,在监控表空间时报出的,

经查为bug Bug 12848798 – OERI:kcbgtcr_13 on active dataguard [ID 12848798.8]

数据库版本为:11.2.0.3.5 

错误内容:

	ORA-00600: internal error code, arguments: [kcbgtcr_13], [], [], [], [], [], [], [], [], [], [], []

在查询V$或者X$会报出的。

下载补丁:12848798可解决

Affects:

Product (Component)

Oracle Server (Rdbms)

Range of versions believed to be affected

Versions >= 10.2 but BELOW 12.1

Versions confirmed as being affected

Platforms affected

Generic (all / most platforms affected)

Fixed:

The fix for 12848798 is first included in

 

Installing and Using Standby Statspack in 11g

0

Posted on by

11g active dataguard 生成的awr信息是主库的awr,而不是dataguard的awr信息,不能生成备库的awr,但是可以生产statspack。

1. Standby Statspack Usage

1.1 Statspack Installation 

The perfstat schema and statspack related objects must be present on the primary and standby prior to installing standby statspack. 

If the perfstat user does not exist in the primary site then you will need to create the perfstat schema using the following:

sqlplus / as sysdba
SQL> @?/rdbms/admin/spcreate.sql

 

The standby statspack installation script (sbcreate.sql) creates the standby statspack schema to hold the standby snapshots. The script asks for:

1.  A password for stdbyperf user

2.  Default tablespace

3.  Temporary tablespace

The script creates the ‘stdbyperf’ user and assigns it the selected default and temporary table space. After the user has been created it calls sbaddins.sql to add the first standby instance to the configuration.
Example:

SQL> @?/rdbms/admin/sbcreate

Note: Due to Bug 11899453, ensure global_names is set to 'FALSE' on the Standby Database while you run this Script. See Note 11899453.8 for some more Details.

 

1.2 Add an Instance to Statspack Configuration

Log in to the primary as the 'stdbyperf' user and run the script sbaddins.sql to add a standby instance to the configuration. The script asks for:

1.  The TNS alias of the standby database instance

2.  The password of the perfstat user on the standby site

The script then creates a private database link to the perfstat schema on the standby site and a separate gathering package for each standby instance. Example:

SQL> connect stdbyperf/your_password 
SQL> @?/rdbms/admin/sbaddins
Input inst2_alias as the tns alias.

 

1.3 Collect Performance Data from a Standby Instance

The script sbaddins.sql creates a separate PL SQL package for each standby instance. Run the snap procedure of those packages to collect the performance data. The statspack_<instance_name>.snap procedure accesses the data dictionary and stats$ views on the standby database via database link connected to the original perfstat user und stores the data to stats$ tables on the primary instance. For example, while the standby is opened read only, login to the primary database and create the snap:

SQL> connect stdbyperf/your_password
SQL> exec statspack_<db_unique_name>_<instance_name>.snap

 

1.4 Generate Standby Statistics Report

The script sbreport.sql generates the standby statistics report. The script asks for: database id, instance number, high and low snapshots id to create the report. Example:

SQL>@?/rdbms/admin/sbreport

 

1.5 Purge a Set of Snapshots

The script sbpurge.sql purges a set of snapshots. The script asks for database id, instance number, low and high snapshots ids. The script purges all snapshots between the low and high snapshot ids for the given instance. Example:

SQL>@?/rdbms/admin/sbpurge

 

1.6 Delete an Instance from the Configuration

The script sbdelins.sql deletes an instance from the configuration, and deletes the associated PL SQL package. The scripts asks for instance name. The snapshots are not automatically purged when the instance is deleted. After deleting the instance, you are not able to generate reports for that instance. Example:

SQL> @?/rdbms/admin/sbdelins

 

1.7 Drop Statspack Schema

The script sbdrop.sql drops the stdbyperf user and tables. The script must be run when connected to SYS (or internal). Example:

SQL> connect / as sysdba
SQL> @?/rdbms/admin/sbdrop

 

 

2. New Statistics Collected

Two new sections are added to standby statspack report: (1) Recovery Progress Stats, and (2) Managed Standby Stats. An example is provided below.

Recovery Progress Stats DB/Inst: MADISON/madison1 End Snap: 2
-> End Snapshot Time: 20-Jun-07 13:59:29
-> ordered by Item, Recovery Start Time desc
Recovery Start Time Item Sofar Units Redo Timestamp
——————- —————– ————– ——- ——————
08-Jun-07 11:58:15 Active Apply Rate 8,420 KB/sec
08-Jun-07 11:58:15 Active Time 4,291 Seconds
08-Jun-07 11:58:15 Apply Time per Lo 267 Seconds
08-Jun-07 11:58:15 Average Apply Rat 3 KB/sec
08-Jun-07 11:57:15 Average Apply Rat 955 KB/sec
08-Jun-07 11:58:15 Checkpoint Time p 0 Seconds
08-Jun-07 11:58:15 Elapsed Time 1,044,073 Seconds
08-Jun-07 11:57:15 Elapsed Time 6 Seconds
08-Jun-07 11:58:15 Last Applied Redo 15,273,580 SCN+Tim 20-Jun-07 13:59:29
08-Jun-07 11:57:15 Last Applied Redo 13,945,701 SCN+Tim 08-Jun-07 11:56:16
08-Jun-07 11:58:15 Log Files 16 Files
08-Jun-07 11:57:15 Log Files 33 Files
08-Jun-07 11:58:15 Redo Applied 3,181 Megabyt
08-Jun-07 11:57:15 Redo Applied 6 Megabyt
————————————————————-

Managed Standby Stats DB/Inst: MADISON/madison1 End Snap: 2
-> End Snapshot Time: 20-Jun-07 13:59:29
-> ordered by Process
Process pid Status Resetlog Id Thread Seq Block Num
———– ———- ———— ———– —— ——- ———–
Client Proc Client pid Blocks Delay(mins)
———– ———- ————– ————–
ARCH 29360 CLOSING 624693241 2 59 18433
ARCH 29360 340 0
ARCH 29358 CLOSING 624693241 1 57 94209
ARCH 29358 1,596 0
ARCH 29356 CLOSING 624693241 1 56 903169
ARCH 29356 1,835 0
ARCH 29354 CLOSING 624693241 2 61 919553
ARCH 29354 770 0
MRP0 30839 APPLYING_LOG 624693241 2 62 57
N/A N/A 2,097,152 0
RFS 28886 IDLE 0 0 0 0
N/A 16388 0 0
RFS 28875 IDLE 624693241 2 62 2164
LGWR 16137 432 0
RFS 30192 IDLE 624693241 1 58 51
LGWR 2092 1 0
RFS 28892 IDLE 0 0 0 0
UNKNOWN 16384 0 0
RFS 30326 IDLE 0 0 0 0
N/A 2268 0 0
————————————————————-

 

3. List of New Scripts and Short Descriptions

All those Scripts below are located in $ORACLE_HOME/rdbms/admin

sbcreate.sql – Install standby statspack
sbcusr.sql – Called from sbcreate.sql to create the schema
sbctab.sql – Called from sbcreate.sql to create tables holding snapshots
sbaddins.sql – Called from sbcreate.sql to add a standby database instance to the configuration.

sbaddins.sql – Add a standby database instance to the configuration
sbcpkg.sql – Called from sbaddins.sql to create the instance specific statspack package

sblisins.sql – List instances in the standby statspack configuration

sbreport.sql – Create a standby statistics report
sbrepcon.sql – Called from sbreport.sql to get the report configuration
sbrepins.sql – Called from sbreport.sql to create the actual report

sbpurge.sql – Purge a set of snapshots identified by low and high snapshot ids

sbdelins.sql – Delete an instance from the standby statspack configuration

sbdrop.sql – Drop the stdbyoperf user and tables of the standby statspack
sbdtab.sql – Called from sbdrop.sql to drop tables
sbdusr.sql – Called from sbdrop.sql to drop user, must run from an account that connects to internal (SYS)

 

摘自:Installing and Using Standby Statspack in 11g (文档 ID 454848.1)

Use EXPDP FOR Physical Standby Database

0

Posted on by

  正常情况下在物理dataguard上是不能expdp,还好expdp有个NETWORK_LINK功能,可以巧妙的导出dump文件。

 

如果是10g,需要先open下

SQL> alter database recover managed standby database cancel;
SQL> alter database open read only;

 

–在一个临时数据库上新建一个到物理dataguard的dblink

SQL> create database link expdp_primary connect to system identified by password using 'standby_database';
SQL> select sysdate from dual@expdp_primary;
SQL> create directory datapump as '/u01';


–在临时数据库的服务器上执行
expdp system/password directory=datapump network_link=expdp_primary full=y dumpfile=standby_database.dmp logfile=standby_database.log

 

如果是exp就没有问题,直接可以导出。

参考:How To Use DataPump Export (EXPDP) To Export From Physical Standby Database (文档 ID 1356592.1)

ORACLE 11G SNAPSHOT STANDBY转换

0

Posted on by

PHYSICAL STANDBY Convert to SNAPSHOT STANDBY

 

SQL> alter system set DB_RECOVERY_FILE_DEST_SIZE=50g scope=both;
 
SQL> alter system set db_recovery_file_dest='/u01/flasharch' scope=both;

SQL> alter database recover managed standby database cancel;

SQL> select database_role,db_unique_name,open_mode from v$database;

SQL> alter database convert to snapshot standby;

SQL> select database_role,db_unique_name,open_mode from v$database;

SQL> alter database open;

 

 

SNAPSHOT STANDBY Convert to PHYSICAL STANDBY

 

SQL> shutdown immedaite

SQL> startup mount

SQL> select database_role,db_unique_name,open_mode from v$database;

SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;

SQL> select database_role,db_unique_name,open_mode from v$database;

SQL> alter database recover managed standby database using current logfile disconnect from session;

 

 

大量update导致的逻辑dataguard延迟

1

Posted on by

处理过程:

 1.关闭apply

alter database stop logical standby apply;

停止不掉可以强制关闭

alter database abort logical standby apply;

 

2.排除表

EXEC DBMS_LOGSTDBY.SKIP('DML','ORADBCA','T_TRANSACTION_ORDER');

update system.logstdby$skip
set esc = '\'
where esc is NULL;
commit;

 

3.重新应用

alter database start logical standby apply immediate;

 

4.关闭应用

alter database stop logical standby apply;

 

5.把排除的表,重新恢复同步

EXEC DBMS_LOGSTDBY.UNSKIP('DML','ORADBCA','T_TRANSACTION_ORDER');

 

6.表初始化

exec dbms_logstdby.instantiate_table('ORADBCA','T_TRANSACTION_ORDER','Pridb');

 

7.起同步进程

alter database start logical standby apply immediate;

 

8.check check

 

 

–附检查sql

select * from V$LOGSTDBY_STATE;

select * from V$LOGSTDBY_STATS;

 

select * from V$LOGSTDBY_PROGRESS;

select * from V$LOGSTDBY_PROCESS;

 

 

select 'Logical standbydb delay '||(sysdate-lp.APPLIED_TIME)*24*3600||'s' delay from v$logstdby_progress lp,v$database d

where d.database_role= 'LOGICAL STANDBY';