juvenile Posted May 22, 2013 Report Posted May 22, 2013 OEM lo Segment advisor run chesaanu ... Daani data OEM nunchi teesukovali ante pichak fack autunnai .. very slow and only 10 records per page ... 1000+ entries ni capture cheyyali .. is there any other way
juvenile Posted May 22, 2013 Report Posted May 22, 2013 [quote name='AggiRamudu' timestamp='1369241296' post='1303783896'] OEM lo Segment advisor run chesaanu ... Daani data OEM nunchi teesukovali ante pichak fack autunnai .. very slow and only 10 records per page ... 1000+ entries ni capture cheyyali .. is there any other way [/quote] NVM .. i got it .. select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message from dba_advisor_findings af, dba_advisor_objects ao where ao.task_id = af.task_id and ao.object_id = af.object_id and ao.owner = 'AGGI';
Joey_Tribbiani Posted May 23, 2013 Report Posted May 23, 2013 Guys ... Oracle re-org ki and Shrink ki difference enti .. Botth do the same thing kada
IWillWin Posted May 29, 2013 Report Posted May 29, 2013 Bhayyas Golden Gate loo sahayam kaavali .. Below is the scenario A -- Production Server B -- Some other Server C -- ITL Server( I don't know what ITL is, but it is not Production server) [size=4]Last two years nunchi A loo Source database and B loo Target database vunaayi .... Golden Gate software A server loo vundi ..... Ippudu C source database kaavali and A Target database kaavali ... Is it possible to use the software in A or do we need to install new Golden gate software again???[/size] [size=4]Telisthey help cheyandi .. Thanks [/size]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [quote name='IWillWin' timestamp='1369871675' post='1303804005'] Bhayyas Golden Gate loo sahayam kaavali .. Below is the scenario A -- Production Server B -- Some other Server C -- ITL Server( I don't know what ITL is, but it is not Production server) [size=4]Last two years nunchi A loo Source database and B loo Target database vunaayi .... Golden Gate software A server loo vundi ..... Ippudu C source database kaavali and A Target database kaavali ... Is it possible to use the software in A or do we need to install new Golden gate software again???[/size] [size=4]Telisthey help cheyandi .. Thanks [/size] [/quote] LTT
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [b] [size=4]AWR Data Extract[/size][/b] [b] [size=4]By Brian Diehl on [url="https://blogs.oracle.com/priminout/entry/awr_data_export?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]May 28, 2013[/url][/size][/b] [color=#555555][font=Arial, Verdana, sans-serif][size=3] [size=4]The Oracle RDBMS has an excellent repository of performance data that is collected automatically. It's called Automatic Workload Repository, or AWR for short. The AWR is a snapshot-based collector of database information. This includes not only the information from V$SYSSTAT, but also snapshots of other configuration information like database parameter values. This information is invaluable in diagnosing issues at the database-level. AWR information is typically exchanged via the AWR reports in HTML format (created with [i]awrrpt.sql[/i]). While this can be useful, what would be better--and more flexible--is to have the raw data used to create the HTML report.[/size] [size=4]This is possible using the AWR extract and import utility. The scripts are available in the same rdbms/admin directory under the Oracle Home. The extraction script is [i]awrextr.sql[/i]. Run this script as a DBA user. First, you will see a list of available databases in the AWR repository. Unless you have imported previous AWR data from other databases, there should be only one DB ID listed. [/size] [size=4]Similar to the HTML report, the script will prompt for the number of days to display a list of snapshots. Choose the appropriate number of days that includes the data you want to export. [/size] [size=4]Enter value for num_days: 1 Listing the last day's Completed Snapshots DB Name Snap Id Snap Started ------------ --------- ------------------ ORCL 402 28 May 2013 09:06 403 28 May 2013 10:00 404 28 May 2013 11:00 405 28 May 2013 12:00 406 28 May 2013 13:00 407 28 May 2013 14:00 408 28 May 2013 15:00 409 28 May 2013 16:00 410 28 May 2013 17:00 411 28 May 2013 18:00 [/size] [size=4]Enter the start and end snapshot dates when prompted.[/size] [size=4]Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 403 Begin Snapshot Id specified: 403 Enter value for end_snap: 411 End Snapshot Id specified: 411 [/size] [size=4]Next, the script will display a list of available directories. The extraction process ultimately creates a dump file using Oracle Datapump. The resulting file will be in the directory you choose here. There is no default, but I usually choose the DATA_PUMP_DIR since it exists in instances by default.[/size] [size=4]Choose a Directory Name from the above list (case-sensitive). Enter value for directory_name: DATA_PUMP_DIR Using the dump directory: DATA_PUMP_DIR [/size] [size=4]Accept the default file name (format awrdat_<start_snap>_<end_snap>). What happens next is important to understand. A temporary schema is created and data from the core AWR tables is moved to copies in this schema. This schema is exported using datapump. The script will finish and give the location of the new dump file.[/size] [size=4] Using the dump file prefix: awrdat_403_411 | | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | The AWR extract dump file will be located | in the following directory/file: | /u01/app/oracle/admin/orcl/dpdump/ | awrdat_403_411.dmp | ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ | | *** AWR Extract Started ... | | This operation will take a few moments. The | progress of the AWR extract operation can be | monitored in the following directory/file: | /u01/app/oracle/admin/orcl/dpdump/ | awrdat_403_411.log | End of AWR Extract [/size] [size=4]The resulting dump file can be imported into another Oracle database using [i]awrload.sql[/i]. I will cover the import process in the next post. Because the AWR tables contains such an extensive collection of information about the database, the time required to diagnose database-related issues may be greatly reduced.[/size][/size][/font][/color]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [b] The MAGIC Questions[/b] [b] By Mike Dietrich on [url="https://blogs.oracle.com/UPGRADE/entry/the_magic_questions?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]May 27, 2013[/url][/b] [color=#555555][font=Arial, Verdana, sans-serif][size=3] Almost every week Roy, Carol and I receive one or more emails in the following style:[indent] [i]"Hey, we (or my customer) plan(s) an upgrade to Oracle 11g. We (or the customer) wants zero downtime. Currently we (or they) are on AIX with Oracle 10g (and someold 9i) databases. Can we get an advice please?"[/i][/indent] or another one here ...[indent] [i]"Upgrade from 8i to 11g. The customer's database is 28 TB (quite big!). Downtime is 5-6 hours. It's on AIX. And it's an it's an Oracle EBS database"[/i][/indent] Well, in both cases we lack a lot of useful information - or sometimes things are almost impossible or simply wishful thinking. So we have a collection of (we call them) The Magic Questions. Once those are answered upfront it is way easier to give a helpful advice.[img]https://blogs.oracle.com/UPGRADE/resource/IMAGES/2013_05_27_MagicQuestions.jpg[/img][list] [*]Will you exchange the hardware? [*]Will you change to a new OS version? [*]Will you change to an entire new OS architecture? [*]Will you change the database characterset? [*]Do you plan to consolidate (schema/database/...)? [*]Number of databases you plan to upgrade or migrate? [*]Size of database(s)? [*]Exact source and target Oracle versions? [*]Maximum allowed downtime per database? [*]Fallback requirements? [*]Test environment available? Testing tools? [*]Does a performance baseline exist? [*]Changes required to enable new features? [*]RAC/Grid Infrastructure already in use or planned? [/list] Once we get the answer and (even more helpful) a sheet describing the entire landscape in more detail we will be able to give some advice. -Mike https://blogs.oracle.com/UPGRADE/entry/the_magic_questions?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29[/size][/font][/color]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [color=#333333][font=Arial, sans-serif][background=rgb(245, 245, 245)]V$DIAG_DIR_EXT has infromation about trace files existed in diag dirs, it can be used to analyze how many of them generated there[/background][/font][/color]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [color=#333333][font=Arial, sans-serif][background=rgb(245, 245, 245)]Bet you did not know ... O R A C L E stands for One Real American Called Larry Ellison.[/background][/font][/color]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [b] [size=5]SLOB: PL/SQL Commit Optimization[/size][/b] [color=#555555][font=Arial, Tahoma, Verdana][size=2] [size=5]MAY 20, 2013 [url="http://flashdba.com/2013/05/20/slob-plsql-commit-optimization/#comments"]2 COMMENTS[/url][/size][/size][/font][/color][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5][url="http://flashdba.files.wordpress.com/2013/03/slob-ghost.png"][img]http://flashdba.files.wordpress.com/2013/03/slob-ghost.png?w=78&h=115[/img][/url]I ran some SLOB tests over the weekend using the new [url="http://kevinclosson.wordpress.com/2013/05/02/slob-2-a-significant-update-links-are-here/"]SLOBv2[/url] kit and noticed some interesting results. I was using SLOB to generate physical I/O but the “anomaly” is best demonstrated by putting SLOB in “Logical I/O mode”, i.e. by having a large enough buffer cache to satisfy all reads.[/size][/size][/font][/color][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]I’m calling SLOB with the following [url="http://flashdba.com/2013/05/20/slob-plsql-commit-optimization/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]configuration[/url] parameters and 32 worker processes:[/size][/size][/font][/color] [size=5]UPDATE_PCT=20 RUN_TIME=30000 WORK_LOOP=1000 SCALE=10000 WORK_UNIT=256 REDO_STRESS=HEAVY LOAD_PARALLEL_DEGREE=8 SHARED_DATA_MODULUS=0[/size][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]Notice the WORK_LOOP value is non-zero and the RUN_TIME is fairly large – I’m choosing to run a specific set of SLOBops rather than use elapsed time to define each [url="http://flashdba.com/2013/05/20/slob-plsql-commit-optimization/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]test[/url] length. With WORK_LOOP at 1,000 and 32 worker processes that should generate 32,000 SLOBops. Since UPDATE_PCT is 20% I would expect to see around (32,000 * 20%) = 6,400 update statements. So let’s have a look at a couple of interesting statistics in the AWR report generated from this run:[/size][/size][/font][/color] [size=5]Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- redo synch writes [b]97[/b] 2.0 0.0 user commits [b]6,400[/b] 134.5 1.0[/size][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]That’s exactly the number of [i]user commits[/i] we expected. But the number of [i]redo synch writes[/i] is interesting…[/size][/size][/font][/color] [b] [size=5]Redo Synch Writes[/size][/b] [color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]When a session places a commit record into the log buffer it posts the log writer process and then puts itself into a[i][url="http://flashdba.com/2013/05/20/slob-plsql-commit-optimization/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]log file[/url] sync[/i] wait until LGWR notifies it that the record has been written to persistent storage. Actually there are times when the session will not post LGWR (because it can see via a flag that LGWR is already [url="http://flashdba.com/2013/05/20/slob-plsql-commit-optimization/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]writing[/url]) but one thing it always does is increment the counter [b]redo synch writes[/b]. So in the above AWR output we would expect to see a matching number of redo synch writes to user commits… yet we don’t. Why?[/size][/size][/font][/color][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]There’s a little-known optimization in Oracle PL/SQL which means that Oracle will not always wait for the log buffer flush to [url="http://flashdba.com/2013/05/20/slob-plsql-commit-optimization/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+orana+%28OraNA%29#"]complete[/url], but will instead carry on processing – effectively sacrificing the D ([url="http://en.wikipedia.org/wiki/Durability_(database_systems)"]durability[/url]) of [url="http://en.wikipedia.org/wiki/ACID"]ACID compliance[/url]. This is best explained by [url="http://jonathanlewis.wordpress.com/"]Jonathan Lewis[/url] in [url="http://jonathanlewis.wordpress.com/oracle-core/oc-6-writing-and-recovery/"]Chapter 6[/url] of his excellent book [url="http://jonathanlewis.wordpress.com/oracle-core/"]Oracle Core[/url] – if you haven’t read it, consider putting it at the top of your reading list.[/size][/size][/font][/color][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]Because SLOB’s engine is a PL/SQL block containing a WHILE … LOOP, Oracle decides that the concept of durability can be rather loosely defined to be at the level of the PL/SQL block rather than the transactions being created within it. According to Jonathan, one way of persuading Oracle not to use this optimization is to use a database link; so let’s modify the slob.sql update statement to include the use of a loopback database link and see if the number of redo synch writes now rises to around 6,400:[/size][/size][/font][/color] [size=5]Statistic Total per Second per Trans -------------------------------- ------------------ -------------- ------------- redo synch writes [b]6,564[/b] 302.3 0.5 user commits [b]12,811[/b] 590.0 1.0[/size][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]Indeed it does… but now the number of user commits has doubled, presumably as the result of Oracle performing a two-phase commit (Oracle doesn’t know the loopback database link points to the same database so assumes the transactions are distributed).[/size][/size][/font][/color] [b] [size=5]Conclusion[/size][/b] [color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]I blogged this because I found it interesting, rather than because I had a point I was trying to prove. However, if there were to be any conclusions to this entry they would be the following:[/size][/size][/font][/color][list] [*][size=5]SLOB is a great tool for experimenting with the behaviour of Oracle under load[/size] [*][size=5]Jonathan’s Oracle Core book is essential reading for anyone who wants to understand Oracle to a deeper level[/size] [/list][color=#555555][font=Arial, Tahoma, Verdana][size=3] [size=5]It’s probably also worth keeping in mind that SLOB’s use of PL/SQL blocks may result in slightly different behaviour from the log writer than you might see from alternative load generation tools or applications which generate I/O.[/size][/size][/font][/color]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 evariki ayina e SLOB tests gurchi telusa vayya?
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [quote name='CBZ' timestamp='1369935417' post='1303806007'] nope... LKTT [/quote] 1 month avutundi edo kanukoni cheptav anna em ayindi
juvenile Posted May 30, 2013 Report Posted May 30, 2013 [color=#282828][font=helvetica, arial, sans-serif]Guys ...[/font][/color] [color=#282828][font=helvetica, arial, sans-serif]Oracle re-org ki and Shrink ki difference enti .. Botth do the same thing kada[/font][/color]
KOTC Posted May 30, 2013 Author Report Posted May 30, 2013 [quote name='AggiRamudu' timestamp='1369935833' post='1303806045'] [color=#282828][font=helvetica, arial, sans-serif]Guys ...[/font][/color] [color=#282828][font=helvetica, arial, sans-serif]Oracle re-org ki and Shrink ki difference enti .. Botth do the same thing kada[/font][/color] [/quote] [size=4][font=Tahoma, sans-serif]If a table with reclaimable space is not eligible for online segment shrink, or if you want to make changes to logical or physical attributes of the table while reclaiming space, you can use [/font][font=Tahoma, sans-serif][b]online table redefinition[/b][/font][font=Tahoma, sans-serif] as an alternative to segment shrink. Online redefinition is also referred to as [/font][font=Tahoma, sans-serif][b]reorganization[/b][/font][font=Tahoma, sans-serif]. Unlike online segment shrink, it requires extra disk space to be allocated. See [/font][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006754"]"Redefining Tables Online"[/url][font=Tahoma, sans-serif] for more information.[/font][/size] [size=4][b] Redefining Tables Online[/b] [font=Tahoma, sans-serif]In any database system, it is occasionally necessary to modify the logical or physical structure of a table to:[/font][/size][list] [*][size=4]Improve the performance of queries or DML[/size] [*][size=4]Accommodate [url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#"]application[/url] changes[/size] [*][size=4]Manage storage[/size] [/list] [size=4][font=Tahoma, sans-serif]Oracle Database provides a mechanism to make table structure modifications without significantly affecting the availability of the table. The mechanism is called [b]online table redefinition[/b]. Redefining tables online provides a substantial increase in availability compared to traditional methods of redefining tables.[/font] [font=Tahoma, sans-serif]When a table is redefined online, it is accessible to both queries and DML during much of the redefinition process. The table is locked in the exclusive mode only during a very small window that is independent of the size of the table and complexity of the redefinition, and that is completely transparent to users.[/font] [font=Tahoma, sans-serif]Online table redefinition requires an amount of free space that is approximately equivalent to the space used by the table being redefined. More space may be required if new columns are added.[/font] [font=Tahoma, sans-serif]You can perform online table redefinition with the Enterprise Manager Reorganize Objects wizard or with the DBMS_REDEFINITION package.[/font] [font=Tahoma, sans-serif][b]Note:To invoke the Reorganize Objects wizard:[/b][/font][/size][list=1] [*][size=4][font=Tahoma, sans-serif]On the Tables page of Enterprise Manager, click in the [b]Select[/b] column to select the table to redefine.[/font][/size] [*][size=4][font=Tahoma, sans-serif]In the Actions list, select [b]Reorganize[/b].[/font][/size] [*][size=4][font=Tahoma, sans-serif]Click [b]Go[/b].[/font][/size] [/list] [size=4][font=Tahoma, sans-serif]This section describes online redefinition with the DBMS_REDEFINITION package. It contains the [url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#"]following[/url] topics:[/font][/size][list] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006790"]Features of Online Table Redefinition[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006821"]Performing Online Redefinition with DBMS_REDEFINITION[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#CJAHIJAH"]Results of the Redefinition Process[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1106851"]Performing Intermediate Synchronization[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006856"]Aborting Online Table Redefinition and Cleaning Up After Errors[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1106919"]Restrictions for Online Redefinition of Tables[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#CJAHDGEC"]Online Redefinition of a Single Partition[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006859"]Online Table Redefinition Examples[/url] [*][url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm#i1006801"]Privileges Required for the DBMS_REDEFINITION Package[/url] [/list] [url="http://docs.oracle.com/cd/B28359_01/server.111/b28310/schema003.htm"]http://docs.oracle.c...0/schema003.htm[/url]
Recommended Posts