Introduction Outline - Start with miscelaneous stuff about your environment, and how to start and stop the database - Move into what separates DBA from someone with root access, Backup and Recovery Database Environment - Everyone at Blackboard is using either liux or solaris, so focus there - Two mandatory environment variables, and one really useful one - ORACLE_HOME tells oracle (and you) where the Oracle software is located. - ORACLE_SID tell oracle (and you) which database instance you are currently interested in using, when you don't explicitly say so in your connect string - SQLPATH tells oracle (and you) where to look for the login.sql file full of SQL*Plus commands Stopping and Starting - Four ways to stop your database. In a blackboard enviuronment, only one will actually stop the database. *describe four ways* - Also four ways to start the database, One starts, two are for maintenace, one os a special case of shutdown abort followed by startup - abort and force are potentially dangerous to the integrity of your data. Use sparingly Startup on Boot - When the host reboots, a script is needed to automatically start your databases - Look for the oratab file, which acts like an fstab file for Oracle. - oratab contains one line per instance, comma separated. Three firlds, the Oracle SID, the Oracle_home for that SID, and a Y or N saying to autostart or not. - Finally, you need a script called by RC that calls "dbstart" and "lsnrctl start" Login.SQL - SQL*Plus sets many options rather stupidly. - By default, pagesize is 24, which tells SQL*Plus to print a table header every 24 rows. - Also, Long is set to 80 characters, so trying to view any reasonable data in CLOBS is difficult. - You can change many options by running commands inside SQL*Plus, but you have to remember to do them everytime you start a new session of SQL*Plus. - This file contains SQL*Plus commands that get called everytime SQL*Plus starts. - First you need to set the SQLPATH environment variable to where the login.sql file is located. I store mine in my homedirectory, and set SQLPATH accordingly. - inside login.sql, I define serveroutput for pl/sql output, set long to 5000 characters to get a better idea what's in my clobs - trimspool removes trailing spaces from each column in every row when spooling to a file. This makes your file much smaller and your TSM much happier Logs - Oracle logs stored in three places. - The parameters ending in dump_dest show where the different logs are stored. You can also change the storage location by altering those oracle parameters. - The most important log file is the alert.log. - Periodically grep for the string ORA which will show any oracle errors that may have occurred recently. Backups - As a DBA, you really have one job. Sure it seems like a lot is expected of you every day, but at the end of the day, you are being paid the big bucks to ensure that when it becomes necessary, you are able to recover the database data after a major disaster. Your data is only as safe as your ability to recover is after a crash. ** more here** **- Benefits of each type ** - If you don't currently have a backup strategy, I highly recommend the RMAN solution. It's easy to implement, very performance efficient to use, and well supported by Oracle. Archive Logs - All Oracle backup schemes first rely on your database being run in archive log mode. - Oracle keeps a records of all inserts, updates, and deletes in online redo logs. These are a series of files on your filesystem that are written to until they are full, then Oracle moves on to writing the next one. Once all files in the set are full, Oracle will circle back and start writing to the first log file again, overwriting what had been written. - In order to recover the database in the event of a crash or data loss, Oracle needs to "replay" the data changes that were recorded in the redo logs. If the redo logs are not avaialble, then recovery is not possible. - In order to maintain a much longer history of changes, Oracle employs Archive Redo Logs. Archive logs are simply copies of the online redo logs. When an online redo log is full, Oracle can make a copy of that log file and store it somewhere safe. This copy os the archive log. - There's a strong temptation to turn off archive logging, you save storage, a bit of processing power. People on a budget might look to archivelogging as a cost savings. - “Running a database in noarchivelog mode guarantees one thing You will someday lose all of your data since your last full cold backup. ” Tom Kyte - Are you archivelogging? Show of hands, how many of you know for fact you are archivelogging? How many think you are? - How to check: run the sql command "archive log list" It will show one of two things, "Log Mode", and "Automatic Archival" - Log Mode: When in Archive mode, this tells oracle not to reuse online redo logs until they have been copied to at least one of the archive log locations. - Automatic Archival: This tells Oracle wether to automatically make copies of the redo logs for you, or if it should wait for you to manually issue the command to copy the files. If disablesd, then Oracle waits for you to issue the command "archive log switch" from teh SQL prompt. This is not feasible in a production environment. I personally see no benefit to disabling Automatic Archival. Leave it Enabled. Start Archive Log Today - If you are not currently archive logging, here's how to get started. ** Read Slides ** RMAN Backups - Now we'll come to the actual backing up of your database. For simplicity sake, I'm going to perform a complete backup of your database using RMAN. Cold backups are pretty straightforward, and Hotbackups are outside the scope of this presentation. RMAN - The first question Oracle will ask of you when setting up RMAN is where do you want to store the metainformation used by RMAN. - If a single database, you can store in that database control file - If managing many databases, may make sense to create a repository. ** Describe benefits/ cons of each method** - Because I'm lazy and it's simpler, I'll choose the control file method - Also, because I have very little time to go over this and still take questions, this will be a functional but basic RMAN setup. RMAN Prerequs - Database In Archive Log Mode. You're experts now in archive log mode. - A place to store the database backup files. RMAN will make backup copes of your database, and you need a place to store them. For safety sake, this should be a different storage location than your production data. - Think about it, if you have just a single storage location, and that storage location crashes, then your backups just died along with your production data. - RMAN only supports OS read/and writes, so your storage will need to appear to be mounted locally to the database server. You can direct attach storage, or mount it via network storage like NFS. You cannot use something like RCP or SCP to copy the data to a remote host. - lastly, while it's not technically necessary, I highly recommend you let oracle maintain multiple copies of your controlfile, on separate filesystems, on separate disk systems. One example would be to have a copy on your SAN, and a second copy on your local hard drives. RMAN Setup - Before RMAN can backup your database, it needs to know a few things first. - First is to configure a CHANNEL. A channel is a device used by RMAN to copy data to a location. You can have RMAN copy your data directly to a disk location or to a tape device. The simple can is to copy to a disk. Here, I tell RMAN to copy to a disk, I tell it how big to restrict the file sizes to, and I tell it the location and format of the backup pieces. You can see that the location is a filesystem I mounted via NFS. - Next I tell RMAN how far back in time I want to be able to go to recover my database. With the setting of 30 days, RMAN will maintain enough backup pieces to let me recover my database to a point in time as far back as 30 days. That means I could in theory roll back my database to the way it looked 30 days ago. The most obvious use case is to recover your database back to the most recent possible moment, and not lose any data, but having the option of going back in time is useful when making copies of your database for your test environment. Such copies are outside the scope of this talk RMAN Setup (2) - Next we'll tell RMAN to automatically backup teh controlfile everytime a backup occurs. This is especially important with this style of backups, as our control file is our only reference to the backups we are making. If we lose the controlfile, we lose access to our backups. RMAN Backup - Now to take an actual backup. - We do Incremental Backups. Level 0 says to take a full backup of the database. This copies everything, and will server as a baseline for future incremental backups. I'd recommend taking a level 0 backup once a week or so. - The other options to this backup command tell RMAN to backup your spfile, which contains the server parameters used to customize your oracle instance. - Also, we told RMAN to backup all archivelogs that are not already backed up. - You want to wrap this command up in a script and run the script via cron on a regular basis RMAN Backup (2) - The next backup is a level 1 backup. This tells RMAN to backup only data that has changed since the last level 0. These level 1 backups are much faster and copy much less data than the level 0 backups. The CUMULATIVE option tell oracle to copy all changes since the last level 0, rather than the last level 1. This option will result in more data copied with each subsequent level 1, but also makes recovery a bit faster when you need to recover. - Other options remain the same, make a backup of the spfile and archive logs every time this command runs - This should also be put into a script and schedule to run daily or even twice daily via cron RMAN Automation - Backups are only good is they are consistently taken. Make good backups by using cron to automate the backups - Create two scripts, one that does a level 0 backup, and one that does a level 1 backup. - Schedule them in cron so the level 0 runs once a week on Sunday, and the level 1 runs the other 6 days of the week. - On my website along side the presentation notes and slides is a document with a sample RMAN script suitable for putting into cron. RMAN Recovery - ** Describe ways that disaster can occur, flood, fire, admin mistake ** - All recovery happens in a similar way. When the database experiences data loss, the steps to follow are to first start an RMAN session like usual. - Now restart the database but only mount it. This will tell oracle to read the controlfile without opening the actual data files. - Next, tell RMAN to restore the database. RMAN will look at all the datafiles and compare them with the most recent backups. Any missing datafiles are restored from the backup location. - Next we tell RMAN to recover the database. This is where RMAN will make use of any available archive logs and redo logs to replay transactions to bring the database back to the most current time possible. - Now that the database is recovered, you can open it up and resume business. - These steps will cover most database disaster problems, but there are a few special cases that will require some extra assistance. One case is when all copies of your controlfile are lost. In this case, you'll need to first restore your control file from the backup location before you can begin recovery. - Another problem situation would be if all copies of the online redo logs are lost. Such a situation means that Oracle has no copies of the most recent transactions, which means they are lost forever. No amount of recovery can restore those final few transactions. - If you encounter any of these special situation, I highly recommend you contact Oracle support to review your situation and figure out where you stand and what can be recovered. Heck, even if you have normal, run of the mill type problems, contact Oracle support. Practice Recovery Often - Nuff Said **Pause** RMAN Practice - One good way to practice your recovery is to use RMAN to clone your database. - A really good use of a cloned database is to copy all your production data to your test environment, make a few changes to the BBADMIN schema, and you've got a great test instance of Blackboard ready to go. - ** More Here ** (Fiery Picture) - The last word I'll say on backups is another quote from Tom Kyte "Recovery is the only thing you really really need to be able to do. As a DBA you can mess lots of other stuff up. But get to where you need recovery and then discover that you can't - that is a really bad feeling." Questions (1)