Oracle / EMC - Snap View Snapshots And Consistent Sessions
Nov 22, 2010
I have some questions about Oracle + EMC shared storage. I have Oracle 11gR1 RAC (2nodes) + ASM environment with shared shorage EMC Clariion AX4.
The database is running no archivelog mode. I'd like to implement point-in-time recovery using Snap View snapshots.
Currently my AX4 platform has the following LUNS:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
Using source LUNs in consistent session will take sync snapshots of all the LUNs working against my database. Once something happens with database, the LUNs can be returned to specific point in time when snapshot consistent session was taken and I'm expecting the database will up and continue to work.
Questions:
1. Is my approach correct at all? (The database is running noarchivelog mode, not dealing with hot backups. The recovery point in time implemented purely via EMC snapshot consistent sessions.)
2. The AX4 platform has a limit 8 source LUNs in session. If I understand it correctly, I can't place more than 8 LUNs in session. What will be the workaround if my database will occupy more than 8 LUNs, I'd like to take their consistent snapshot; for example:
LUN1 - registry 1
LUN2 - registry 2
LUN3 - vote 1
LUN4 - vote 2
LUN5 - vote 3
LUN6 - ASM - DISKGROUP DATA DISK DATA01 (actual db datafiles)
LUN7 - ASM - DISKGROUP DATA DISK DATA02 (actual db datafiles)
LUN8 - ASM - DISKGROUP DATA DISK DATA03 (actual db datafiles)
LUN9 - ASM - DISKGROUP DATA DISK DATA04 (actual db datafiles)
I have read almost all docs about distributed transaction on tahihi.oracle.com website,But I can find a statment about this:
Can Oracle always guarrantee the data consistent in a distributed transaction?
For example,there is a distributed transaction on node a,node b and nod c.node b and node c informed node a they were prepared,so node a committed,and inform node b and node c commit.then node b committed and feedback,but network on node c broken at this point,So node a can't not get feedback from node c,but node a and node b has been committed, so what will Oracle do in this condition?
If node c rollback the data on local node, consistent in this distributed transaction was failed ,yes?
When I want to generate AWR report for database, I dont see any Snap IDs. What was disabled?
Is it possible to generate awr reports for unseen snap IDs?
Here below is the log: Current Instance ~~~~~~~~~~~~~~~~ DB Id DB Name Inst Num Instance ----------- ------------ -------- ------------ 49472052 WPSDBSTG 1 wpsdbstg
Specify the Report Type ~~~~~~~~~~~~~~~~~~~~~~~ Would you like an HTML report, or a plain text report? Enter 'html' for an HTML report, or 'text' for plain text Defaults to 'html' Enter value for report_type: html
Type Specified: html
Instances in this Workload Repository schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ DB Id Inst Num DB Name Instance Host ------------ -------- ------------ ------------ ------------ * 49472052 1 WPSDBSTG wpsdbstg rcolnx88700
Using 49472052 for database Id Using 1 for instance number
Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots.
Enter value for num_days: 1
Listing the last day's Completed Snapshots
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:
I have an existing view that stores some data. I was asked to create a table based on the columns present in the view. I only copied the table structure from the view.
Now I would need to create a stored procedure that executes the query and stores the data in the table with a snap id and date of snap column. The reason this needs to be done is to show differences over time for historical and trending.
our db shows more than 200 INACTIVE Sessions ; and the DBA plans to reboot the db to get rid of these sessions . Can we not KILL these sessions and avoid the reboot ?
I am using oracle 10g as server in my lab. I faced some problems initially, but later after increasing the USERS tablespace it is working fine.
But there is still one problem. During the query execution some queries will be blocked and it doesn't leave any consequent queries to execute from the same user.
The blocked sessions will be displayed in the admin page under blocking sessions link. There is a option to kill the session. But when i do that, it affects all the users and the connection will be lost to all the users. again I have startup the database from beginning.
The sqlcache is getting over-written.I would like to capture information from AWR snapshots and feed those (as a workload) into the DBMS_ADVISOR.I can't see where it's possible (other than manually creating my workload from AWR information)
I was just wondering how Oracle manages multiple sessions in a database performing DML. I believe this is related to 'Read Consistency' and I tried to search for the same but could not get any satisfactory online documents.
CASE 1: user A logs in to a database1 issues select on table A and then inserts 4 rows user B logs in to databse1 issues select on table A and then inserts 5 rows issues rollback user C logs in to a database1 issues select on table A and then inserts 6 rows issues commit
How many rows can user C see in the table A when he issues select?
CASE 2: user A logs in to a database1 issues select on table A and then inserts 4 rows user B logs in to databse1 issues select on table A and then inserts 5 rows user C logs in to a database1 user B issues rollback user C issues select on table A and then inserts 6 rows issues commit
How many rows can user C see in the table A when he issues select?
NOTE: All the users are currently logged in to the same database and none has logged out.
We are experiencing a problem with SSO causing 2nd or 3rd concurrent Oracle sessions to hang. The Oracle application hangs during loading and the task manager has to be used to close the application.
I have tested logging onto our application servers using SSO and I cannot load more than 3 concurrent Oracle sessions. When I bypass the SSO and logon to the same server I can load more than 20.
I have a table MYTABLE in database mydb1 duplicated via materialized view and materialized view log and refresh_snapshot commands to a MYTABLE on mydb2 database.
I like to duplicate this table MYTABLE to a third database mydb2, using the same method (materialized view and refresh_snapshot command).
Is it possible ? What's hapend to the materialized view log where I launch a refresh_snapshots on mydb2 ? How is this materialized view log truncated ?
How can check database performance in a non-sequential parts of a day using AWR tool. For example, suppose we divide a day to 2 parts: low-traffic time and high-traffic time with the following time interval:
low-traffic time : 7:00am - 10:00am and 7:00pm - 11:00pm high-traffic time : 10:00am - 01:00pm and 4:00pm - 06:00pm
I want to examine performance using snapshots gathered by AWR. If I get 2 AWR reports for low-traffic time of day (one for 7:00am - 10:00am, another for 7:00pm - 11:00pm ) and compute average of values in 2 reports, could I called it database performance in low-traffic part of day or not?
Simplifying the data structure that my problem concern, let's say there are two materialized views between whose data there's a one-to-many relationship [the relationship can be logical without a need of creating any foreing keys].
The data should be as actual as possible respecting the content of the master tables, let's say it shoul be refreshed every 5 minutes.
As far as I know, the jobs related to each snapshot, even if they have a START WITH and NEXT parameter set to the same value, work independently...So: what would be the best manner to synchronize the jobs so as to make sure the data of both snapshots are coherent?
If I create an AWR Report via OEM or awrrpt.sql for an interval between two snapshots, in the report there are statements which were not executed in this interval.
Are there any known bugs for that problem or have you got an explanation for this behavior?
i am given a database to recover. The person who gave me the database says that he has taken a full hot backup of the database on 19th march and i was also given the archives of the incremental backups until 21march now how can i check if the backup he has taken is consistent or not? after unzipping the available archivelogfiles on and after 19th(THE DATE on which full backup was taken) i tried to open the database using
>startup mount
>recover database using backup controlfile until cancel;
here i got an error and i found out that the archive log files after 13 march are missing.i queried select * from v$log and found that the last log was on 13 March. is this the correct way to say that, since this returned 13 MARCH the database is taken full backup on 13 March itself?
but the person who has taken full backup is saying that he has taken the full backup on 19th march.
how can i confirm whether the full backup taken on 19th was not consistent concretely? are there any other ways to find whether a backup taken is consistent or not i am not using RMAN backup i want to know the procedure when i have to do it manually
I have already added the indexes for the outer sql to reduce the cost from 374 to 150. But the the consistent gets seems not reduce that much. I notice that the function will cost 6 consistents gets per execution. It seems it not very high. But if this function goes with the sql, it will cost a very high consistent gets, if I remove this function, the sql only cost 1903 consistents gets.
So what I am thinking there should be two ways to reduce the consistent gets..The first one is reduce the recursive call of the sql. The second is reduce the consistent gets of the function. (but it seems that the consistent gets in this function is very low, only 6.)
RUN{ SET NEWNAME FOR DATAFILE 1 TO 'F:oracleproduct10.2.0oradata estsystem01.dbf'; SET NEWNAME FOR DATAFILE 2 TO 'F:oracleproduct10.2.0oradata estundotbs01.dbf'; SET NEWNAME FOR DATAFILE 3 TO 'F:oracleproduct10.2.0oradata estsysaux01.dbf'; SET NEWNAME FOR DATAFILE 4 TO 'F:oracleproduct10.2.0oradata estusers01.dbf'; SET NEWNAME FOR DATAFILE 5 TO 'F:oracleproduct10.2.0oradata estusers02.dbf'; SET NEWNAME FOR DATAFILE 6 TO 'F:oracleproduct10.2.0oradata estperfstat.dbf'; SET NEWNAME FOR TEMPFILE 1 TO 'F:oracleproduct10.2.0oradata est emp01.dbf'; [code]....
Recovery Manager complete.It seems when i run alter database open resetlogs i get this:
ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'F:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF'
I am having performance issues on a query in a production environment that I cannot replicate in our test environment. Our test environment is an import of production. Version information is:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production TNS for Solaris: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - ProductionWhen I run the query in test, I get the following results using TKPROF [code]....
Misses in library cache during parse: 1 Optimizer mode: ALL_ROWSThis performance problem started a few weeks ago and the problem seems to be in the high number of consistent reads during fetch. The DBA tried restarting the instance and gathering fresh stats on the tables but that has not made a difference. Now he wants to export the tables, drop the schema and re-import.
APEX_ITEM functions do not seem to be working correctly. When I create a region with this query:
select APEX_ITEM.SELECT_LIST_FROM_QUERY(1, 1, 'select ''Jan'' d, 1 r from dual union select ''Feb'' d, 2 r from dual') month_id
from dual I get the error:report error:ORA-06502: PL/SQL: numeric or value error: associative array shape is not consistent with session parametersWhen I run that query in SQL Developer I get this:
which is correct.I tried changing NLS_COMP parameter from Linguistic to Binary and changing NLS_SORT to BINARY_CI but that did not make a difference. This seems like a rare problem and generally related to installation but it has me stopped dead in my tracks.
I am trying to open the database bur gettinf following errors
ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent ORA-01110: data file 1: 'C:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF'
SQL> shutdown ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 289406976 bytes
Actually, i have test database open in read only and i want to apply archive files carry from production database at daily level. When i'm going for recovery, since database is already in consistent state, it is not asking for recovery.
When we set Consistent parameter as yes during export, we get a consistent dump of the database/ schema from the point it was taken by setting the transaction Read only.
So let say we are exporting a table TAB_1 and at the same time a different user updates one of the row of this table and then another user updated this row again so and so forth. So from where do export gets the image of this row which was present at the point in time when the export was initiated in the first place. Is it the Undo?
I have set my processes and session value in my database as 1000 and 1248 respectively. I am using 11.2.0.1 but when I restart my DB it showing me sessions=1524...its too high value then what I set.I don't know why it showing higher value than the value I set...
ALTER SYSTEM SET processes=1000 SCOPE=SPFILE; ALTER SYSTEM SET sessions=1248 SCOPE=SPFILE;
Starting up: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. Using parameter settings in server-side spfile /u02/app/oracle/product/11.2.0/db_1/dbs/spfileeng11g02.ora System parameters with non-default values: processes = 1000 sessions = 1524
And one more thing is there any recommended formula for calculate sessions in 11g? [ I found one formual from google: SESSIONS derived: (1.1 * PROCESSES) + 5
One of our DB server is high on CPU utilization, as per the AWR we found couple of queries are running many times and taking huge CPU, hence we need to know from which user these queries are runnings.find the session information as per the sql id?
I'm using Toad 11.6, I can see about 156 sessions for the database in the session browser but when I do a select from v$session, only 40 sessions are showing up. I was able to look at all the sessions until yesterday. I tried several views like v$session, v$open_cursor etc, but only 60 sessions show up. I'm connected to the database using same user login yesterday and this morning.. May be this user had some privileges revoked last night??If so is it possible to limit sessions in these views (v$session etc) but it's strange that I can see them in the session browser in Toad. 'm very confident that there are 156 sessions in the database but it's just that I'm not able to see them in the v$session etc views. I need to troubleshoot a session but it is not showing up in any views.
