Server Administration :: Continuously Changing Data Blocks?
Jan 10, 2011If the data blocks in the buffer continuously get updated such that they never reach the Least used list of LRU,then when will they be written to disk?
View 19 RepliesIf the data blocks in the buffer continuously get updated such that they never reach the Least used list of LRU,then when will they be written to disk?
View 19 RepliesWe are suffering from very bad application response for last few days, when i try to check and drill down, where the actual contention is? I came to know that there may be contention on data blocks, which may be a prime reason for degraded performance. Herewith i'm pasting my actual stats of gathered from v$waitstat. I gone through some of asktom docs and find that there may be a problem with freelist or segment space management. My data tablespace is segment space management = Manual.
My main question is
1) Should i need to increase freelist value (Right now my value is 1)
2) Or i have to move on segment space management = auto
SQL> select * from v$waitstat;
CLASS COUNT TIME
------------------ ---------- ----------
data block 2022 4052
sort block 0 0
save undo block 0 0
segment header 1 1
save undo header 0 0
free list 0 0
extent map 0 0
1st level bmb 0 0
2nd level bmb 0 0
3rd level bmb 0 0
bitmap block 0 0
bitmap index block 0 0
file header block 0 0
unused 0 0
system undo header 0 0
system undo block 0 0
undo header 6 0
undo block 0 0
18 rows selected.
we have 5 tempfile ( each of 65 gb ) allocated to TEMP tablespace...and still we are running in short of space..when i checked the TEMP segment usage, i am able to see much FREE blocks. how to release those space ?
TABLESPACE_N FILE_ID FILE_NAME Size(MB)
------------ ---------- ------------------------------------------- ----------
TEMP 1 +DATA/tedw/tempfile/temp.3043.727779755 65535.9688
TEMP 2 +DATA/tedw/tempfile/temp.3042.727779749 65535.9688
TEMP 3 +DATA/tedw/tempfile/temp.3041.727779741 65535.9688
TEMP 4 +DATA/tedw/tempfile/temp.4065.730387401 65535.9688
TEMP 5 +DATA/tedw/tempfile/temp.4075.731586241 65535.9688SELECT tablespace_name,
total_blocks,
used_blocks,
free_blocks,
total_blocks*16/1024 as total_MB,
used_blocks*16/1024 as used_MB,
free_blocks*16/1024 as free_MB
FROM v$sort_segment;
TABLESPACE_N TOTAL_BLOCKS USED_BLOCKS FREE_BLOCKS TOTAL_MB USED_MB FREE_MB
------------ ------------ ----------- ----------- ---------- ---------- ----------
TEMP 9994624 1007360 8987264 156166 15740 140426
1 row selected.
further when i checked the session details using TEMP segment, i got below output:
SELECT b.tablespace, b.segfile#, b.segblk#, b.blocks, a.sid, a.serial#,a.username, a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks;
TABLESPACE SEGFILE# SEGBLK# BLOCKS SID SERIAL# USERNAME OSUSER STATUS
------------------------------- ---------- ---------- ---------- ---------- ---------- ------------------------------ ------------------------------ --------
TEMP 15001 3549184 576 475 1237 EQUIPMENT infa ACTIVE
TEMP 15001 4002368 64 796 4677 CRM infa ACTIVE
TEMP 15002 580608 20352 868 615 EDW infa ACTIVE
TEMP 15002 3962112 832 92 1065 EDWSTG infa ACTIVE
TEMP 15002 4021120 576 1236 7257 EQUIPMENT infa ACTIVE
TEMP 15003 23936 64 819 5586 EDW infa ACTIVE
TEMP 15003 3798400 832 855 1801 EDWSTG infa ACTIVE
TEMP 15004 205056 21632 795 8171 EDW infa ACTIVE
TEMP 15004 4031488 832 403 1299 EDWSTG infa ACTIVE
TEMP 15004 4131456 576 19 6802 EQUIPMENT infa ACTIVE
TEMP 15005 3617856 832 1166 6204 EDWSTG infa ACTIVE
TEMP 15005 3741760 576 862 953 EQUIPMENT infa ACTIVE
TEMP 15005 4042752 18176 1226 5379 CDM infa ACTIVE
3 rows selected.
if i killed the SID - 1226, then those temp blocks ( 18176 blocks ) will be released and can other session use that space further ?
there is one more column - SEGBLK#
explain what is the exact meaning of this column ?
to reclaim the space, should i issue below command -
sql>alter tablespace TEMP coalesce;
how to repair the blocks that gets corrupted found in dbverify
View 8 Replies View Relatedtrying to get oracle-xe-universal_10.2.0.1-1.0_i386.deb rolling on Debian/6.0.1a which coincidentally is on another server, not on my desktop, so I obviously can't reach the web frontend at 127.0.0.1:8080. And I couldn't trick it with a simple TCP proxy on the server:
nc -l -p 80 127.0.0.1 8080
and then on the client:
telnet 192.168.0.252 80
bailed out with an error
invalid connection to [192.168.0.252] from (UNKNOWN) [192.168.0.1] 35798
So, is there a way to change the listening IP address, or to allow IPs other than the local host to connect? I've tried some googling around,.
i do know how change the database but can any one assist me how to chnage SID of database which in windows 2003 server?
View 1 Replies View RelatedJust to validate from you experts if in case I change the destination file of my archive logs, does it require a restart of the database?
View 10 Replies View Relatedour system admin is changing the time zone on solaris system, how can it effect my DB or as DBA what should i check before he do that and after he change the time zone. he is changing the time zone because of an issue with the DST (Daylightsaving time).
View 1 Replies View RelatedI am using oracle 11g in window server 2008 R2 enterprise under WORKGROUP. And it was working fine. But after changing it from WORKGROUP to DOMAIN not able to connect it.
Its comes as
ORA-12505:TNS:listener does not currently know of SID given in connect description.
While increasing the tablespace i am getting below error. How to handle this
SQL> set lin 300
SQL> col TABLESPACE_NAME for a25
SQL> col FILE_NAME for a65
SQL> select TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE,sum(BYTES/1024/1024) MB
2 from dba_data_files where TABLESPACE_NAME='SYSAUX' group by TABLESPACE_NAME,FILE_ID,FILE_NAME,AUTOEXTENSIBLE order by sum(BYTES/1024/1024) DESC,file_name;
TABLESPACE_NAME FILE_ID FILE_NAME AUT MB
------------------------- ---------- ----------------------------------------------------------------- --- ----------
SYSAUX 3 /ora2/oradata/dbname/sysaux_01.dbf NO 300
SQL> Alter database datafile 3 RESIZE 60000M;
Alter database datafile 3 RESIZE 60000M
*
ERROR at line 1: ORA-01144: File size (7680000 blocks) exceeds maximum of 4194303 block
I have scheduled a ADHOC Oracle JOB in OEM to gather statistics for the database with 100% estimation during weekend. This JOB currently fails because of a batch Job that runs at the same time and drops few old partitions. This OEM job runs for 1 hour and fails because few partitions are getting dropped at the same time.
How i can enable this statistics JOB to skip/Ignore errors and to continue to run to gather complete statistics of the entire database. I have scheduled this job in OEM. how can i enable this SKIP/IGNORE errors for this job.
I am interested about the fast way to access all data in physical block. what is the quick way to bring data blocks using the rowid, I found this script but soon as I can have faster access:
select * from table_name t
WHERE ROWID between 'AAAUaOAAEAAHkJiAAA' and 'AAAUaOAAEAAHkJiAA8';
where 'AAAUaOAAEAAHkJiAAA' is the last element in the block and 'AAAUaOAAEAAHkJiAA8' is the first one
my question is can retrieve all the data in one block more quick than this query.
We are having two data blocks as Earnings and Deductions. We need to export this to an excel in single sheet parallel [ imagine your payslip format ].
if we use normal text_io we are not able to get the result we want. so we have tried using a package called export2excel. we achieved what we want. The form is working perfectly in client server concept. When we move the same form to our Unix application server, it is not working.
I have created two non base table text items(from date, to date) and if values are not entered from front end i.e. null then hadrcoded in pre_query trigger to default values.But even i enter values dynamically from front end and when ever i press fetch button ,it is taking default values i.e. it is not accepting values what i have entered.
View 6 Replies View RelatedI have 4 blocks in my form which is basically used for travel booking for the employees in a company within India
1) Header block :- contains info abt the person who is booking the tickets for number of employees. Here i have given booking no a primary key.
2) Employee Detail :- Here the basic info of an employee is entered. Here i have taken booking number as a foreign key and then given emp_cd & booking number as a composite primary key.
3) Travel Detail :- Here the travel detail of individual employee will be entered wherein a unique trv_no will b generated 4 every single travel. Again i have taken foreign key as bkng_no frm 1st blck and emp_cd frm 2nd blck and tkn a composite primary key which comprises of bk_no,emp_cd and trv_no. this is used to maintain the uniqueness for single travel.
4) Vehicle Hotel Details :- This block is placed on different canvas for same form.It is meant for Other details in which details regarding hotel,vehicle etc booking aftr reaching the destination is entered. In this block thrs no primary key, but i hv taken the composite primary key of 3rd block as a foreign key since thr will be multiple entries for this one entire travel.
At every level there will be multiple entries for each corresponding entered record. I am able to enter one single record properly i.e. for 1 emp i am able to enter multiple travel details and his other requirements but asi try to enter more then one employee info, his travel details, other requirements i face an error stating foreign key constraint violated.Parent key not found for 3 rd level block.
How can i get the desired o/p wherein all the multiple records for every single subsequent record are stored correctly taking all the constraints in to consideration.
I have analyzed that, datapump estimation is 9.902GB. When i check size of .dmp file, it's shows 1.44Gb.
Export: Release 11.2.0.1.0 - Production on Fri Apr 5 02:00:05 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/******** dumpfile=expdp_LVGITRN_30_24_050413.dmp directory=DP_DIR logfile=expdp_LVGITRN_30_24_050413.log full=y exclude=statistics
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: [bold]9.902 GB [/bold]
Why Datapump estimate so much than actual size?
I want to change dbname,sid,data/control file locations in operational dataguard setup i plan to follow as below
1)shutdown primary and standby (stop managed recovery)
2)change db name in init.ora of primary and standby change database name control file location
3)create control file for primary from trace(script) make changes for db name and file locations
4)mount and open primary database
5)create standby control file
6) transfer standby control file to standby
7) mount stand by database and start manage recovery
If this steps will error free do i need to follow any thing additional to this or what is other best way for this or its not possible at all
Using Application Express 4.2.1.00.08
Any problem using 4.2's One Level Tabs - Content Frame page where you have a parent region (of type content frame Body Container ) while number of child regions (of type Hide & Show Region - Borderless ), if you want to re-order them by changing the sequence, it has no effect when you run the page.
The regions still show in the same order top to bottom as it did before the update of sequences. Heading links (to show/hide child regons in content frames) has the same ordering as before.
I have a table users which contains the column password and the datatype for this is raw(64) ,which will be the hashed one coming from the application. When Inserting the application is inserting with the lower case but while storing the table data is storing int the uppercase .Is it the expected behavior of this datatype. I am using Oracle 11g and attaching the ddl and dml pertaining to the column
CREATE TABLE users_new(USER_ID NUMBER(10), PASSWORD RAW(64) ); insert into USERS_NEWvalues(1,'ba660ed7b382f2c57754c4cd0d5289cc60ee455244847268d88b555168b28b23156f5a016b2764966a15674bec4eb0fb2220be736bfc21282be957910b9e80cf');
But after selecting from the database I am getting as below BA660ED7B382F2C57754C4CD0D5289CC60EE455244847268D88B555168B28B23156F5A016B2764966A15674BEC4EB0FB2220BE736BFC21282BE957910B9E80CF
We have an Oracle Server database of Size 50 GB having 10 GB Data. And Planning to have a new Database Server of 200GB . So my question is after moving all the 10 GB data to 200 GB Database Server, will the performance of the system come down? Will it reduce the speed?
View 9 Replies View RelatedI've 2 DB : One primary and One physical standby. I want to change SYS password on primary DB. Can I do it without any action on standby DB side ? If not, give me a step by step details to perform this action properly.
View 4 Replies View RelatedI'm using 2 databases : Primary and Standby DB. It working fine. I've just one question : I want to change SYS password on primary DB. Can I change it without any action on Standby DB?
View 8 Replies View RelatedI want to replace one redo log on the primary database. actually this redo log is on G:oracleoradata; I want to move it on F:oracleoradata. How to do that cause the same redo log is also on standby database?
View 3 Replies View RelatedYesterday only I have Installed Oracle 11G & created DataBase - JafferDB And from Oracle SQL Developer, I have created a connection called - JafferCon and SID also given..and the role is - SYSDBA And I excute the below statement
insert into MyTable1 Values ('AAA1', 'BBB1', 'CCC1')
insert into MyTable1 Values ('AAA2', 'BBB2', 'CCC2')
Then I checked by Select statement, it has shown the values....No Problem.... But, as a test, I deleted the connection and created a new connection tio the same DataBase with different name and when I checked by Select Statement....., it has not shown the values....?
The developer team wants to test their application. For the same they need some data which resides at oracle 8i instance. However they are not able to create a dblink between 11g and 8i as this is not supported by oracle.
Now they want the data to be refreshed from 8i to 11g by exp/imp.
Apart from exp and imp is there is any other alternatives?
RAW datatype in oracle,any problems we will face using this datatype,Reason is we have column which stores session id in RAW datatype using sys_guid, drawbacks in using this datatype.
View 5 Replies View RelatedI've changed the ip address of the enterprise manager grid control server. But oms could not be started. I got the error below:
[oracle@YDFULGRID bin]$ ./emctl start oms
Oracle Enterprise Manager 11g Release 1 Grid Control
Copyright (c) 1996, 2010 Oracle Corporation. All rights reserved.
Starting WebTier...
[code]...
When i checked the log:I am getting this error:
2012-09-13 16:23:56,602 [main] DEBUG oms.StatusOMSCmd processStatusOMS.155 - Using http port.
2012-09-13 16:23:56,602 [main] DEBUG oms.StatusOMSCmd processStatusOMS.195 - Attempting to connect to servername
2012-09-13 16:24:17,638 [main] DEBUG oms.StatusOMSCmd processStatusOMS.200 - page status code is 503
2012-09-13 16:24:17,638 [main] DEBUG oms.StatusOMSCmd processStatusOMS.202 - header is 3
2012-09-13 16:24:17,639 [main] INFO wls.OMSController statusOMS.950 - statusOMS finished with result: 3
Actually, i found that some forms if they are changed (like some buttons/text fields/code added to them) and this change can be saved and there is no errors in compilation. but when this form is run from the application, the change is not visible.
Even if the form with the image is removed from the directory, it still runs from the application link. Is there some memory issue or anything else.
Now i have one problem. I have two database oracle 10g and install on two server (call is A and B). I already create database link between two database. Each database has over 200 tables.
Basically structure two database is same, but database A have data real time, database B is stand by server. Now i want to synchronize all data of database A to database B. What should i do?
I need store history for two tables in my system. I thought that Flashback Data Archive will be the best option. There is also another ways to do this but don't focus on this. I need to to this by FDA (Flashback Data Archive);
So my prerequisite was to create tablespace and flash back archive, and alter table to be archived.
create tablespace audit_archive datafile 'd:oradata etaaudit_archive.ora' size 100M;
create flashback archive audit_flash_archive
tablespace audit_archive quota 10G retention 10 year;
alter table teta_admin.t_prac flashback archive audit_flash_archive;
and everything works fine but on sys user.
i can query this table using "as of timestamp" clause
select prac_id, imie, imie_2, nazwisko, nr_ew from teta_admin.t_prac as of timestamp to_timestamp('2011-08-23 08:20:00','yyyy-mm-dd hh24:mi:ss')
but final construction of idea was to create additional user (interface), grant select on teta_admin.t_prac object and query archive data from interface user. and this is point of my failure. this don't work on new user.
interface user have such sys privs:
SQL> SELECT * FROM dba_sys_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
INTERFACE CREATE SESSION NO
and table privs:
SQL> SELECT * FROM dba_tab_privs
2 WHERE grantee = 'INTERFACE';
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------------------------ ------------------------------ ------------------------------ ------------------------------ --------------------
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN INSERT
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN DELETE
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN ALTER
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN FLASHBACK
INTERFACE TETA_ADMIN T_PRAC TETA_ADMIN SELECT
what i need to do in order to query this flashback table from interface user. when i try to do this from this user oracle says ORA-00942.