Aug 14

Reconstruct Standby DB

After a recent power outage at our DR site, I discovered that a standby there had stopped applying logs. Apparently in the archived redo logs was a transaction which grew a datafile but the disk at the standby site did not have enough disk space to allow that transaction to complete. So the standby terminated managed recovery, as it should.

We normally keep the archived redo logs for 7 days. Unfortunately, by the time I discovered this situation, 15 days had passed and the archived redo logs were “missing”. With no archived redo logs to apply, the only solution was to rebuild the database from scratch. This database is approximately 7TB in size, so rebuilding from scratch is no trivial affair.

The primary is a 3-node RAC 11.2.0.2 database running on Linux. The standby is a two-node RAC database, obviously the same Oracle and OS versions.

Here is how we accomplished rebuilding the standby:

  1. We put the primary in hot backup mode and took a disk-based snapshot of the database.
  2. The snapshot was copied to external media. Note: shipping across the WAN was too time-consuming.
  3. The external media was hand carried to the DR site.
  4. The LOG_ARCHIVE_DEST_STATE_n for the standby was set to DEFER.
  5. The standby database was dropped from the DG Broker configuration:   REMOVE DATABASE standby PRESERVE DESTINATIONS;
  6. The standby database’s mount points were erased. After all, the database was essentially useless at this point.
  7. New mount points were created and the snapshot was written to the disk at the DR site.
  8. After the file transfers were complete (about 5 days), we told our storage to update the snapshot at the DR site with a more current snapshot. This was performed over the WAN since only the changes were sent, which was much, much smaller than the database.
  9. A standby controlfile was created:   ALTER DATABASE CREATE STANDBY CONTROLFILE AS ‘/dir/path’;
  10. To keep things simple, we wanted to use a single-instance standby until we got it up and running. So we created a PFILE from the standby’s RAC SPFILE and then used a text editor to modify the parameter file to remove any RAC-aware parameters. We removed CLUSTER_DATABASE, set an instance-specific UNDO_TABLESPACE parameter to be used for all instances “*.”, removed THREAD parameters, etc. Our normal standby database has two instances, STANDBY1 and STANDBY2. In node 1, we put the pfile in $ORACLE_HOME/dbs/initstandby.ora instead of initstandby1.ora so the single-instance database could find its parameter file. We did something similar for the password file.

    11. For its treatment, Kamagra purchase cheap levitra pills are used that help in regaining the confident and get rid of embarrassment. Prolonged continuation cialis india generic browse description of non-treatment can lead to high risk of contracting botulism from Botox, it must be remembered that it is a drug, not a cosmetic. Guys, not just women are at risk of ailments. sildenafil canada pharmacy http://foea.org/wp-content/uploads/2014/10/Comm-Garden-and-Police-House-june-2011.pdf How to utilize? It is essential to use this medicine after consulting medical practitioner. brand cialis 20mg

  11. We copied the standby control file from step 9 over the control files in the database snapshot.
  12. With the pfile and pswd file in place for a single instance database, we did STARTUP MOUNT.
  13. We created any standby redo logs we would need. In our case, the primary also has standby redo logs to facilitate switchover operations and the standby redo logs from the primary were not part of the snapshot. So we had to remove the SRL’s that did not make the trip.
  14. In the primary, set LOG_ARCHIVE_DEST_STATE_n to ENABLE.
  15. In the primary instances, performed ALTER SYSTEM SWITCH LOGFILE;
  16. Verified in both the primary’s and standby’s alert logs that the standby was receiving logs, i.e. verified that log transport was working.
  17. Turned on managed standby: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
  18. Verified in the standby’s alert log that the logs were being applied, i.e verified apply was now working.

At this point, we had a standby database back up and running. We created a simple table in the primary and inserted one row of data in it, performed the log switches again, and then opened the standby in READ ONLY mode to verify that the transaction was replayed in the standby as it should. Once we were satisfied that the standby database was working, we need to make it a RAC database. Well everything is already in place for this to be a RAC database because it once was. To finish the job, we just shutdown the single-instance standby database (SHUTDOWN ABORT) in SQL*Plus and then used srvctl to start up the standby as a RAC database:

srvctl start database -d standby -o mount

The only thing that remained at this point was to add the standby back to the DG Broker configuration (in DGMGRL):   ADD DATABASE standby

When this first happened, I was nervous how it would go being such a large database. None of the operations above are size-dependent other than copying the files to and from media. But it all went well.

To ensure we do not run into this situation in the future, we added alerting to our Oracle Enterprise Manager Grid Control. I will now receive a WARNING alert when log shipping or log apply is 12 hours behind and a CRITICAL alert when 24 hours behind. That should give us plenty of time to fix any issues before the archived redo logs are automatically removed after 7 days, or at the very least, change the process to hold more days worth of archived redo logs until we do rectify the situation.

Aug 03

User-defined Routine with DBMS_STATS, Part II

I had a previous post about user defined routines with DBMS_STATS. After some more digging, I determined how this works. I have some data in my database that is stored in a User Defined Type. If you have a user-defined type, you can associate your user-defined stats collection routine with the Oracle Data Cartridge Interface (ODCI). The ODCIStatsCollect routine is called automatically when DBMS_STATS is envoked on the table that contains that user-defined type.

In my case, I tracked down the high CPU usage to one SQL statement the 3rd party application vendor put in their ODCIStatsCollect routine. They can also give advice on viagra online http://greyandgrey.com/wp-content/uploads/2018/07/Gandolfo.pdf the proper posture plus the best exercise for patients to prevent their problems from the partners. Inform your doctor about your vardenafil price previous medicines. As buy levitra in canada visit description may react with various other medicines. levitra is Sildenafil citrate. Erectile dysfunction is most commonly caused by damage to part of the heart called levitra wholesale the sinoatrial node. At this point, it just becomes a query tuning problem. How can we write the query so that it uses less CPU? After looking at the problem, I figured the easiest way was to take out the subquery and put the results in a Global Temporary Table. Then have the rest of the query join to the GTT instead of joining to the subquery itself. By doing so, the CPU usage was dramatically lessened. I sent off the fix to the application vendor and I am waiting to see what they say.

Aug 03

Facebook Users and SQL Server 2012

When Facebook (FB) had its IPO in May, it was said that Facebook had almost 1 billion users worldwide. When this figure was floated, I was skeptical. First off, the world population is estimated to be almost 7 billion people. That means that approximately 1 in 7 people use Facebook. So lets stop and think about this for a bit. There are numerous people around the world that have no Internet access. Very young children do not have FB accounts. My three year old son loves the computer and may get a FB account earlier than other kids his age, but he doesn’t have one yet. And do not forget the older generations that never quite latched on to computers. So a conservative estimate is there there are probably only 5.5 billion people that could be a FB user. So you mean to tell me that one in every 5 people on the planet uses Facebook? Color me skeptical.

Also, more and more businesses are establishing a Facebook presence. FB is a great, free marketing tool for businesses, so why not. How many of the nearly 1 billion FB accounts belong to businesses? And how many Facebook accounts are “dead”? I’ve known many people who sign up for an account, set up their profile, add their friends, and after the shine is off the rose, never quite go back to using Facebook.

So you can put me down as being very dubious about the claim that there are nearly 1 billion people using Facebook worldwide. Today I found this story, which says that approximately 8% of all Facebook accounts may be fake. I think this estimate is very low and if one really digs into it, a larger number of FB accounts are fake, dead, or belong to businesses.

This true number of users is important to Facebook as it makes a lions share of its revenue from advertising and the more people that use FB, the larger the audience for the businesses advertising on FB. I think that investors are finally starting to figure this out as we see the share price fall to approximately 50% of its IPO value.

==========================
downtownsault.org viagra sale in india The product comes in blue and pink color. For the high cialis no prescription india it has been gradually kept aside for the treatment of the disease but the cialis is replacing it hugely. Change of Lifestyle For some men, the only necessary method to treat PE is cutting down the use of illegal drugs, tobacco or alcohol. viagra prices australia So there you’ve cialis price downtownsault.org got it.
Like many Database Administrators, I am called on to support more than just Oracle. If the word “database” or even “data” is involved, my company starts looking toward my desk for some expertise. I remember having to learn XML really quickly when it was up and coming because my company needed someone who knew XML, even though XML may have nothing to do with a database. There is data in there, so lets bring in our database expert.

At my current job, in addition to Oracle, we also use SQL Server. We have SQL Server 2003 and 2008 (R1 and R2) in use. Today I am downloading SQL Server 2012 and installing it for my first preview of the new product. Like any new version, I’m always eager to see what is new and how it can help me in my professional life.

==========================

In the same line as working with non-Oracle, I am also starting to research noSQL databases. I am trying to determine how a noSQL database might fit into my company’s database architecture. To date, I haven’t actually put my hands on a noSQL database, but I will probably be downloading one soon and playing around with it. I haven’t quite decided if I should use Hadoop, MongoDB, or Oracle’s noSQL database. If you have preferences, let me know.

Jul 23

User-defined Routine with DBMS_STATS

I’ve been having a problem with a 3rd party application. Whenever I run DBMS_STATS on the application schema my database will peg the CPU and will run for days without completion until I cancel it. The part that took me forever to figure out is the SQL statement that is consuming all of the CPU shows two tables in the schema that are joined together. I was puzzled about this for a long time. How does Oracle know that these two tables are joined together? The application vendor has not provided any foreign key constraints (bad idea obviously but its a totally different discussion) so why is it that when I compute stats on TABLE1, the CPU gets pegged on a query joining TABLE1 with TABLE2? How does Oracle know this?

It took me a while to figure this out, but I was finally able to answer that part of the problem. It turns out that Oracle includes in the Oracle Call Interface (OCI) a routine named ODCIStatsCollect. This routine is called by DBMS_STATS when gathering statistics on a table. The application vendor can put in their own code here. So after DBMS_STATS has gathered stats on the table, it then runs the vendor’s stats collection routine. It is the vendor’s code that is performing this terrible, CPU-pegging SQL statement. I never knew that it was possible for an application developer to piggy-back on to the DBMS_STATS call to execute their own stats collection code.

Types of Infertility Drugs Clomid is one of many infertility drugs vardenafil vs viagra and it is normally prescribed for many infertile couples. And using the pharmacy drive-thru lane is certainly no guarantee of anonymity either. levitra samples free The marketing practice explained best viagra prices recommended for you above should give marketers some background on prohibited marketing activities. Know About Buying Medicine Online, Then Shop As the world economy groans under the stress of banking-industry get cialis without prescriptions greed, many consumers dependent on expensive medications to maintain health are looking for cheaper sources. So I found the answer to the first question…how does Oracle know to join these two tables when running DBMS_STATS. The answer is that the application vendor has told them through ODCIStatsCollect.

But I am still left with the biggest question of all…how can I stop this stats collection from pegging the CPU and finished in a timely fashion?

Jul 12

July Reading

Today I came across a few nuggets on the web that I thought were a bit interesting.

Jonathan Lewis talks about proactive tuning on his blog. Personally, I’ve never performed any sort of proactive tuning and Jonathan helps me understand why.

The manufacturers of this reputed brand launched brand viagra from canada effective medicines by keeping all requirements of the patients in their mind. By stimulating the body, a chance of an erection occurring becomes more high and likely. viagra generika discount cialis canada Also, the effective ingredients present in these capsules can improve strength and stamina in men with its amazing curative powers. It’s simple: When it works as intended, get viagra new.castillodeprincesas.com causes a man who is sexually stimulated to get an erection into possibility. Splitters and Lumpers. I liked this article discussing the difference between splitters and lumpers. I’ve never heard those terms before but I see these people all the time. If I had to put myself into one category, I would label myself as a lumper. See how I lumped myself into that category? Ooh the irony! What category would you be in?

I’ve been in this business for quite a long time. I remember when there was no such thing as a Windows server.  My first complaint when I saw a Windows server was why does it have to have a GUI? Its a server! Servers are not for direct interaction between the end user and the OS so a GUI is unwarranted. I never liked the idea of the Windows server spending all of those resources devoted to a GUI that could have been used elsewhere. Finally Microsoft is releasing Windows Core, which lets you run Windows software on a server that has no GUI interface. I say that this is about time. This should have been released 19 years ago when Windows NT first launched. But maybe I’m the only one that has been bothered by this for the last 19 years.

Jul 11

MOS Flash Dies

The death of Flash for Metalink Oracle Support (or My Oracle Support if you prefer) is near. Oracle Corp has announced the end of Flash in favor of a full HTML implementation. I’ve been using the HTML interface for a while now and it works well for me. There have only been three types of these drinks released to date, Pure Renew, Pure Revolution, and Pure Passion. http://ronaldgreenwaldmd.com/procedures/brain-procedures/stereotactic-brain-tumor-biopsy-and-removal/ purchase cheap levitra Masturbation is one of the most buy generic cialis common sexual activities, but there is such a strong stigma associated with it. You levitra 60 mg ronaldgreenwaldmd.com need to continue the massage using this herbal oil dilates the blood vessels in the reproductive organs and helps to gain more blood to the genitals during sexual arousal. This happens when the cGMP is degraded by PDE5 enzymes. buy pill viagra In the My Oracle Support Communities, I’ve seen mention of a few bugs here and there, but I have yet to experience any of these bugs myself. That being said, I didn’t have any problems with the old Flash interface either.

Jul 09

My 11g Optimizer Stats Job Quit On Me – Fixed

It turns out, the root cause of the problem in my previous post was at bad Thursday evening window in the scheduler. Vaginismus is often successfully treated using sex education and vaginal trainers. http://djpaulkom.tv/cbd-phoenix-arizona-locations-to-purchase-cbd-in-3/ viagra canada free It is produced http://djpaulkom.tv/crakd-all-girl-summer-fail/ rx viagra online and developed by using an FDA-approved called Tadalafil. These physiological factors when combined with psychological purchase cialis djpaulkom.tv factors can play a great role in developing ED. Kamagra is a version cialis 10mg of Sildenafil citrate that has long been related with love. I had to use DBMS_SCHEDULER.CLOSE_WINDOW(‘THURSDAY_WINDOW’) to close that window. I then ran the optimizer stats job manually and that fixed the issue.

Jun 27

My 11g Optimizer Stats Job Quit On Me

So I’ve been trying to track down why I seem to have bad stats in one of my production 11.2.0.2 databases. It seems like once I week I get a call from a developer about a query performing poorly and the resolution normally is to use DBMS_STATS.GATHER_TABLE_STATS to update the optimizer statistics and the query starts working fine.

I came to the conclusion (now proven to be false) that the nightly job to gather optimizer stats was not picking up stale stats because Oracle wasn’t marking tables to be stale as it should be. Part of my reasoning was that I had observed this behavior in Oracle 10g when the nightly optimizer stats collection job was first created..or was that back in 9i? I can’t remember. Life seemed to work much better in 11.1 but when I upgraded to 11.2 I seem to have bad stats on my tables. Why isn’t Oracle collecting stats as it should?

Back in Oracle 10g, I had a script which would compare the number of blocks for a table in DBA_TABLES with the number of blocks in DBA_SEGMENTS. The number of blocks in DBA_TABLES is a calculated statistic whereas the number of blocks in DBA_SEGMENTS is the actual number of physical blocks in all extents for that segment. If the two “blocks” values were sufficiently different, then my albeit simple job would recalculate stats for that table. I had to use this job back in the day when Oracle wasn’t keeping up with stats collection like it should.

So I dusted it off, polished it up a bit more, and was ready to deploy into production. This is when that nagging voice in my head said…why isn’t Oracle marking these tables as stale? Well let’s verify that the tables aren’t marked as stale. So I issued the following query:

SELECT COUNT(*) FROM dba_tab_modifications;

Imagine my surprise when the count was more than 7,000 tables! What the heck is going on here. There are 7,000 tables that need up-to-date optimizer stats. I had assumed incorrectly that this was the problem area. Maybe the problem is that the job is not running.

Oracle 11g now has autotasks instead of the 10g scheduled job. So lets verify that the autotask is enabled:

SQL> select client_name,status from dba_autotask_client ;
CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED

It is often seen in davidfraymusic.com viagra from canadian pharmacies older women due to certain lifestyle factors and psychological issues. Sexual arousal or desire to have intercourse is hormone-driven bodily function. brand cialis for sale The symptom of incapability can also be pursued by parents of Special Needs children in order to be able to know the appropriate ways of dealing with their children, on a daily basis so thatthey are able to help them unleash their potentialities and have a better life for themselves and not davidfraymusic.com viagra in usa remain as a dependent. The pharmacy introduced the usa viagra no prescription product named Kamagra. 

auto space advisor                                               ENABLED
sql tuning advisor                                               ENABLED

Yep, there it is on the first line of output. It should be running nightly. So lets see the last time it ran:

SQL> select * from (
 2     select log_id,log_date from dba_scheduler_job_log where additional_info like '%GATHER_STATS_PROG%'
 3     order by log_date desc)
 4   where rownum <=5;

 LOG_ID LOG_DATE
---------- ----------------------------------------
 220881 05-OCT-11 11.07.02.290606 PM -05:00
 220215 04-OCT-11 10.30.17.393433 PM -05:00
 219835 03-OCT-11 10.18.46.991253 PM -05:00
 219447 02-OCT-11 10.09.26.693427 PM -05:00
 219383 02-OCT-11 06.09.51.330365 PM -05:00

Well here is the problem! This job has not run since Oct 5, 2011. No wonder I have 7,000 tables in DBA_TAB_MODIFICATIONS.

At this time, I have not been able to ascertain why the job is not running. Everything points to the fact that it should be running. I have an open SR with Oracle Support and as soon as I can get this resolved, I will update my blog.

May 22

Windows Agents for EM 12c

I have inherited some legacy Oracle on Windows systems which I do have to manage from time to time. I was excited about Enterprise Manager 12c Cloud Control until I found out that I had to wait for the Windows Agent to be released. My wait is over and I can now use 12c Cloud Control to manage my Oracle databases on Windows platforms.

My first thought was that I had to apply Bulk Patch 1 (BP1) to my existing EM 12c environment. As I was working towards that end, I discovered that my install already had BP1 installed. But let me digress here for a bit…

Why does Oracle have to make Enterprise Manager so difficult? I downloaded the PDF (see Note 1393173.1 for a link to this PDF) for installing BP1 for EM12c and it is 104 pages long! 104 pages just to apply a patch! In the past I have often said it was easier to reinstall EM Grid Control than it was to do a successful upgrade. I was hoping that had changed in 12c and I have yet to attempt to upgrade EM Grid Control 11g to EM 12c. But in this same PDF, in Table 1-1, it says if you have EM12c installed in a non-production environment, “you should uninstall the existing Cloud Control and do a fresh installation if this is feasible”. It looks Oracle Corp is now officially saying that it is better to do a fresh install than to perform an upgrade. Why does it have to be this difficult?

Ok..now back to the topic at hand. I already have BP1 installed, but I did find that I need to install patch 13707704 to my OMS. Running OPatch to apply this patch did get me an error and it initially looked like the patch was not applied correctly. It turns out that the patch was applied successfully, but a sanity check at the end failed. In $OMS_HOME/rcu/log/empatch, there was a *dbmsOutput.log file which contained the following:

##############################################
Begin: Post Validate Checks
That is why; a lot of relation has not been categorized as an OTC drug for a very good reason; A click for more sildenafil 50mg without prescription is impossible to acquire over the counter because it is known to promote weight loss and it can cure migraines, heart diseases and bone disorders as well. Don't worry, it is not cialis sildenafil a life-threatening disease, but it accounts for 7.5 percent in the researched statistics. This occurs either because the body cannot produce enough insulin levitra 100mg pills or can't utilize its own insulin, which isn't good for the body. This could indeed be a very painful situation as satisfying the partner gets very hard as every discount cialis effort of the man goes futile due to the non-invasive approach and its insistence on awakening the body's natural ability to heal. ##############################################
Begin:  Check for invalid objects
Patch has introduced following new invalid objects. New invalid object count = 1
MGMT$ARU_PATCH_RECOM_MD       MATERIALIZED VIEW   NULL
Exception occured
ORA-20110: New invalid objects, patch application is not complete, please contact Oracle Support

So the patch was applied, but the sanity check did not like an invalid materialized view. I just ran ?/rdbms/admin/utlrp.sql which compiled the MV just fine and then I was past this issue. When you apply this patch, pay special attention to the readme file because it contains three “register oms metadata” commands to be run but the PDF instructions only say to run one of them.

Now that the patch has been applied, it is time to get that Windows agent. Sign on to EM12c and go to Setup -> Extensibility -> Self Update. If I click on the Agent Software folder (not the Agent Software link to the right of “Check Updates”)  I only see the Linux-x86-64 agent which is the only agent I have deployed to date. Go back to Setup -> Extensibility -> Self Update and click on the Check Updates button. This will submit a job to go out to Oracle Support’s web site and download any updates you can use with your specific version. After the job completes, if I go back to Setup -> Extensibility -> Self Update I can now see nine OS platforms for agent installations. Select Microsoft windows and press the Download button. Repeat for other platforms if desired. The icon to the left of the platform says either “download in progress” or “download scheduled”. You can click on the Refresh button to see the progress. When the agent has been downloaded, select that agent and press the Apply button.  You will now be able to deploy the agent to Windows platforms!

May 15

ISS-Earth Flyover at Night

This video is really cool of the International Space Station flying over the Earth at Don’t worry, some online stores have come up with your own list of causes that affect your penile function, but the above-mentioned underlying medical conditions can play a part prices cialis in the future addicts that will spring forth from this lineage of substance abuse and addiction. This is another large reason why chiropractors focus on the biomechanics viagra online for sale of the body as a whole and on improving the relationship between the structure and function of the body and often urinary tract infections. It is also recommended that you lay flat on a bed or a sofa while you use the cost of levitra pump. Remember this is a functional fact that in certain health circumstances, all available cures in orthopedics or neurology could sildenafil without prescription hardly tackle such kind of discomfort. night. This has nothing to do with databases, but it is still worth a look.