DBA Blogs
Blogrotate #5: The Weekly Roundup of News for System Administrators
Hi all, and welcome back to blogrotate. It’s been a busy week here at Pythian which reduced the amount of time I had for cruising the news, so this weeks edition will be a short one. Here’s a few of the stories that tweaked our interest this week.
Operating Systems
The Machine SID Duplication Myth is an article on the Microsoft Technet blog by Mark Russinovich. It goes through an in depth explanation of what SID’s are used for, and notes that Sysinternals has officially retired the NewSID utility as of Nov 03, 2009. This is of particular interest to anyone who created desktops and laptops via saved images as NewSID was a staple utility after the machine was imaged to ensure it did not conflict with other machines on the network.
Michael Larabel published some CentOS 5.4 vs. OpenSuSE 11.2 vs. Ubuntu 9.10 Benchmarks on the Phoronix site. I was suprised to see that CentOS beat the others on the majority of tests run, at least in part due to issues with the ext4 filesystem that both SuSE and Ubuntu use as their defaults.
Over at the Computerworld blog, Steven J. Vaughan-Nichols writes about 5 Reasons why Ubuntu 9.10 is better than Windows 7. I agree with most of what he says, but generally Linux is still not an easy conversion for a die hard Windows user. It sure did spark a huge amount of debate in the commentary.
Mandriva Linux 2010 is out. Check out the release information and feature set at the Mandriva blog site. I’ll have a closer look at this if I ever get the time.
HardwareIn the world of hard drives the trend has always been to bump up the amount of bits the drive can hold to combat the constant increase in data size. Another way of dealing with this could be deduplication of data which should reduce the amount of storage required for the same information. Could a hard drive dedupe data has more on this subject.
SecurityRyan Paul writes HTTPS, SSL attack vector discovered; fix is on the way. This vulnerability was discovered by Marsh Ray and Steve Dispensa from security company PhoneFactor but not publicized pending a fix. There is a temporary workaround from the OpenSSL team, hopefully it’ll be resolved quickly.
Not long after Windows 7 was released, John Leydon at The Register writes that Naked Win 7 still vulnerable to most viruses. He’s reporting on testing done by the Sophos security firm which showed that 7 out of 10 of the malware tested still managed to run in the default configuration. So even if you upgraded to Windows 7 you still need to run that anti virus.
Even Linux is not safe from security threats (nothing ever is IMHO). Bug in latest Linux gives untrusted users root access by Dan Goodin gives you the details. Patches for RedHat linux are already out, keep your system up to date to make sure you get the patch as soon as it’s available.
VirtualizationRed Hat takes on VMware with server virtualization solution by Ryan Paul discusses RedHat’s newest foray into the virtualization market with their solution called Enterprise Virtualization for Servers. This solution uses RedHat’s recently acquired KVM and is prominent in the recent RedHat Enterprise Linux 5.4 release.
So as not to be left out, Cisco, EMC, and VMware join hands and plunge into cloud with their new joint venture called Acadia. You can also read more about this in Cisco, EMC, VMware & Intel Form Acadia.
In the “I totally called it” departmentI mentioned in a recent version of this blog that Microsoft was backing a Family Guy episode. I said at the time that I did not see how they could funny it up, apparently Microsoft could not see it either. Joe Fay gives us the skinny in Microsoft drops Family Guy like a hot deaf guy joke. Apparently the humour was not in keeping with the clean, family friendly image that Microsoft wants to convey. Seriously? I suggest someone at Microsoft watch any of the Seth McFarlane shows before signing on with him. I am guessing it was not a matter of foul language however, expletives are an occupational hazard when using Windows.
Til next time, keep your cache full and your swap empty.
Brad
Questions you always wanted to ask about Flashback Database…
Last Friday in the Sydney Oracle Meetup I talked about Oracle Flashback technology and how it helps to reduce downtime. The session generated great interest among the attendees, which led to interesting discussions and many questions about the subject.
Some of the question couldn’t be answered during the meeting so I’ve followed up on them and I’m posting the answers here since they may be of interest for many others.
Q: Is there a separate background process for writing flashback logs?A: Yes. RVWR (Recovery Writer, a.k.a Flashback Writer) was introduced in Oracle 10g to write flashback data from the Flashback Buffer in the SGA to the flashback database logs on disk.
Q: Do I need to shutdown and mount the database to turn flashback on?A: NO! This is a very common misconception. ALTER DATABASE FLASHBACK ON is an online operation.
To turn flashback on, all Oracle needs to do is to start saving the before-images of the database blocks before they are changed. This guarantees that the database can be flashbacked to the exact point-in-time when the flashback mode was turned on.
Q: What happens if RVWR cannot write to disk?A: It depends on the context where the write error occurs:
- If there’s a Guaranteed Restore Point, the database crashes to ensure the restore point guarantee is not voided.
- If there isn’t a Guaranteed Restore Point and it’s a primary database, the Flashback Mode will be automatically turned off for the database, which will continued to operate normally.
- If there isn’t a Guaranteed Restore Point and it’s a standby database, the database will hang until the cause of the write failure is fixed.
A: Yes, but indirectly. The size of the Flashback Buffer is set to 2 * LOG_BUFFER.
For performance reasons, it’s recommended to set LOG_BUFFER to at least 8MB for
large databases running in Flashback Mode.
A: No. Flashback Logs are not backed up. Even if the command BACKUP RECOVERY AREA is used to backup the contents of the FRA to tape only the following file types are backed up: full and incremental backup sets, control file autobackups, datafile copies, and archived redo logs.
Flashback Logs are considered to be transient files and cannot be backed up by RMAN. They are not needed for media recovery.
Q: When are the flashback logs deleted?A: Flashback logs are managed by Oracle only. Oracle will try to keep as much Flashback logs as needed to satisfy the DB_FLASHBACK_RETENTION_TARGET parameter. However, if there’s space pressure in the Flash Recovery Area (FRA), flashback logs may be deleted to make room for other things, like backups and archived logs, for example.
- If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.
- If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then a flashback log is reused.
- If the database must create a new flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.
- If the fast recovery area is full, then an archived redo log that is reclaimable according to the FRA rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that would require the use of that redo log file for the use of
FLASHBACK DATABASEare also deleted. - No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. Thus, retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely.
Other than that flashback logs are deleted according to the below:
- When flashback mode is turned off all flashback logs are deleted ONLY if there’s no guaranteed restore points. If there’s at least one guaranteed restore point, no flashback logs are deleted.
- When the oldest guaranteed restore point is deleted and flashback mode is off, all flashback logs older than the second oldest guaranteed restore point are deleted. If flashback mode is on for the database OR the guaranteed restore point is not the oldest no flashback logs are deleted.
A: You can use either the RC_RESTORE_POINT view in the recovery catalog or the command
LIST RESTORE POINT [ALL|restore_point_name] in RMAN.
RESETLOGS operation is it possible to flash forward to the incarnation after the RESETLOGS?
A: Yes, it’s perfectly possible.
Q: Can you see the progress of aFLASHBACK DATABASE operation?
A: Yes, you can. During a FLASHBACK DATABASE operation you can query V$SESSION_LONGOPS from another session to see the progress of the flashback.
The FLASHBACK DATABASE operation has two distinct phases: the actual flashback and the media recovery that happens afterwards to bring the database to a consistent state.
While the actual flashback is running you’ll see the following message in V$SESSION_LONGOPS:
Flashback Database: Flashback Data Applied : 238 out of 282 Megabytes done
During the media recovery, the following messages will be seen:
Media Recovery: Redo Applied : 263 out of 0 Megabytes done
Media Recovery: Average Apply Rate : 1164 out of 0 KB/sec done
Media Recovery: Last Applied Redo : 626540 out of 0 SCN+Time done
Media Recovery: Elapsed Time : 232 out of 0 Seconds done
Media Recovery: Active Time : 116 out of 0 Seconds done
Media Recovery: Active Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Maximum Apply Rate : 1859 out of 0 KB/sec done
Media Recovery: Log Files : 15 out of 0 Files done
Media Recovery: Apply Time per Log : 7 out of 0 Seconds done
A: Oracle’s recommendations are:
- Use a fast file system for your flash recovery area, preferably without operating system file caching. It is recommended to use a file system that avoids operating system file caching, such as ASM.
- Configure enough disk spindles for the file system that will hold the flash recovery area. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively.
- If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
- For large, production databases, set the init.ora parameter
LOG_BUFFERto be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.
You CAN execute DTS packages from SQL Server 2005 64-bit
All over the web I’d read that DTS packages could be stored on SQL Server 2005 64-bit, but not executed on this server. Workarounds I’ve seen range from creating SSIS packages with Execute DTS tasks, migrations to SSIS using the wizard or third party tools, and running the DTS Packages from a 32-bit server against the 64-bit target.
Recently (and much to my embarrassment after making that statement), a colleague demonstrated that this is not correct. DTSRun.exe can be found on SQL Server 2005 64-bit installations (although it might not be supported by Microsoft).
Upon investigating and testing on my own, I found that on the servers I checked, DTSrun.exe is located in: C:\Program Files (x86)\Microsoft SQL Server\80\Tools\Binn\.
I mocked up a simple DTS package that picks up a flat file, creates a table, and then imports the data into it with a transformation step. I saved it as a structured storage file, which I copied to the 64-bit server, generated a DTS Run line (F.Y.I. – DTSRunUI.exe is not located on these servers), then executed it from a command prompt on the 64-bit server. And it worked!
SSIS is a good platform with many noticeable improvements over DTS (also some new quirks), but if you need a workaround or just can’t justify a complete re-write of your DTS packages to your manager, this may be an option for you.
I tested some simple commands, (not the entire functionality) so I strongly recommend you test your packages before relying on this in a production environment.
Please note that DTSRun.exe is not included in SQL Server 2008 installations.
Log Buffer #168: a Carnival of the Vanities for DBAs
This is the 168th edition of Log Buffer, the weekly review of database blogs. Let’s give the wheel a spin and see who comes first . . .
MySQLBrian “Krow” Aker has something to say about Drizzle, InfiniDB, and column-oriented storage: “I have been asked a number of times ‘do you think there is a need for a column oriented database in the open source world?’ The answer has been yes! . . . I was very happy to see Calpont do their release of Infinidb last week.”
Vadim of the MySQL Performance Blog said, “As Calpont announced availability of InfiniDB I surely couldn’t miss a chance to compare it with previously tested databases in the same environment.” And he didn’t, as shows his post Air traffic queries in InfiniDB: early alpha. Bob Dempsey and Jim Tommaney of InfiniDB are in on the discussion.
Back to Drizzle for a moment, and Jay Pipes’ item, The Great Escape. “This week, I am working on putting together test cases which validate the Drizzle transaction log’s handling of BLOB columns. . . . I ran into an interesting set of problems and am wondering how to go about handling them. Perhaps the LazyWeb will have some solutions. . . . The problem, in short, is inconsistency in the way that the NUL character is escaped (or not escaped) in both the MySQL/Drizzle protocol and the MySQL/Drizzle client tools.”
Baron Schwartz has been catching erroneous queries, without MySQL proxy, having been inspired by Chris Calender’s post, Capturing Erroneous Queries with MySQL Proxy.
Nick Goodman promises instant relief from slow MySQL reporting queries using dynamoDB. And no gooey applicator!
Robert Hodges of the Scale-Out Blog looks at replicating from MySQL to Drizzle and beyond. “I am . . . delighted that Marcus Erikkson has published a patch to Tungsten that allows replication from MySQL to Drizzle. He’s also working on implementing Drizzle-to-Drizzle support, which will be very exciting. . . . This brings up a question–what about replicating from MySQL to PostgreSQL? What about other databases?”
And back to xaprb, where Baron confesses, I’m a Postgres user, as it turns out.
PostgreSQLBruce Momjian has published a new security talk, Securing PostgreSQL From External Attack.
Andrew Dunstan delves into recursion in Recursion, n. See recursion. “Never,” he says, “underestimate the usefulness of silly demos (this is written for a talk next week) to teach things worth knowing.”
Bernd Helmle shares a walk-through of cloning Slony nodes. “The new stable branch 2.0 of Slony-I is out for a while now. Time to blog about one of my favorite new features there, cloning an existing node without doing an initial SUBSCRIBE command.”
SQL ServerIt was PASS Summit this week. On Home of the Scary DBA, Grant Fritchey covers the event with several good posts, including PASS Summit 2009 Key Note 3. (Grant is also Geek of the Week! Congratulations, Grant! I guess. Quote: “I think most DBA’s have adminhood thrust upon them. I think the ‘accidental’ DBA is the most prevalent path into becoming a DBA. I became a full time Admin by opening my mouth once too often.”)
Aaron Bertrand also has his summary Blogging from the PASS Keynote: 2009-11-03. (Grant and Aaron both have to specify which keynote they mean, because there’s more than one keynote. This, I guess, is “keynote redundancy”, but I still think PASS needs to normalize.)
Greg Low announces the launch at the PASS Summit of a new book, SQL Server MVP Deep Dives. “This is no ordinary book,” he writes. “Paul Nielsen took up Steve Ballmer’s challenge at a recent MVP summit to do something notable to give back to the community. He organised a large group of SQL Server MVPs to create a unique book and worked with Manning to get it published. The money made on the book was to go directly to a charity and the charity chosen was WarChild.”
Ben Nevarez asks, Are You Using Scalable Shared Databases? “Did you know that you can share read-only databases between several instances of SQL Server? . . . Scalable Shared Databases is a very interesting SQL Server feature that many of us seem to almost have forgotten about . . . ”
Here’s Roman Rehak reporting an issue with restoring 2000 backups on 2008. He writes, “Recently we’ve been experiencing a lot of headaches with SQL Server 2008 crashing while restoring a backup taken on a SQL Server 2000 production server. The crash resulted in a stack dump but SQL Server would continue running, although less stable, and sooner or later needed a reboot.”
Meanwhile, Adam Machanic reports on SQL Server 2008: lock escalation, INSERTs, and a potential bug. Adam says, “Lock escalation is a funny thing. I’ve found myself on numerous occasions waging war against its concurrency-sapping existence, and rarely have I found myself wishing that it would work more aggressively. But there is a time and place for everything, and yesterday I discovered that a major change has occurred with regard to lock escalation in SQL Server 2008.”
OracleMohammed Mawla on the Pythian Blog bridges the gap with his item on running the same query against multiple SQL Server AND Oracle instances.
Surachart Opun shares his HOWTO on using DUPLICATE without a connection to target database: “ . . . that’s a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.”
Here’s another HOWTO, this one from the great grandson of Husnu Sensoy: How to Install Oracle 11g Release 2 on OEL 5.4 on VirtualBox: Installing Grid Infrastructure. He begins, “In Oracle 11g Release 2 you will find that things have changed even for single instance database installation. I will try to illustrate in this series of posts how to install a single instance Oracle 11g Release 2 database to your Linux machines.”
But let’s step back a bit. Ronny Egners says, Oracle on linux – yes of course – but what linux?. “There is a discussion from December 2008 what Linux (SLES vs. Red vHat vs. Oracle Enterprise Linux) to use for running oracle on Linux by Yann Neuhaus. . . . After nearly one year i wanted to catch up the article and check if the pros and cons are still valid or if there changed anything.”
Chen Shapira offers The Senile DBA Guide to Troubleshooting Sudden Growth in Redo Generation, which begins, “I just troubleshooted a server where the amounts of redo generated suddenly exploded to the point of running out of disk space. . . . The problem was found and the storage manager pacified, I decided to save the queries I used. . . . It was very embarrassing to discover that I actually have 4 similar but not identical scripts . . . Now I have 5.”
Embarrassing, Chen! But do you have guilty feelings like Martin Widlake does? He makes a guilty confession. The sin? “I use the Buffer Cache Hit Ratio.”
Last, Tyler Muth introduces Logger, A PL/SQL Logging and Debugging Utility.
That is all for now. Please let’s hear your favourite database blogs in the comments. Until next time!
Sqlnet Listener :- status READY, has 0 handler(s) for this service...
Listener was running, service was READY, but there were 0 handlers for the service.
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xxx)(PORT=1560)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yyy)(PORT=1560)))
Service "TEST" has 1 instance(s).
Instance "zzz", status READY, has 0 handler(s) for this service...
According to Metalink note 885431.1 we can avoid this issue if our listeners use only one end-point. We need to listen on the VIP address, but don't need to listen on the HOST address.
E.g change
LISTENER_XXX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myvip)(PORT = 1560)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1560)(IP = FIRST))
)
)
To
LISTENER_XXX =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myvip)(PORT = 1562)(IP = FIRST))
)
)
Manageability By Design (MBD)
Installing SQL Server 2005 Reporting Services 32-bit on a Windows Server 2003 64-bit
Installing a 32-bit version of SQL Server 2005 Reporting Services on a Windows Server 2003 64-bit could be a bit of a challenge, as it requires IIS. I have seen several customers who purchased servers with Windows Server 2003 64-bit pre-loaded while they only have a license for a 32-bit SQL Server 2005.
It is always recommended to have a 64-bit application running on a 64-bit OS to take full advantage of the 64-bit platform. IIS, by default, runs 64-bit on a 64-bit Windows Server system. SQL Server Reporting Services requires ASP.NET which can be manually installed on top of IIS.
When you install the .NET Framework on a 64-bit machine, you have both the 32- and 64-bit versions. Nonetheless, you will not be able to install ASP.NET 32-bit version on a 64-bit IIS, which will be needed by SQL Server Reporting Services.
To be able to install ASP.NET 32-bit on a 64-bit IIS, you need to configure IIS to run 32-bit web applications. With Windows Server 2003 Service Pack 1, IIS can be enabled to run 32-bit applications on a 64-bit Windows using the Windows32-On-Windows64(WoW64) compatibility layer. This makes it possible to run ASP.NET 32-bit and other 32-bit web applications as well as allow creation of 32-bit worker processes.
To enable IIS 6.0 to run 32-bit web applications on a 64-bit Windows, navigate to the %windir%\Inetpub\AdminScripts directory. Run the adsutil.vbs script with the following parameters:
csript.exe adsutil.vbs set W3SVC/AppPools/Enable32BitAppOnWin64 "true"
This will enable IIS to run 32-bit web applications. You can then install SQL Server 2005 Reporting Services 32-bit which will install the corresponding ASP.NET version as part of the .NET Framework.
If, however, you already have the .NET Framework installed on the server, you will need to manually install ASP.NET 2.0 32-bit on IIS after it has been configured. Doing so before configuring IIS will throw an exception stating that you cannot run 32-bit ASP.NET on 64-bit IIS. Navigate to the %WINDIR%\Microsoft.NET\Framework\v2.0.50727 folder (the 64-bit version of the .NET Framework will be at the %WINDIR%\Microsoft.NET\Framework64\ folder). Run the aspnet_regiis.exe utility on the command line.
aspnet_regiis -i
Now that all the groundwork has been prepared, you are ready to run SQL Server 2005 Reporting Services 32-bit on a Windows Server 2003 64-bit.
If, however, you decide to install it on a Windows Server 2008 64-bit, there’s more groundwork to do. By default, IIS 7 as well as ASP.NET are not installed. You have to add the web server role and further configure IIS. SQL Server 2005 Reporting Services is dependent on the IIS 6 or earlier metabase, and thus requires installation of the IIS 6 Metabase Compatibility and IIS 6 WMI Compatibility. Installing these two would also configure the IIS Default Application Pool to run on 32-bit similar to running the adsutil.vbs script for Windows Server 2003.
A Microsoft KB outlines the detailed steps in preparing IIS7 to run SQL Server 2005 Reporting Services on Windows Vista, but the steps work pretty well in Windows Server 2008. In case you still see the installation error on required 32-bit ASP.NET, you can switch the configuration of the Default Application Pool to Enable 32-bit applications. You don’t really need to do this once all the IIS 7 groundwork has been completed but, then again, you’ll never know. It might come in handy.
ORA-01503, ORA-12720 when creating controlfile
CREATE CONTROLFILE REUSE SET DATABASE "T01SBT" RESETLOGS FORCE LOGGING ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
# solution
alter system set cluster_database=false scope=spfile;
Backups in SQL Server 2005/2008, Part 1: The Basics
This is the first post in a series dedicated to exploring the backup and availability options in SQL Server 2005 and 2008. It is aimed at anyone unfamiliar with the database backup options in SQL Server 2005 and 2008. I’m not going to explore every single option or scenario, the goal is to give you the language and the tools to do deep dives where you need to.
SQL Server 2005 has several DBA-job-saving options available to the would-be administrator. Think of a Database Backup as the technology to save data and Database and Availability as the technology to keep it online and available to it’s consumers.
A very brief introduction to SQL Server databasesIts important to have a few SQL Server database basics in order to understand the backup options. If you know what a recovery model is, and the difference between an .ldf and .mdf file, you can skip this section. If this is as good as a foreign language to you, read on.
FilesBy default, every SQL Server database has two files: one data file (with the .mdf extension), and one transaction log file (with the .ldf extension). The data file (.mdf) holds your database objects (tables, views, etc) and the data. The transaction log file (.ldf) contains the transactions in your database. Depending on the recovery model selected, it may hold uncommitted transactions, or it may hold every transaction since the last truncation.
Wait – What’s a transaction?According to Wikipedia, a transaction is defined as a unit of work performed against the database. If you have a particularly large database, someone may split your databases into several files, or even put them on different disks. This is often done to balance the disk activity, and it can be part of your backup strategy. A DBA might put tables that are infrequently or never changed into their own file database file.
Recovery ModelsThere are three recovery models to choose from: Simple, Full, and Bulk-Logged.
This is set per-database and it doesn’t affect options for the other databases on this server. The recovery model chosen affects some backup, availability, and redundancy options.
Simple
Basically, when Simple recovery is in use, the data from any committed transaction will be discarded after each checkpoint is issued. These transactions cannot be backed up or restored. The transaction log for these databases will be smaller.
Full
All transactions will be stored in the transaction log until the log is truncated or backed up. Please note that, contrary to popular belief, only transaction log backups and truncating the log remove entries from the log, full backups do not. More here.
This recovery model is required for certain backup and availability options. In order to avoid extremely large transaction logs and/or filling up the disk, you must schedule regular transaction log backups to use this recovery model.
The reason this recovery model is used is that it allows you to perform point-in-time restores (discussed more later).
Bulk-Logged
Very similar to Full recovery, except some operations are minimally logged. A list of details regarding which operations are minimally logged can be found here. This recovery model also allows point-in-time restores, but not to any point of a non logged operation.
Now that you’re up to speed on files, transactions, and recovery models . . .
What are the backup options?Full backups — These are exact copies of your database at the time they’re taken. They “stand alone” and can be restored back to your server, or onto other SQL Servers. The output of a full backup will be one file, but will include the transaction log and all data files for the database.
Differential backups — These are backups that include all of the changes since the last full backup that was taken. The result will be one backup file and will only hold the changed data. You will require the most recent full backup in order to restore the differential backup(s).
Transaction Log Backups — These are backups of all of the transactions in the exact order of occurrence, since the last full backup, OR the last transaction log backup. Transaction log backups require the database to be in “Full” recovery mode.
Backups can be set up, created, and scheduled via SSMS or scripted and run as code. If someone has added several files to a database or has invoked some of the high-availability options, there are additional options for consideration. This brings me to a couple special circumstance options.
Copy-Only backupsIntroduced in SQL Server 2005 as an option and in SQL Server 2008 as an option in the GUI, copy-only backups do not affect the chain of backups. This is a great addition to the platform as it allows backups to be taken in special circumstances, merely for the purpose of duplication.
Partial BackupsPartial backups are new to SQL Server 2005, and are intended to be used on databases where the tables have been segmented into filegroups, and some of them set to read-only. In this situation, the partial backup would back up all changed data from the non-read-only filegroup.
This is a great feature for large data-warehouse type databases but won’t be used by everyone. The ideal candidate is someone who has a large data warehouse or a similar database where there is archive type date in the database.
This feature is not accessible from SSMS, which means you will need to write SQL to use it.
Differential Partial BackupsOnly to be used with partial backups, all of the same prerequisite circumstances exist for partial backups, with the additional requirement that your must be using partial backups.
SQL Server Enterprise Edition brings a new option for compression of backups. Any edition of SQL Server 2008 can restore compressed backups, but only Enterprise Edition can create them. This option will reduce the size of your database backups.
How do I decide which backups to use, and when to use them?Next post . . .
André Araujo at AUSOUG National Conference
It’s only one week to go now and the program for the AUSOUG National Conference Series 2009 is out. I’ll be presenting on the first day in Perth (Nov 10th) about Oracle Flashback technology.
I’m looking forward to attending the conference in Perth, not only because I’ll be presenting there but also because it’s my first time in Western Australia. All going well my presentation will be honed before the weekend and I’ll be arriving in Perth still this week, on Friday, to enjoy an extended weekend in Perth and Margareth River wine region with my wife before the conference begins.
DUPLICATE Without Connection to Target Database
Backup-Based: Duplication Without a Target ConnectionOracle DUPLICATE Without Connection to Target Database, that's a 11gR2 Feature. DUPLICATE can be performed without connecting to a target database. This requires connecting to a catalog and auxiliary database.
Start...
- Create password file
$ orapwd file=/oracle/product/11.2.0/dbhome_1/dbs/orapwAUX entries=5
Enter password for SYS:- Create pfile (use ORACLE_SID=AUX)
SQL> create pfile='/oracle/product/11.2.0/dbhome_1/dbs/initAUX.ora' from spfile;
File created.Modify initAUX.ora file.
*.audit_file_dest='/oracle/product/admin/AUX/adump'
*.compatible='11.2.0.0.0'
*.control_files='+DISK01/AUX/controlfile/current.263.696964775','+DISK02/AUX/controlfile/current.492.696964779'
*.db_block_size=8192
*.db_create_file_dest='+DISK01'
*.db_domain=''
*.db_name='AUX'
*.db_recovery_file_dest='+DISK02'
*.db_recovery_file_dest_size=10737418240
*.diagnostic_dest='/oracle/product'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=AUXXDB)'
*.job_queue_processes=1000
*.log_archive_format='%t_%s_%r.dbf'
*.log_buffer=33554432
*.memory_target=813694976
*.open_cursors=300
*.processes=150
*.remote_listener='RHEL5-T-cluster-scan:1521'
*.remote_login_passwordfile='exclusive'
*.undo_tablespace='UNDOTBS1'- Check parameters and create some directories
$ mkdir -p /oracle/product/admin/AUX/adump- Check Target Database in Catalog
$ sqlplus rman/password@catalog
SQL> select * from rc_database ;
DB_KEY DBINC_KEY DBID NAME RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
1 501 1224745511 ORCL 3626383 03-NOV-09- STARTUP NOMOUNT (AUXILIARY) database
$ export ORACLE_SID=AUX
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Tue Nov 3 19:27:19 2009
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
- Duplicate Database by RMAN
$ rman
Recovery Manager: Release 11.2.0.1.0 - Production on Wed Nov 4 12:38:14 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> CONNECT AUXILIARY /
connected to auxiliary database: AUX (not mounted)
RMAN> CONNECT CATALOG rman/password@catalog
connected to recovery catalog database
RMAN> DUPLICATE DATABASE orcl TO AUX ;
Starting Duplicate Db at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=14 device type=DISK
contents of Memory Script:
{
sql clone "create spfile from memory";
}
executing Memory Script
sql statement: create spfile from memory
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
contents of Memory Script:
{
sql clone "alter system set control_files =
''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment=
''Set by RMAN'' scope=spfile";
sql clone "alter system set db_name =
''ORCL'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''AUX'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile;
alter clone database mount;
}
executing Memory Script
sql statement: alter system set control_files = ''+DISK01/aux/controlfile/current.282.702045937'', ''+DISK02/aux/controlfile/current.349.702045941'' comment= ''Set by RMAN'' scope=spfile
sql statement: alter system set db_name = ''ORCL'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''AUX'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
Starting restore at 04-NOV-09
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=140 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473
channel ORA_AUX_DISK_1: piece handle=+DISK02/orcl/autobackup/2009_11_03/s_701989468.353.701989473 tag=TAG20091103T210428
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:17
output file name=+DISK01/aux/controlfile/current.282.702045937
output file name=+DISK02/aux/controlfile/current.349.702045941
Finished restore at 04-NOV-09
database mounted
contents of Memory Script:
{
set until scn 3657467;
set newname for clone datafile 1 to new;
set newname for clone datafile 2 to new;
set newname for clone datafile 3 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 6 to new;
set newname for clone datafile 7 to new;
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 04-NOV-09
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00002 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00003 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00004 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00005 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00006 to +DISK01
channel ORA_AUX_DISK_1: restoring datafile 00007 to +DISK01
channel ORA_AUX_DISK_1: reading from backup piece /oracle/RMAN/12ktf0ar_1_1
channel ORA_AUX_DISK_1: piece handle=/oracle/RMAN/12ktf0ar_1_1 tag=TAG20091103T210011
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:04:25
Finished restore at 04-NOV-09
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=19 STAMP=702046260 file name=+DISK01/aux/datafile/system.283.702045995
datafile 2 switched to datafile copy
input datafile copy RECID=20 STAMP=702046260 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=21 STAMP=702046261 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=22 STAMP=702046261 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=23 STAMP=702046261 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=24 STAMP=702046261 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=25 STAMP=702046261 file name=+DISK01/aux/datafile/tbs_fda.288.702046001
contents of Memory Script:
{
set until scn 3657467;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 04-NOV-09
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 1 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355
archived log for thread 1 with sequence 2 is already on disk as file +DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_1.351.702037355 thread=1 sequence=1
archived log file name=+DISK02/orcl/archivelog/2009_11_04/thread_1_seq_2.350.702039149 thread=1 sequence=2
media recovery complete, elapsed time: 00:00:33
Finished recover at 04-NOV-09
contents of Memory Script:
{
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set db_name =
''AUX'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
sql statement: alter system set db_name = ''AUX'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 469764368 bytes
Database Buffers 301989888 bytes
Redo Buffers 36081664 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "AUX" RESETLOGS ARCHIVELOG
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 32
MAXLOGHISTORY 292
LOGFILE
GROUP 1 SIZE 50 M ,
GROUP 2 SIZE 50 M ,
GROUP 3 SIZE 50 M
DATAFILE
'+DISK01/aux/datafile/system.283.702045995'
CHARACTER SET TH8TISASCII
contents of Memory Script:
{
set newname for clone tempfile 1 to new;
switch clone tempfile all;
catalog clone datafilecopy "+DISK01/aux/datafile/sysaux.284.702045997",
"+DISK01/aux/datafile/undotbs1.287.702045999",
"+DISK01/aux/datafile/users.289.702046003",
"+DISK01/aux/datafile/example.286.702045999",
"+DISK01/aux/datafile/tbs1.285.702045999",
"+DISK01/aux/datafile/tbs_fda.288.702046001";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to +DISK01 in control file
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/sysaux.284.702045997 RECID=1 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/undotbs1.287.702045999 RECID=2 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/users.289.702046003 RECID=3 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/example.286.702045999 RECID=4 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs1.285.702045999 RECID=5 STAMP=702046345
cataloged datafile copy
datafile copy file name=+DISK01/aux/datafile/tbs_fda.288.702046001 RECID=6 STAMP=702046345
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=702046345 file name=+DISK01/aux/datafile/sysaux.284.702045997
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=702046345 file name=+DISK01/aux/datafile/undotbs1.287.702045999
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=702046345 file name=+DISK01/aux/datafile/users.289.702046003
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=702046345 file name=+DISK01/aux/datafile/example.286.702045999
datafile 6 switched to datafile copy
input datafile copy RECID=5 STAMP=702046345 file name=+DISK01/aux/datafile/tbs1.285.702045999
datafile 7 switched to datafile copy
input datafile copy RECID=6 STAMP=702046345 file name=+DISK01/aux/datafile/tbs_fda.288.702046001
contents of Memory Script:
{
Alter clone database open resetlogs;
}
executing Memory Script
database opened
Finished Duplicate Db at 04-NOV-09
- Check
SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
1555302662 AUXFun ;) to duplicate database without connection to target database.Written By: Surachart Opun http://surachartopun.com
The Senile DBA Guide to Troubleshooting Sudden Growth in Redo Generation
I just troubleshooted a server where the amounts of redo generated suddenly exploded to the point of running out of disk space.
After I was done, the problem was found and the storage manager pacified, I decided to save the queries I used. This is a rather common issue, and the scripts will be useful for the next time.
It was very embarrassing to discover that I actually have 4 similar but not identical scripts for troubleshooting redo explosions. Now I have 5 
Here are the techniques I use:
- I determine whether there is really a problem and the times the excessive redo was generated by looking at v$log_history:
select trunc(FIRST_TIME,'HH') ,sum(BLOCKS) BLOCKS , count(*) cnt ,sum(decode(THREAD#,1,BLOCKS,0)) Node1_Blocks ,sum(decode(THREAD#,1,1,0)) Node1_Count ,sum(decode(THREAD#,2,BLOCKS,0)) Node2 ,sum(decode(THREAD#,2,1,0)) Node2_Count ,sum(decode(THREAD#,3,BLOCKS,0)) Node3 ,sum(decode(THREAD#,3,1,0)) Node3_Count from v$archived_log where FIRST_TIME >sysdate -30 group by trunc(FIRST_TIME,'HH') order by trunc(FIRST_TIME,'HH') desc
- If the problem is still happening, I can use Tanel Poder’s Snapper to find the worse redo generating sessions. Tanel explains how to do this in his blog.
- However, Snapper’s output is very limited by the fact that it was written specifically for situations where you cannot create anything on the DB. Since I’m normally the DBA on the servers I troubleshoot, I have another script that actually gets information from v$session, sorts the results, etc.
create global temporary table redo_stats ( runid varchar2(15), sid number, value int ) on commit preserve rows; truncate table redo_stats; insert into redo_stats select 1,sid,value from v$sesstat ss join v$statname sn on ss.statistic#=sn.statistic# where name='redo size' commit; insert into redo_stats select 2,sid,value from v$sesstat ss join v$statname sn on ss.statistic#=sn.statistic# where name='redo size' commit; select * from redo_stats a, redo_stats b,v$session s where a.sid = b.sid and s.sid=a.sid and a.runid = 1 and b.runid = 2 and (b.value-a.value) > 0 order by (b.value-a.value) - Last technique is normally the most informative, and requires a bit more work than the rest, so I save it for special cases. I’m talking about using logminer to find the offender in the redo logs. This is useful when the problem is no longer happening, but we want to see what was the problem last night. You can do a lot of analysis with the information in logminer, so the key is to dig around and see if you can isolate a problem. I’m just giving a small example here. You can filter log miner data by users, segments, specific transaction ids – the sky is the limit.
-- Here I pick up the relevant redo logs. -- Adjust the where condition to match the times you are interested in. -- I picked the last day. select 'exec sys.dbms_logmnr.add_logfile(''' || name ||''');' from v$archived_log where FIRST_TIME >= sysdate-1 and THREAD#=1 and dest_id=1 order by FIRST_TIME desc -- Use the results of the query above to add the logfiles you are interest in -- Then start the logminer exec sys.dbms_logmnr.add_logfile('/u10/oradata/MYDB01/arch/1_8646_657724194.dbf'); exec sys.dbms_logmnr.start_logmnr(options=>sys.dbms_logmnr.DICT_FROM_ONLINE_CATALOG); -- You can find the top users and segments that generated redo select seg_owner,seg_name,seg_type_name,operation ,min(TIMESTAMP) ,max(TIMESTAMP) ,count(*) from v$logmnr_contents group by seg_owner,seg_name,seg_type_name,operation order by count(*) desc -- You can get more details about the specific actions and the amounts of redo they caused. select LOG_ID,THREAD#,operation, data_obj#,SEG_OWNER,SEG_NAME,TABLE_SPACE,count(*) cnt ,sum(RBABYTE) as RBABYTE ,sum(RBABLK) as RBABLK from v$logmnr_contents group by LOG_ID,THREAD#,operation, data_obj#,SEG_OWNER,SEG_NAME,TABLE_SPACE order by count(*) -- don't forget to close the logminer when you are done exec sys.dbms_logmnr.end_logmnr; -
If you have ASH, you can use it to find the sessions and queries that waited the most for “log file sync” event. I found that this has some correlation with the worse redo generators.
-- find the sessions and queries causing most redo and when it happened select SESSION_ID,user_id,sql_id,round(sample_time,'hh'),count(*) from V$ACTIVE_SESSION_HISTORY where event like 'log file sync' group by SESSION_ID,user_id,sql_id,round(sample_time,'hh') order by count(*) desc -- you can look the the SQL itself by: select * from DBA_HIST_SQLTEXT where sql_id='dwbbdanhf7p4a'
Logger, A PL/SQL Logging and Debugging Utility
Good Article About btrfs
Run the same query against multiple SQL Server AND Oracle instances
It is not unusual that a DBA needs to run the same query across multiple instances. The query can be anything from a simple line to retrieve a specific value (such as an instance version), to others that involve data modifications or schema changes.
SQL Server 2008 brought the ability to execute Statements Against Multiple Servers Simultaneously by simply creating a local server group or broadly using a Central Management Server and one or more server groups. Inside these groups there should be one or more registered servers.
There is also the commercial Red-Gate SQL Multi Script with an Unlimited edition license that allows you to run your code against any number of SQL server instances.
Before SQL Server 2008, there was no native support for running the same query against multiple instances using shipped tools, so it can be approached in one of these ways:
- Manually connecting to each instance to run the code; this is very time consuming.
- Using batch files to call OSQL or SQLCMD to loop against a defined set of servers; this involves a bit more work and control of the batch files.
- Using linked servers and loops inside a T-SQL query.
- Using DTS or SQL server Integration services.
I’ve used nearly all of them, but I have found that linked servers provide a higher degree of control on the target servers, error handling, and most important, the ability to use SQL server Encryption to store the credentials of source servers if some of the servers are using SQL authentication.
I’ve also managed to make this run against Oracle databases, but had to take the extra step of configuring configuring Oracles as linked servers as described in KB 280106.
The code has the following characteristics:
- It uses encryption to store and retrieve credentials for Oracle and SQL server instances that use SQL authentication. There is a way to connect to Oracle using windows authentication but I didn’t test that.
- The code uses an ASYMMETRIC KEY to encrypt the credentials.
- It uses SQL cursors to retrieve list of servers against which I will run the code.
- Use of TRY…CATCH for error handling.
- Code against SQL server is run using Sp_executesql. The supplied code is liable for any restrictions imposed by Sp_executesql; I found very long queries in particular to be sometimes a problem.
- Code run against Oracle instances are done through Openquery. I found that using direct Linked server name exposes some problems especially with metadata. The supplied code must be Oracle-compatible.
- You need at least SQL server 2005 to use TRY..CATCH and Encryption.
- You need to format the source code to be syntax error-free, especially when it contains single quotes.
- When it comes to retrieving data only, Openrowset looks like a fast alternative to creating then dropping a linked server. OpenRowset is a fast way to access remote data on the fly using an OLE DB data source.
- I’ve not used the code much against Oracle—just simple tasks, but it worked for me.
- The code can be used against any datasource (DB2, MySQL, etc.) that can be accessed using Linked servers.
Here is the code. sql-oracle.sql.txt I welcome your feedback and additions.
Just Fun with RMAN Recovery...
Tue Nov 03 20:12:13 2009
ALTER DATABASE OPEN
This instance was first to open
Tue Nov 03 20:12:13 2009
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '+DISK01/orcl/datafile/system.267.696964505'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/system.267.696964505
ORA-15012: ASM file '+DISK01/orcl/datafile/system.267.696964505' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '+DISK01/orcl/datafile/sysaux.266.696964509'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/sysaux.266.696964509
ORA-15012: ASM file '+DISK01/orcl/datafile/sysaux.266.696964509' does not exist
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_dbw0_3372.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '+DISK01/orcl/datafile/undotbs1.265.701210003'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/datafile/undotbs1.265.701210003
ORA-15012: ASM file '+DISK01/orcl/datafile/undotbs1.265.701210003' does not exist
It's time to test(RMAN) database recovery (From BACKUPSET).
Workaround:
run{
startup nomount;
restore controlfile;
alter database mount;
restore database;
recover database;
alter database open resetlogs;
}But It could not "resetlogs"... and alert log showed:
alter database open resetlogs
Errors in file /oracle/product/diag/rdbms/orcl/orcl/trace/orcl_ora_5509.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '+DISK02/orcl/onlinelog/group_1.491.696964811'
ORA-17503: ksfdopn:2 Failed to open file +DISK02/orcl/onlinelog/group_1.491.696964811
ORA-15012: ASM file '+DISK02/orcl/onlinelog/group_1.491.696964811' does not exist
ORA-00312: online log 1 thread 1: '+DISK01/orcl/onlinelog/group_1.262.696964787'
ORA-17503: ksfdopn:2 Failed to open file +DISK01/orcl/onlinelog/group_1.262.696964787
ORA-15012: ASM file '+DISK01/orcl/onlinelog/group_1.262.696964787' does not exist Perhaps I made something wrong, so i check last SCN(backupset) in catalog and recovery "until scn" :
$ rman target / catalog rman/password@catalogRMAN> list backup
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
422 Incr 4 760.00K DISK 00:01:58 03-NOV-09
BP Key: 425 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174313
Piece Name: /oracle/RMAN/0uktekpi_1_1
List of Datafiles in backup set 422
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/system.267.696964505
2 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/sysaux.266.696964509
3 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/undotbs1.265.701210003
4 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/users01.dbf
5 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/example.259.696964945
6 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs1.256.697633229
7 4 Incr 3626382 03-NOV-09 +DISK01/orcl/datafile/tbs_fda.257.701208791
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
443 Full 17.95M DISK 00:00:06 03-NOV-09
BP Key: 451 Status: AVAILABLE Compressed: NO Tag: TAG20091103T174521
Piece Name: +DISK02/orcl/autobackup/2009_11_03/s_701977521.358.701977527
SPFILE Included: Modification time: 03-NOV-09
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3626526 Ckp time: 03-NOV-09
RMAN> shutdown
database dismounted
Oracle instance shut down
RMAN> startup nomount;
connected to target database (not started)
Oracle instance started
Total System Global Area 810053632 bytes
Fixed Size 2217712 bytes
Variable Size 645925136 bytes
Database Buffers 125829120 bytes
Redo Buffers 36081664 bytesRMAN> restore controlfile from autobackup;
Starting restore at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=14 device type=DISK
recovery area destination: +DISK02
database name (or database unique name) used for search: ORCL
channel ORA_DISK_1: AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527 found in the recovery area
channel ORA_DISK_1: looking for AUTOBACKUP on day: 20091103
channel ORA_DISK_1: restoring control file from AUTOBACKUP +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
channel ORA_DISK_1: control file restore from AUTOBACKUP complete
output file name=+DISK01/orcl/controlfile/current.263.696964775
output file name=+DISK02/orcl/controlfile/current.492.696964779
Finished restore at 03-NOV-09
RMAN> alter database mount;
Database altered.
RMAN> run {
set until scn 3626382;
restore database;
recover database;
alter database open resetlogs;
}
executing command: SET until clause
Starting restore at 03-NOV-09
Starting implicit crosscheck backup at 03-NOV-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=13 device type=DISK
Crosschecked 22 objects
Finished implicit crosscheck backup at 03-NOV-09
Starting implicit crosscheck copy at 03-NOV-09
using channel ORA_DISK_1
Crosschecked 1 objects
Finished implicit crosscheck copy at 03-NOV-09
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: +disk02/ORCL/AUTOBACKUP/2009_11_03/s_701977521.358.701977527
using channel ORA_DISK_1
skipping datafile 4; already restored to file +DISK01/orcl/datafile/users01.dbf
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to +DISK01/orcl/datafile/system.267.696964505
channel ORA_DISK_1: restoring datafile 00002 to +DISK01/orcl/datafile/sysaux.266.696964509
channel ORA_DISK_1: restoring datafile 00003 to +DISK01/orcl/datafile/undotbs1.265.701210003
channel ORA_DISK_1: restoring datafile 00005 to +DISK01/orcl/datafile/example.259.696964945
channel ORA_DISK_1: restoring datafile 00006 to +DISK01/orcl/datafile/tbs1.256.697633229
channel ORA_DISK_1: restoring datafile 00007 to +DISK01/orcl/datafile/tbs_fda.257.701208791
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0sktekgc_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0sktekgc_1_1 tag=TAG20091103T173819
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:48
Finished restore at 03-NOV-09
Starting recover at 03-NOV-09
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DISK01/orcl/datafile/system.272.701988391
destination for restore of datafile 00002: +DISK01/orcl/datafile/sysaux.273.701988393
destination for restore of datafile 00003: +DISK01/orcl/datafile/undotbs1.276.701988395
destination for restore of datafile 00004: +DISK01/orcl/datafile/users01.dbf
destination for restore of datafile 00005: +DISK01/orcl/datafile/example.275.701988395
destination for restore of datafile 00006: +DISK01/orcl/datafile/tbs1.274.701988395
destination for restore of datafile 00007: +DISK01/orcl/datafile/tbs_fda.277.701988395
channel ORA_DISK_1: reading from backup piece /oracle/RMAN/0uktekpi_1_1
channel ORA_DISK_1: piece handle=/oracle/RMAN/0uktekpi_1_1 tag=TAG20091103T174313
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished recover at 03-NOV-09
database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync completeJust fun with RMAN (If A database has backup)... ;)Written By: Surachart Opun http://surachartopun.com
Zero ajax label help tooltips for Apex?
Thinking about Design Patterns
I have this friend who is an ambitious young corporate climber. When I was started out as a team lead, I was totally overwhelmed by the office politics I was suddenly exposed to. Naturally, I turned to my friend for advice. She told me to read Machiaveli’s “The Prince”. So I did. Its an interesting read, but not that amazing as far as management advice goes. When I later tried to discuss the book with my friend, I found out that she never actually read the book herself – she just thought it is good advice.
6 years later and I still believe people when they tell me that I have to read a book. I’m naive like that.
So I spent the last two weeks reading “The Timeless Way of Building”. Its an architecture book. Architecture as in cities and buildings. The reason I spent two weeks reading a professional book intended for a different profession is that at some point in history (1994, I believe), some people though that the ideas in the book are relevant to software development. These days you can’t really be a Java developer without being fully fluent in the development pattern language.
And of course, everyone was saying “You have to read The Timeless Way of Building. It will change the way you think about software.”. From my days in development, I still remember quite a bit of stuff about design patterns, and I never really liked that particular approach to software development, but I didn’t really figure out why. After reading the authoritative source on patterns, I can say the following:
- Christopher Alexander had some good ideas about patterns. The book is readable to non-architects and is very enlightening. I recommend reading it if you are interested in what makes some cities and buildings feel better than others.
- I am pretty sure that his advice on how to design good buildings is not really applicable to software development field.
A lot of his ideas are based on the fact (which he did the research to prove) that people intuitively know how buildings should be designed, and that when you ask a large number of people “How do you imagine you will feel in such room?”, you’ll get an overwhelming consensus. This is far from being true in the software field. - What is common known today as software design patterns is so far removed from what Christopher Alexander recommended for architects, that software developers should really go and find a different name for what they are doing.
I’m still rather shocked by the differences between Christopher Alexander’s patterns, and what design patterns look like today. It is not few tiny differences that occur whenever ideas are translated from one domain to another. Some of the changes are profound.
First of all, Christopher Alexander says that patterns describe the way people already do things. “Night Life” and “Parallel Streets” existed before the book “A Pattern Language” was written. I’m not at all sure this is the case for design patterns. People buy design pattern books to learn the patterns themselves, not just the language or which patterns are better than others.
Second, patterns should have an intuitive meaning and intuitive name. Again, you don’t need a book to know what is a “Bus Stop” or “Small Parking Lots”. You may want to read the book to find out why they are a good idea, or how to make a good bus stop, but you know what it is. I don’t believe that anyone knew what is an “Abstract Factory” before reading a document about design patterns. Even patterns that have been used for decades got a fancy name. It can take a while to figure out that a Singleton is a global variable. One of the simplest and most common patterns in software development “A function that does exactly one task” is missing from software design patterns. “A Loop” is also a pattern which is missing in action. All this gives the wrong impression that patterns are very complicated and something that can be mastered by experts only – which is exactly the opposite of what Christopher Alexander intended.
Third, patterns are abstract concepts. They are always implemented in a different way, because the entire idea is to be sensitive to the context, which is never the same twice. There is a pattern called “Six-foot balcony”, but it would be wrong to mass-manufacture six-foot balconies and start attaching them to buildings. Six-foot balcony is the idea, the exact shape of the balcony will be designed to match the building, the view, the trees, the sun, etc.
So it is rather annoying to discover that all patterns have “implementation examples”, which developers enjoy copying into their code. I’m all for code reuse, but this is not patterns mean.Wikipedia has a decent description of what defines a pattern, and “being implementable in one or two simple classes that can be copy-pasted” is not part of it.
Executive summary: “Timeless Way of Building” is an interesting book on architecture, with some good insights about how humans like to live and a bit of a Zen feel. You will not learn anything about software development from reading it. If you already know software design patterns, you will be struck by how different the ideas in the book seem.
Two Oracle RAC bugs on the wall, two Oracle bugs. Take one down …
Ok, not as good as beer and they can give you a nasty headache, so you have been warned ;)
Reason for this post are 2 bugs I discovered with Oracle RAC, both resulting in a single point of failure.
The platform on which I’m working is Oracle 10gR2 (10.2.0.4) on OEL 4.7.
The first one is when you are using NFS volumes to host the ocr and ocrmirror volumes.
Normally, when the ocr volume gets corrupted or unavailable , oracle should failover to the ocrmirror volume. The exact response is documented in the RAC FAQ on metalink (note 220970.1) and is currently discussed in a series of blog posts by Geert de Paep.
With NFS, however, you must use both the nointr and hard mount options (OS is OEL 4.7) and as a result the process that is trying to read or write an unavailable ocr volume will wait undefinitly on a response. This is not only happening when using commands such as crs_stat or srvctl, but also when an instance or service failover is initiated.
Oracle support however, does not exactly see it this way and has first blamed the os, then the storage and finally stated that there is no failover foreseen between the ocr and ocrmirror volumes…
It took some escalating and a change in support engineer to get some progress in that SR (mind you that after more then 4 months, they still have not acknowledged it as a bug).
The second problem is that, when you made the public interface redundant with os bonding, the racgvip script does not detect when all interfaces in the bond are disconnected.
This is caused because the script, unlike older version, is using mii-tool to check the availability of the public interface. Only when mii-tool states that the link is down, a ping test is done to the public gateway. If that test fails as well, then the vip fails over and the rac instances on that node are placed in a blocked state.
The problem however with mii-tool is that it plays not very well with bonds, and always reports the bond status as being up (in fact, regardless of the link state, mii-tool is always reporting a network bond as “bond0: 10 Mbit, half duplex, link ok”). So, the racgvip script always thinks that the public interface is up.
As mii-tool is an os utility, I first opened a case on the Oracle Enterprise Linux support, to check with them if its behavior was normal (I already confirmed that by googeling, but Oracle support does not seem to accept results from google :) ). And after running multiple tests with different bond options, they finally stated that mii-tool was indeed obsolete and should not be used to verify a bond status (yes, I know. Its own man page already states that mii-tool is obsolete).
So next, I opened a SR on part of the clusterware and oracle development promptly stated that it was not a clusterware bug but an os issue, pointing the finger to mii-tool and asking where it was written that mii-tool is obsolete… . After making them aware of the statement made by their OEL colleagues and the mii-tool man page, they have seemed to have accepted it as a bug.
I have checked the 11gR2 version of the racgvip script, and it seems to suffer the same problem.
ps) Note 365605.1 – “Oracle Bug Status Codes, Descriptions and Usage” is, although it seems incomplete, very usefull to understand the different status codes
Learn "NOLOGGING" with Tablespace "force logging" mode
If the database, or that tablespace, is in "force logging" mode, the nologging will not work.Check Database is Archive Mode:
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 101
Next log sequence to archive 103
Current log sequence 103Check REDO Size with "create table" logging:
SQL> @redo-new
OLD_VALUE
----------
1860
SQL> create table T_LOGGING tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 1860) OLD_VALUE
OLD_VALUE
----------
8622128Check REDO Size with "create table" nologging:
SQL> @redo-new
OLD_VALUE
----------
784
SQL> create table T_NOLOGGING nologging tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 784) OLD_VALUE
OLD_VALUE
----------
105448Check REDO Size with "create table" nologging on Tablespace "force logging" mode:
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';
TABLESPACE_NAME FOR
------------------------------ ---
TBS1 NO
SQL> alter tablespace TBS1 force logging;
Tablespace altered.
SQL> select tablespace_name, force_logging from dba_tablespaces where tablespace_name='TBS1';
TABLESPACE_NAME FOR
------------------------------ ---
TBS1 YES
SQL> @redo-new
OLD_VALUE
----------
788
SQL> create table T_NOLOGGING_F nologging tablespace TBS1 as select * from dba_objects;
Table created.
SQL> @redo-diff
old 1: select (value - &OLD_VALUE) OLD_VALUE
new 1: select (value - 788) OLD_VALUE
OLD_VALUE
----------
8640936That's right, NOLOGGING isn't work on database or tablespace in "force logging" mode.
redo-new.sql:
column OLD_VALUE new_value OLD_VALUE
select value OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';redo-diff.sql:
select (value - &OLD_VALUE) OLD_VALUE
from v$mystat, v$statname
where v$mystat.statistic# = v$statname.statistic#
and v$statname.name = 'redo size';Written By: Surachart Opun http://surachartopun.com
