All 21 entries tagged Oracle-Availability

No other Warwick Blogs use the tag Oracle-Availability on entries | View entries tagged Oracle-Availability at Technorati | There are no images tagged Oracle-Availability on this blog

September 09, 2008

Reflect on configure of standby db

We recently switchover to standby db due to some problem on primary db.
On thing annoy me is that some non-dynamic parameters are not configure decently. Since we switchover to standby in a rush, we did not have a change to re-start the server.

This is the list of them


alter system set control_files='c1.ctl,c2.ctl,c3.ctl' scope=spfile (double check the syntax)
alter system set sga_target=5g scope=spfile;
alter system set pga_aggregate_target = &pga_aggregate_target scope=spfile;
Alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
alter system set processes = 500 scope=spfile;
alter system set audit_sys_operations=true scope=spfile;
alter system set audit_trail=xml scope=spfile;

June 09, 2008

Physical standby file management

/package/oracle/product/10.2.0/dbs/UNNAMED00008 indicate a failure to create corresponding primary datafile.

To fix this:

ALTER DATABASE CREATE DATAFILE '/package/oracle/product/10.2.0/dbs/UNNAMED00008' as '/package/oracle/oradata/remus/undotbs01.dbf' ;

If manged recover service stopped, restart it.


March 18, 2008

Logical standby ORA–01403: no data found

Writing about web page http://orajourn.blogspot.com/2007/02/setting-up-logical-standby-database.html

Our logical server keep on throwing ORA-01403: no data found and stops the logical standby services.

A developer connect to standby server and created a table. ( Standby server should be configured to Guard ALL not Guard None)

Then he realised and create a new table.

Oracle noticed the conflict that standby table was created before primary table. Every time the table is updated, it will throw ora-01403

Tried to modify the table on primary in hope to refresh the table on standby. Error persist.

DBMS_LOGSTDBY.INSTANTIATE_TABLE fixed the problem


February 26, 2008

Wake up Oracle RFS

Follow-up to Force Oracle primary DB to reset connection to logical standby from Oracle/Java/Others

After another short period network outage, the primary database y wont sent archivelog to standby server.

set log_archive_dest_state_n = defer| enable cannot wake up oracle this time.

Fixed one server using the following:

Changed
alter system set log_archive_dest_2='service=boston valid_for=(online_logfiles,primary_role) db_unique_name=boston';

to
alter system set log_archive_dest_2='service=boston lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=boston';

Update:
The preceding method only works slight better. During nigthtly rman backup, several logs missing (Omit 100 words …). Eventaully set log_archive_max_processes to a higher value fixed the problem


January 31, 2008

Physical standby vs logical standby

In a 3-tie application scenario:

  • If primary db dies, developers can just re-config java connection pool to point to standby db without DBA to manually open the db. For physical standby, you have to manually open it read only
  • If primary db wont come back and you want to do a failvoer, you have to restart it if the physical standby database has been opened in read-only mode since the last time it was started. Since the physical db has been oepned read-only in precedding step, you must have to restart it. That means downtime of the whole site.
  • If primary do come back , but you do not trust it anymore. You can do a switchover. That will certainly have to re-start physical standby.

January 15, 2008

Several things to break logical standby database

  • Update a row in Oracle SQL Developer. SQL develper will use a ROWID to update the row. After this sql is applied to standby, it will complains since the ROWID is different on standby.
  • Drop a constraint with system generated name (e.g., sys_cxxx). When create a index/constrinat, give it a name. Otherwise Oracle will generate a name SYS_C003154 and the name on standby db will be likely different. When drop the constraint on the primary server, the SQL will failed on standy server since Oracle complains wont find the constraint/index.

September 18, 2007

Create a 10gR2 logical standby for a 10gR1 database

Primary Db is 10.1.0.3 32 bit running on Solarix 9 X86 platform. A new server is Solaris 10 X86 64 bit. To upgrade the new primary db, we plan to create a logical standby db on new server using 10.2.0.3.

1. I installed 10.2.0.3 software on standby server, duplicate primary db and created physical db succesfuuly. The DB is automatically converted it to 10gR2.
However, when tried to run “alter database recover managed standby database disconnect from session”, the primary server reported error message as follows:

FAL[server]: Version mismatch, incoming FAL request aborted
FAL[server]: FAL request for destination dionysus was:
GAP - thread 1 sequence 20770-20824
DBID 1520159046 branch 573933382
FAL[server]: Fail to queue the whole FAL gap
GAP - thread 1 sequence 20770-20824
DBID 1520159046 branch 573933382 

Oracle support confirmed:
Even in 10.x DataGuard, Primary and Standby Database have to be on the same Release. The only Exception is during Rolling Upgrade using Logical Standby Database.
2. Because primary and standby have to be same release, so 10gR1 has be to installed first. Since the official release 10gR1 32bit does not support Solaris 10, the /Disk1/install/oraparam.ini has to be changed and add the 5.10 in the support list. The 10gR1 installed smoothly. Oracle support approved my trick:

Changing the oraparam.ini is ok. If you use different Wordsizes, basically this is no Problem and has no Performance Impact. Please be aware of Note 414043.1: Role Transitions for Data Guard Configurations Using Mixed Oracle Binaries (32bit/64bit) and the Same OS Family if you perform any Role Transistions.
3. Oracle instance crashed when I tried to start it nomount.

ORA-07445: exception encountered: core dump [SIGSEGV] [Address not mapped to obj
ect] [1782579204] [] [] []

Oracle support suggested : Apply Patch 4186426 and patchset 10.1.0.5 on this ORACLE_HOME

The instance started up after patching.

4. When I tried to duplicate the primary db to standby server, it reported:

RMAN-06136: ORACLE error from auxiliary database: ORA-06550: line 1, column 166:
PLS-00553: character set name is not recognized
ORA-06550: line 0, column 0:
PL/SQL: Compilation unit analysis terminated
RMAN-06031: could not translate database keywor

fixed this by setting enviroment vairable :

export NLS_LANG=”English_United Kingdom.WE8ISO8859P1”

5. The physical db created. After converting it to logical standby and starting logical standby, it complained

ORA-12801: error signaled in parallel query server P001
ORA-06550: line 1, column 250:
PLS-00306: wrong number or types of arguments in call to 'LOGMNR_KRVRDA_TEST_APPLY'
ORA-06550: line 1, column 250:
PL/SQL: Statement ignored

fix this by rerun catproc.sql in standby. Metalink suggested to run this on prmary however.


6. The next step was to upgrade the logical standby db. Came across error again:when run up catupgrd.sql:

CREATE OR REPLACE FUNCTION version_script
*
ERROR at line 1:
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [],
[]
ORA-06553: PLS-801: internal error [56319]

This seemed to be the bug 5333374 or 5079213. Opened a new SR and got response as:

1 Change catupgrd.sql to always execute utlip.sql

% cd $ORACLE_HOME/rdbms/admin
% cp catupgrd.sql catupgrd_orig.sql
% vi catupgrd.sql

change:
==> @@&utlip_file
to:
==> @@utlip.sql

2. Rerun upgrade with echo on

SQL> spool /tmp/upgrade_aft_wa.out
SQL> set echo on
SQL> catupgrd

Appied this and it worked


July 17, 2007

SQL Apply service append ROWID in the SQL

Writing about web page http://orajourn.blogspot.com/2007/02/setting-up-logical-standby-database.html

The logical standby service was broken for a while due to there are large amount of SQL containing ROWID. Logical standby does not support this ROWID.

I thought the SQL are come from the primary DB application, which use Hibernate generating SQLs. After turning on SQL trace using DBMS_MONITOR, there is no such SQL in the trace file.

So something is wrong on logical standby. I then decided to re-instatiate the table using DBMS_LOGSTDBY.INSTATIATE_TABLE, but Oracle keep on give me ORA-39004, ORA-39001, ORA-31631 error.

I then choose to use export on primary and import on standby. After finishing that and turning on logical standby apply, the ROWID clause rears it ugly head again.
After reading this, I then decided to switch log several times before exporting data.

This time, it works ok. It seems the table in primary DB has to been updated first.


June 07, 2007

Logical standby and Flashback

Flashbck helps logical standby a lot:

Flashback a failovered primary db to become a standby.

Step 1 Determine the SCN to which to flash back the failed primary database.
SQL> SELECT APPLIED_SCN AS FLASHBACK_SCN FROM V$LOGSTDBY_PROGRESS;

Step 2 Determine the log files that must be copied to the failed primary database for Flashback Database.
On the new primary database, issue the following query to determine the log files that must be copied to the failed primary database for Flashback Database to reach a consistent state

SQL> SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG 
2>   WHERE NEXT_CHANGE# >  (SELECT VALUE FROM DBA_LOGSTDBY_PARAMETERS WHERE NAME = 'STANDBY_BECAME_PRIMARY_SCN')
5>    AND FIRST_CHANGE <= (FLASHBACK_SCN from step 1);

Step 3 Flash back the failed primary database.

To create a new logical standby database, shut down the database (if necessary),
 mount the failed primary database, flash it back to the FLASHBACK_SCN determined in step 1, and enable the database guard.

SQL> STARTUP MOUNT;
SQL> FLASHBACK DATABASE TO SCN  FLASHBACK_SCN;
SQL> ALTER DATABASE GUARD ALL;

Step 4 Open the database with the RESETLOGS option.

SQL> ALTER DATABASE OPEN RESETLOGS;

Step 5 Create a database link to the new primary database and start SQL Apply.

SQL> CREATE PUBLIC DATABASE LINK mylink CONNECT TO system IDENTIFIED BY password USING 'service_name_of_new_primary_database';

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY NEW PRIMARY mylink;

Flashback standby server to retrieve history data


SQL>flashback database to timestamp ( sysdate - 0.25);
SQL>alter system set audit_trail='OS' scope=spfile;
SQL> shutdown immediate; and restart

SQL>alter database open read only;
SQL> shutdown immediate; 
SQL>alter databse open resetlogs;

Have to open with resetlog. You do not need to do anything and after the standby service is restarted, it is happily apply the history archivelog again.

Flashback a Logical Standby Database After Flashack the Primary


Step 1 Determine the SCN at the primary database.
On primary
SQL> SELECT TO_CHAR(RESETLOGS_CHANGE# - 2) AS FLASHBACK_SCN FROM V$DATABASE;

Step 2 Stop SQL Apply. 

SQL> ALTER DATABASE STOP LOGICAL STANDBY APPLY;
SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;

If the APPLIED_SCN is less than the value of the resetlogs_change#-2, you do not need to flash back the standby database and can proceed to Step 6. This may happen if SQL Apply is running with a delay. 

Step 3 Determine which archived redo log file contains the FLASHBACK_SCN.

On the logical standby database, determine which archived redo log file contains the FLASHBACK_SCN determined in Step 1

SQL> SELECT FILE_NAME FROM DBA_LOGSTDBY_LOG WHERE FLASHBACK_SCN BETWEEN FIRST_CHANGE# AND AND NEXT_CHANGE# 

FILE_NAME
----------------------------------------------------------------
/net/sat/oracle/dbs/hq_sat_146.log

Step 4 Locate the timestamp in the alert.log file.

Locate the timestamp in the alert.log file associated with the SQL Apply initial reading of the first log file displayed in Step 1. For example:

%grep -B 1 '^LOGMINER: Begin mining logfile' alert.log |  grep -B 1 hq_sat_146.log

Tue Mar  7 12:38:18 2005 LOGMINER: Begin mining logfile: /net/sat/oracle/dbs/hq_sat_146.log

Step 5 Flash back the logical standby database to the timestamp.

SQL> SHUTDOWN;
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('11-Feb-09 15:01:00', 'DD-Mon-RR HH24:MI:SS')
SQL> ALTER DATABASE OPEN RESETLOGS;

Step 6 Confirm SQL Apply has applied less than or up to the Apply SCN.

On the logical standby database, issue the following query:

SQL> SELECT APPLIED_SCN FROM V$LOGSTDBY_PROGRESS;

Step 7 Start SQL Apply.

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Think
step 3 and 4 is just to find out the timestamp before resetlogs.
Can we use the SCN ( frist_change#) ?? No.
Can we use the FIRST_TIME ? maybe we can. It will go further back because FRIST_TIME show the time on primary server. If there are delay in log apply service on standby server, then you have to apply extra hours log apply. It might be easier than the documented step.

Retrieve history data
One thing strikes me is that we can set a higher value of undo_retention on standby server. So when we try to retrieve history data, we do not need to do export/import. For a schema contains large tables that is especailly helpful.

The other way is to flashback the standby server, turn in read only mode , make sure the data is OK, then open resetlogs. After retrieving the history data, start logical standby service.

In a nutshell
The standby db need to follow primary closely. If primray resetlogs, so will standby.

Extra

Flashback DB:

Set DB_FLASHBACK_RETENTION_TARGET (minutes) to specify how far we can flashback db.

Set undo_retention (seconds) to specify how far the flashback versions query


June 04, 2007

NON_SCHEMA_DDL

According to Oracle doc,

  • SCHEMA_DDL All DDL statements that create, modify, or drop schema objects (for example: tables, indexes, and columns)
  • NON_SCHEMA_DDL All DDL that does not pertain to a particular schema

So NON_SCHEMA_DDL has very wide range. Among others, it includes all the permissions DDL, all tablespace operations, etc.

If logical standby apply get stuck by a SQL like “grant select on tableA” , we can use dbms_logstdby.skip(‘NON_SCHEMA_DDL’,NULL,NULL,NULL) to skip it.


Search this blog

Search Warwick Blogs

 

Tags

Most recent comments

  • Oracle 11g is the clear leader in this area. Oracle has invested heavily into self–tuning capabiliti… by shaane on this entry
  • All the features are very nice.I like Repair Advisors the most.Oracle 10G introduced some of the dat… by lucy on this entry
  • "logical standby failed to re–start, I am also getting the same error. I have search on internet and… by anemia on this entry
  • Thanks for the nice information because from last three days i am stuck on this problem…. by robot vacuum on this entry
  • So what happen City speed dating when you tried this coding once in your PC.. Please tell me so i am… by John Bergeron on this entry
RSS2.0 Atom
Not signed in
Sign in

Powered by BlogBuilder
© MMXXII