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
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
/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
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
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';
alter system set log_archive_dest_2='service=boston lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=boston';
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
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
- 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
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:
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:
3. Oracle instance crashed when I tried to start it nomount.
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.
ORA-07445: exception encountered: core dump [SIGSEGV] [Address not mapped to obj ect]    
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: , , , , , , ,  ORA-06553: PLS-801: internal error 
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
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
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.
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;
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.
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
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.