Dataguard Log Query

Dec 3, 2012

This is my dataguard check script:

/home/oracle/bin/check
if [ $# -lt 1 ]; then
echo "usage: check <oracle_sid>"
echo
exit
fi

[Code]...

This is the output it gives on the primary:

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PARTHREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 prod
xxxx
11.2.0.3.0 29-NOV-12 OPEN NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

[Code]....

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

This is the output it gives on the standby:

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
HOST_NAME
----------------------------------------------------------------
VERSION STARTUP_T STATUS PARTHREAD# ARCHIVE LOG_SWITCH_WAIT
----------------- --------- ------------ --- ---------- ------- ---------------
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
---------- --- ----------------- ------------------ --------- ---
1 prod_stby
xxxx
11.2.0.3.0 29-NOV-12 MOUNTED NO 1 STARTED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

[Code]....

SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

When I run the following:

SQL> select archivelog_change#-1 from v$database;

On the primary I get:

ARCHIVELOG_CHANGE#-1
--------------------
4902447998

On the standby:

ARCHIVELOG_CHANGE#-1
--------------------
4902447999

Which makes sense to me as my understanding of this is that the log is written, send to the standby, applied, then applied on the primary, yes?

So my questions are:

Why is there a lower entry on the primary (234) marked as opening, when it's closing 235, and writing 236, what does LNS mean?

Why is the second archive on the standby saying "CONNECTED 0" and do RFS, & MPR0 mean?

My init files say this:

Primary:
log_archive_dest_1='LOCATION=/u01/archive/prod/arch'
log_archive_dest_2='SERVICE=prod_stby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod_stby'

Standby:
log_archive_dest_1='LOCATION=/u01/archive/prod_stby/arch'
log_archive_dest_2='SERVICE=prod LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

Geek, scruff, economics wonk, a mind forever voyaging.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Configure RAC TO RAC Dataguard

Apr 27, 2012

i want to configure rac to rac dataguard 10g( primary is having 2 nodes and standby also 2 nodes).

oracle 10g/os linux5.

View 1 Replies View Related

Active Dataguard Database

Jul 17, 2013

I've got a physical standby database and I'm now licensed for Active DataGuard. I would like to automate the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION command to be executed right after the STARTUP command so that whenever the database is started by our DBSTART script, it activates READ ONLY W/APPLY immediately after start without manual intervention. This is probably simple, but I figured I'd ask before starting to pore through documentation.....!

View 6 Replies View Related

Uses Of ORAPWD In RMAN And Dataguard

Aug 29, 2012

I know the uses of ORAPWD utility and REMOTE_LOGIN_PASSWORDFILE initialization parameter.I wish to know that, what will be the primary purpose of using ORAPWD passwordfile in the following situations.

1. RMAN Cloning.
2. Dataguard Standby configuration.

View 4 Replies View Related

DataGuard - Standby Redo Log Files

Sep 3, 2012

What is the purpose of Standby Redo Log Files in Data Guard configuration? When it is utilized by the database?

View 4 Replies View Related

SQL Script Validation For Use In Dataguard Environment?

Feb 21, 2013

We have a 10g environment with DataGuard running for reporting purposes. Our application team is getting ready to upgrade their application and it requires that several SQL Scripts be run to perform the primary database updates. A couple of them are over 1 MB in length. My concern is that these scripts will break DataGuard. I am not a DBA by any stretch, but I seem to remember there are certain limitations with DataGuard.

I have reached back to the team and asked them to have the Vendor confirm these scripts are validated for a DataGuard environment and I have also reached out to the System DBAs for review. I am wondering if there is an utiility available that can check the SQL to confirm if there are any DDL or DML statements that will not work in DataGuard. Likewise, we are going to run these scripts in a non-production DataGuard enabled environment first, but I want to have some level of confidence, that this will be a success.

View 3 Replies View Related

Dataguard Setup Primary And Standby

Dec 2, 2011

I have a dataguard setup, a primary and a standby.my instance is testdb.i have set all necessary dataguard parameters and everything seemed to work well initially with logs being moved from primary to secondary and being applied there.but all of a sudden i see that the primary pushes logs to the standby and at the standby end these logs are available but not applied.

when i try to execute the: alter database recover managed standby database cancel; it hangs for a long time and never returns to sql prompt.

i bascially want to stop and restart the MRP process to apply the logs but cannot cancel the managed recover mode.Is it advisable to kill the mrp process at os level using "kill -9 "?

View 7 Replies View Related

Exadata :: Configuring Dataguard With DR Server And DBM

Oct 10, 2012

Refer a MOS Note for configuring data-guard on Exadata with DR server being and Exadata DBM too.

View 4 Replies View Related

Auto Startup Active DataGuard After Reboot

Apr 6, 2011

I try to auto startup my standby database after reebot machine. I used trigger " on startup" , srvctl but still it doesn't work..Database 11g , operating system RedHat 5 or HP-UX.

View 2 Replies View Related

Dataguard / Logs Not Shipped To Standby With Error

Oct 26, 2010

I have two Oracle 10.2.0.4 databases, on two SUN M5000 (Solaris 5.10). One is primary (EXP1), the other is the standby (EXPBKP). Both SID is 'EXP'. If I do a manual recover (after I sync'ed primary archive logs from primary to standby), it works. If I do a manual log switch, logs are shipped AND applied on the standby. But after a while, I can see some errors on primary alert log , and logs are not shipped anymore :

CODE Errors in file /applications/oracle/admin/EXP/bdump/exp_arc2_1277.trc:
ORA-03135: connection lost contact
Tue Oct 26 08:30:14 2010
FAL[server, ARC3]: FAL archive failed, see trace file.
Tue Oct 26 08:30:14 2010
Errors in file /applications/oracle/admin/EXP/bdump/exp_arc3_1279.trc:
ORA-16055: FAL request rejected
ARCH: FAL archive failed. Archiver continuing
[code]....

View 1 Replies View Related

Data Guard :: Difference Between Standby And Dataguard

Jun 29, 2010

Difference between standby and dataguard. I assume standby and dataguard are same?

View 4 Replies View Related

ORA-01109 - Setup 11g Dataguard - Physical Standby

Jul 12, 2011

I have setup 11g Dataguard - physical standby. Everything seems to be fine. I have also tested switchover and failover using DG broker which seems to work fine.All, I would like to know is abt ORA-01109 during switchover..

My config - RACprim- RACstby
Primary - TEST_prim (instances - TEST1, TEST2)
Standby - TEST_stby (instances - TEST1, TEST2)

DGMGRL> SWITCHOVER TO TEST_stby;
Performing switchover NOW, please wait...
New primary database "TEST_stby" is opening...
Operation requires shutdown of instance "TEST1" on database "TEST_prim"
Shutting down instance "TEST1"...
ORA-01109: database not open
[code].....

This ORA error occurs when it tries to shut down primary. When primary is in open state, why its saying that database not open. Operation requires shutdown of instance "TEST1" on database "TEST_prim"..Shutting down instance "TEST1"..

View 4 Replies View Related

Process To Refresh Schema In DataGuard Enabled Environment

Feb 21, 2013

We have a non-production Oracle 10g Cluster running on Linux, with DataGuard (logical standby). From time to time, we need to refresh the schema on primary, but to do so as always caused problems with the logical standby. Our DBAs can never get it to complete successfully. They have tried a bunch of different methods (even provided from Oracle), but it does not work. e have a bunch of skip statements on the

Everytime, we need to refresh the schema, we have to build the entire database (primary and logical standby) from production RMAN backup. As you can imagine this is a very time consuming ordeal. There has got to be a way this process can be completed in a timely manner.

I was thinking of the following....
1) shut down dataguard, log shipping
2) lock user, kill sessions, drop user from primary
3) lock user, kill sessions, drop user from logical standby
4) run impdb on production export file on primary
5) run impdb on production export file on logical standby
6) re-enable dataguard, log shipping
7) confirm logs being applied, databases in sync

We are only replicating the one schema to the logical standby. I am not clear on how redo logs would be applied to the logical standby. There are hundreds of them @ 100 mb each, so I would think if we do this independently, I could somehow sync primary and logical standby after the imports complete.

View 2 Replies View Related

DataGuard - Archive Log Apply Gap In Physical Standby Database

Nov 23, 2010

1) The Primary Database is UP. The Physical Stand By Database is DOWN. The Current Archive Log Sequence is 99 in Primary.

We have to apply Archive Log from 51 to 99 to the Standby Database. But Unfortunately, there is no backup of those Archivelogs and the ArchiveLogs from 51 to 98 have got deleted at Primary end.

Now how will you apply these Archive Logs from Primary Database to Physical Standby Database?

Note : The Physical StandBy Database is DOWN.

View 2 Replies View Related

RAC & Failsafe :: Configure Oracle Dataguard For 2 Node Database

Oct 28, 2010

We have to configure Dataguard for our 24x7 critical banking 2 Node RAC database(10.2.0.4). Before proceeding with the configuration we have to make sure what steps we should follow to have minimum or no downtime.

1) Document covering DG setup in RAC environment.
2) We have to perform switchover as well so need its steps too. Either the normal switchover steps would be used or have to stop/start rac services as well.

View 7 Replies View Related

RAC/ASM Clusterware Installation :: Dataguard Trigger For Linux Services?

Oct 3, 2012

we are running oracle database 11.2.0.3 and tomcat on the same machine(SLES 11).

I need to contol tomcat(start/stop) depending to database status. For example, if database stops or become standby then to stop tomcat service.

View 2 Replies View Related

Recovery Manager (RMAN) :: Duplicate On Active Dataguard Physical Standby

Feb 8, 2013

In DR site. i have to clone the database from pshysical standby database and clone db will be normal db not standby. Is it possible to do rman duplicate from active dataguard? will it support the duplicate database from active database or i have to take the rman backup of standby database and duplicate from backup piece.

View 2 Replies View Related

Data Guard :: Unable To Get Data Of Primary In Standby Database (dataguard)

Jan 16, 2013

i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..

Primary alert log

Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated)))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production

[code]....

View 1 Replies View Related

Data Guard :: Possible To Use Dataguard From Single Instance To RAC Instance?

Jul 1, 2010

Is it possible to use oracle dataguard from single instance to RAC instance ? Does Oracle dataguard support this ?

View 1 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related

SQL & PL/SQL :: Oracle Query Joining A Table With Existing Query?

Jun 19, 2012

I have the following four tables with the following structures Table A

ColA1 ColA2 ColA3 ColA4 ColA5 AA 100 CC DD EE

Table B

ColB1 ColB2 ColB3 ColB4 ColB5 AA 100 40452 A9 CDE

when these two tables were joined like the following:

Select colA1,ColA2, ColA3, ColA4, ColB3,ColB4, ColB5 from table A Left outer join (select ColB3, ColB4, ColB5 from table B where colB3 = (select max(colB3) from table B ) on (colA1 = colB1 and ColA2 = col B2)

Now i have to join the next table C with table B

Table C structure is

ColD1 ColD2 ColD3 Desc1 A9 Executive Desc1 A7 Engineer

I have the common column such as ColD2 and colB4 to get the Col D3

how do i join the existing query + join between table b and table c?

View 4 Replies View Related

Forms :: F11(Query Mode) And Execute Query In Oracle?

Jul 17, 2011

how to achieve F11(Query mode) and Execute Query in Oracle Forms?

View 1 Replies View Related

SQL & PL/SQL :: Query Using DBLink Returns More Rows Than Direct Query?

Apr 6, 2010

I have a query that is pulling back more rows when I use the dblink than when I hit the linked database directly.

For example:

select
x,y,z
from
mytable@dblink

returns 788,324 rows

while
select
x,y,z
from
mytable

returns 712,102 rows

It's the exact same query, with the only difference being the dblink. It's not pulling the data into a cursor or array, it's a simple, straightforward query on a remote database.

View 10 Replies View Related

Getting Top-N Query To Work As Sub-select In Larger Query?

Mar 10, 2012

Is there a technique to getting a Top-N query to work as a sub-select in a larger query -or- is there another way to generate Top-N like results that works as a sub-select?

Background:

We have a large query that is being used to build an export from a legacy HR system to a new one. Amount the data needed in the export is the employees primary phone number.

The legacy HR system allows multiple phone numbers to be stored in a simple table structure:

SELECT emp_id, phone_type, phone_number
FROM employee_phones

emp_idphone_typephone_number
------- --------------- -------------------
46021CELL2222222222
46021HOME1111111111
46021WORK3333333333

The new HR system does allow for multiple phone numbers, however they need a primary phone number identified and stored with the employee master information. (Subsequent phone numbers get stored in alternate table.)

From a business perspective, we have decided that if they have a HOME phone in the legacy system that should be the primary in the new system, if no HOME phone, then WORK, if no WORK then CELL.

That can be represented as:

SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111
46021WORK2222222222
46021CELL3333333333

Or similarly with Top N concept:

SELECT *
FROM (SELECT *
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

emp_idphone_typephone_number
------- --------------- -------------------
46021HOME1111111111

Or really what I want in my export:

SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = '46021'
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1

phone_number
-------------------
1111111111

However, when the Top-N query is added as a sub-select in a larger query using the employee id from the larger query (WHERE emp_id = export.emp_id), it fails saying that �export.emp_id� is not a valid id.

(SELECT phone_number
FROM (SELECT phone_number
FROM employee_people_phones
WHERE emp_id = export.emp_id
ORDER BY decode(phone_type, 'HOME', 'a', 'WORK', 'b', 'CELL', 'c', 'z')) results
WHERE ROWNUM = 1)

1.Any way around this? Is it possible to put a Top-N (with a WHERE clause using data from the main query) in a sub-select?

2.Any alternatives (other than Top-N) to delivering a ROWNUM=1 result with a �custom� ORDER BY statement?

Other Notes: Yes, we know we could do two queries in the data conversion first deliver the bulk data to the target table, and then update with the phone numbers. However, for multiple reasons, that is less than desirable.

View 3 Replies View Related

SQL & PL/SQL :: How To Read Field From Query In Another Query

Sep 19, 2010

I am having a Select query(below Query1) and I want to use one column(sum(col4)) from this Select query to be displayed in another Select query(Query 2). how to display this.

Query 1 :-
select a.col1,a.col2,b.col3,sum(b.col4)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3

Query 2 :-
select a.col1,a.col2,b.col3,sum(b.col6)
from tab a, tab b
where a.key1=b.key1 and a.key2=b.key2
group by a.col1,a.col2,b.col3,b.col5

View 4 Replies View Related

PL/SQL :: Converting Inner Query To Sub Query

Sep 18, 2012

This query is written in inner join, can any one try to write using sub query.

SELECT B.CNO
FROM CUSTEN A
INNER JOIN ORDS B
ON A.CNO = B.CNO
AND A.PRNO = B.PRNO

[Code]...

View 4 Replies View Related

SQL & PL/SQL :: Divide Count From One Query By Count From Another Query

May 24, 2010

I have the folloiwng two queries:

Query_1: select count(*) yy from table1;
Query_2: select count(*) zz from table2;

I need to compute the following:

var:=(yy/zz)*100

How can I achieve this in a single query?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved