Knowledge increases exponentially. Today, you probably own more books than great universities of times past—Cambridge University owned less than two hundred books in the fifteenth century. First came the invention of writing, then alphabets, then paper, then the printing press, then mechanization. Each step caused an exponential increase in the collective human knowledge. In our generation, Al Gore invented the internet and the last barriers to the spread of knowledge have been broken. Today, everybody has the ability to contribute, communicate, and collaborate. We are all caught up in a tsunami, an avalanche, a conflagration, a veritable explosion of knowledge for the betterment of humankind. This is the blog of the good folks at Database Specialists, a brave band of Oracle database administrators from the great state of California. We bid you greeting, traveler. We hope you find something of value on these pages and we wish you good fortune in your journey.
|
Mike Dean, Sr Staff Consultant
One of the new Backup and Recovery features of Oracle 11g Release 2 (11gR2) is the ability to duplicate a database without connecting to the target database. In RMAN terminology, the “target” database is the one you wish to duplicate. In this case, the only requirement is that you provide a disk backup location where RMAN can find all the backups, data file copies, archived logs, and control file copies for database duplication. This database duplication enhancement is helpful when it is not possible to connect to the target database and the recovery catalog.
This came in handy for me recently on a customer project. They wanted me to test a disaster recovery scenario in which the production database server was completely gone and we needed to restore it from tape onto another server. This is a fairly typical DR situation, but in this case it was made more difficult because the directory structure on the test server didn’t match that of the production server. Specifically, on the production server, everything was located on the E: drive while on the Test server, the only drive that exists is C:. This means that all file locations need to be changed as part of the duplicate procedure. This can all be done in one single RMAN command.
Note: One thing that makes this process easier is to enable controlfile autobackups on the production database. This is a good practice in general and should be configured for all databases.
rman target / nocatalog
configure controlfile autobackup on;
Here are the steps that I followed:
Step1) Copy the most recent backup files and archivelog files to the test server. In this case, we restored from tape to the location c:\prod backup files. The backup files and archivelogs come from the following locations:
E:\app\flash_recovery_area\prod\ARCHIVELOG\2013_03_21
E:\app\flash_recovery_area\prod\BACKUPSET\2013_03_21
E:\app\flash_recovery_area\prod\AUTOBACKUP\2013_03_21
Step 2) Create a file called INITPROD2.ORA in %ORACLE_HOME%\database containing one line: db_name=PROD2
Step 3) At a command prompt:
set oracle_sid=prod2
oradim –new –sid prod2
sqlplus / as sysdba
startup nomount
exit
rman auxiliary / msglog=c:\restore.log
Step 4) At the RMAN prompt:
duplicate database to prod2 spfile
set control_files=’C:\app\oracle\oradata\prod2\control01.ctl’
set db_file_name_convert=’E:\app\oradata\prod’,'C:\app\oracle\oradata\prod2′,’E:\app\prod_tbs’,'C:\app\oracle\oradata\prod2′
set LOG_FILE_NAME_CONVERT=’E:\app\oradata\prod’,'C:\app\oracle\oradata\prod2′,’C:\oracle\oradata\prod’,'C:\app\oracle\oradata\prod2′
set db_recovery_file_dest=’c:\app\administrator\flash_recovery_area’
set diagnostic_dest=’c:\app\administrator\diag’
backup location “c:\prod backup files\”;
That’s all it takes! As you can see, I changed all file location parameters and converted datafile and logfile names with the _CONVERT parameters. You can set any parameters for the new database in this way. For example you may want to disable all database jobs in the duplicate database by setting job_queue_processes=0. Or, change the size of the SGA for a smaller server. RMAN will create the PROD2 database, perform as much recovery as possible given the available archive logs and open the database with the resetlogs option. This is a really nice feature of RMAN.
Mike Dean, Sr Staff Consultant
I arrived home from Denver, CO around 2am this morning after attending the Rocky Mountain Oracle Users Group Training Days 2013 conference (aka RMOUG TD2013). What a great event! It was two full days (with an optional 3rd half-day) of intense Oracle training – enough to make my brain hurt – given by some of the best-known experts in the field.
Interestingly, the conference was not strictly about Oracle. There were quite a few sessions on MySQL, SQLServer, Hadoop and various development topics. However, since I have limited mental capacity, I decided to stick with what I already know and attended mostly the Oracle-specific sessions. Because we have a few customers running Oracle Exadata, I attended several sessions devoted to this, hoping to improve my understanding in this area. I have to say that I definitely have a better understanding of some of the key aspects of this technology now.
The highlight of the conference for me was on Tuesday at 4pm. This is when I did my own presentation: “All About Oracle Auditing – Everything You Need to Know”. I have been working on this for quite a while and was very happy to have the opportunity to do my presentation in front of other Oracle professionals. Database auditing is an important part of any security strategy and I tried to present the audience with technical facts along with my own observations and experience on the subject. It was both well attended and (I think) well received by the audience. If you weren’t lucky enough to attend the conference, you can download my presentation and the accompanying white paper from our website. You can find them here: http://www.dbspecialists.com/presentations.html#Dean_paper
Gary Sadler, Sr. Staff Consultant
Have you found that daily full database backups are becoming impractical for your growing databases? Incremental backups are no longer the exception in some shops. But with the increased complexity comes a greater responsibility for thoroughly planning and testing your backup solution. Testing a more complicated backup solution makes for a more complicated validation procedure. This article isn’t going to get into every aspect of backup testing but we will show one example of how an inadequate test of incremental backups can have disastrous results in a database recovery scenario. For this example we used Oracle 11.2.0.1 Enterprise Edition on CentOS 5.5. I tried the same test on Oracle EE 10.2.0.4 and 11.1.0.7 with similar results.
While auditing a backup solution for a new client recently, we noted that they were using the RMAN syntax for improving backup efficiency:
RMAN> backup database incremental level 0 not backed up since time “blah blah blah”;
The NOT BACKED UP SINCE TIME clause can be handy for resuming backups that were interrupted for some reason. Suppose your strategy calls for weekly level-0 incrementals and daily level-1 incrementals. The level-0 is akin to a full backup and the level-1 picked up changes made since the level-0 (or the last level-1 unless you specify that it should be cumulative). If you’ve done a level-0 backup on half of your data files when the backup goes south and you’ve got a pretty large database then on the next backup attempt you don’t really want to back those guys up again. Why not just do the level-0 on the ones that remain. But there’s potential for misuse of the NOT BACKED UP SINCE TIME clause here. Let’s illustrate through an example.
RMAN> report need backup;
RMAN> retention policy will be applied to the command
RMAN> retention policy is set to redundancy 1Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/oradata/test2/system01.dbf
2 0 /u01/oradata/test2/sysaux01.dbf
3 0 /u01/oradata/test2/undotbs01.dbf
4 0 /u01/oradata/test2/users01.dbf
Okay, all of the data files need to be backed up so let’s do a level-0 incremental backup:
RMAN> backup incremental level 0 database;
Starting backup at 02-07-2013 11:52:02
...
Finished backup at 02-07-2013 11:53:40
Our strategy calls for weekly level-0 incrementals and daily level-1 incrementals so let’s simulate that by taking a level-1 backup of the database now.
RMAN> backup incremental level 1 database;
Starting backup at 02-07-2013 12:08:16
...
Finished backup at 02-07-2013 12:08:54
So we have a level-0 backup to serve as our baseline and a level-1 backup with changes made since the level-0 was performed. Now let’s try the NOT BACKED UP SINCE TIME clause but first we’ll introduce a “what-if” condition. What if the level-0 backup has gotten lost or deleted. We’ll simulate that in the file system by manually deleting those backup set pieces and then crosscheck in RMAN to synchronize the control file and make RMAN aware that they’re missing:
RMAN> crosscheck backup;
...
RMAN> delete expired backup;
Okay, RMAN now knows that we only have a level-1 incremental backup – not good obviously because you can’t restore your database from a level-1 backup alone. We’ll confirm that RMAN is informed of the situation:
RMAN> report need backup;
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
1 0 /u01/oradata/test2/system01.dbf
2 0 /u01/oradata/test2/sysaux01.dbf
3 0 /u01/oradata/test2/undotbs01.dbf
4 0 /u01/oradata/test2/users01.dbf
Now let’s test how our what-if condition causes confusion when combined with an improperly implemented NOT BACKED UP SINCE TIME usage. Let’s try doing a level-0 backup but use a time after the last level-0 backup.
RMAN> backup incremental level 0 database not backed up since time "to_date('02-07-2013 12:00:00','mm-dd-yyyy hh24:mi:ss')";
Starting backup at 02-07-2013 12:15:53
using channel ORA_DISK_1
skipping datafile 1; already backed up on 02-07-2013 12:08:51
skipping datafile 2; already backed up on 02-07-2013 12:08:51
skipping datafile 3; already backed up on 02-07-2013 12:08:51
skipping datafile 4; already backed up on 02-07-2013 12:08:51
Finished backup at 02-07-2013 12:15:54
RMAN is not going to back up the files even though we stated that we want a level-0 backup. The trouble is that RMAN assumes the time we specify is directly related to the incremental level we specified. Even though it knows the files need to be backed up in order to obey the backup retention policy, it blindly skips the files because of the level-1 backup we performed.
Now let’s try a database restore:
RMAN> restore database;
Starting restore at 02-07-2013 12:18:49
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 02/07/2013 12:18:49
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 5 found to restore
RMAN-06023: no backup or copy of datafile 4 found to restore
RMAN-06023: no backup or copy of datafile 3 found to restore
RMAN-06023: no backup or copy of datafile 2 found to restore
Our backups are useless!
What is the lesson here? The lesson is that even though RMAN is a great tool and frees the DBA from a lot of responsibility with regard to backup retention, it can also present the occasional pitfall if you’re not careful. If you use the NOT BACKED UP SINCE TIME clause in conjunction with an incremental backup strategy, make sure the time you use corresponds to the specific level of incremental backup that you performed. As this example shows if you make the mistake of using the wrong time you might wind up with egg on your face.
Finally, we should note here that testing your backups and disaster recovery strategy is not to be taken lightly. If there is one thing a DBA can get fired over, it is losing data. If you are not totally comfortable with your backup solution and need help with testing or if you would just feel better about getting another pair of eyes on your strategy, please give us a call at 415-344-0500 (ext 501) or email info@dbspecialists.com. We would be happy to help.
Jay Stanley, Sr. Staff Consultant
Introduction
Recently, I was doing some work with OpenOffice (OO), and I noticed a part of OO that I wasn’t aware of - the Base database. Base is similar to Microsoft Access, though it of course lacks some features and polish. It does come with the ability to interact with several different databases, the Oracle RDBMS among them. It has a tables section, where you can design and work with tables; a Queries section which has several ways to interactively build queries, a Forms section where you can create a front-end form for a database table or set of tables, and a Reports section for creating reports.
I was curious – just how good is Base, what is it good at, and is it worth bothering with when using it with Oracle?
Installation of Oracle JDBC library
Before Base can connect to your Oracle database, OO needs to be configured to use the Oracle JDBC driver. Do do this, first figure out which version of Oracle you are using. Then, go to the Oracle website to retrieve the proper JDBC thin-client - at this time it can be found athttp://www.oracle.com/technetwork/database/features/jdbc/index-091264.html – though this may change in the future. In my case, this file was called ojdbc6.jar – for me, this was already installed as part of a thick-client. In OO, go to Tools then options – then under the OpenOffice.Org tree find the ‘Java’ node. Click on theClass Path... button on the right, and then click on ‘Add Archive’. Select your ‘ojdbc6.jar’ (or other Oracle JDBC library), and then click ‘Open’ to add it.
You will need to shut down & restart OO in order for this to work. If it is not installed correctly, you will get error messages when trying to add a connection to your target database.
Setting up a connection to your Oracle database
You will require network access, with proper credentials, in order to connect to the Oracle database. I would suggest that while testing, you should be using a test account in a database with no write access to your ‘main’ accounts for safety.
As this is using Oracle JDBC, you will need to know the:
- username,
- password,
- service-name, and
- IPV4 network address
for your target database.
I’ve found that working with OO connections with Oracle go much faster if the account that is being connected to does not have any DBA, or ’select any table’ privileges. When you first connect and periodically after that, OO will query the data dictionary to see what tables can be accessed. If this includes most or all tables in the database, it will cause this step to be quite slow, loading information from hundreds of tables.
When you first run Base, a Database Wizard dialog box is invoked so that the connection information can be configured. Click on Connect to an Existing Database, and change the connection type to Oracle JDBC, then click Next. Enter the Database Name, the Server (where you can enter the IP address if you like), the Port. Click onTest class – this tests to insure that the Oracle JDBC driver can be loaded. if this returns an error, something is wrong with the installation of the JDBC library above. Then, click on Next. At this stage you’ll need to enter your username/password. You can click on Test connection. If you get The connection was established successfully then it has been configured correctly. For future connections, just use the Open an existing database file option on the dialog box that pops up when it starts, to connect to your previously configured database.
Tables
Once you have connected, if you have used Microsoft Access before, things should be pretty familiar. Just like MS Access, you can use a wizard to create new tables. It has quite a few tables pre-defined – for example Customers and TimeBilled would could be quite handy – especially if what you are storing is common. However, I ran into an error when trying to create these pre-defined tables (”Missing closing parentheses”), so the wizard may not work.
Using the design view, though, worked fine for a test table I created.
Queries
There are three ways to define queries – using the wizard, using Create query in Design View, and Create query in SQL View. These options allow you to create queries pretty easily – the Create query in Design View allows you to visually add tables, then define join conditions, and finally to figure out what fields are needed. Obviously, if you are using more advanced SQL (including nonstandard Oracle syntax) then you’ll need to use the Create query in SQL view. Note: I did get some errors when working with queries (”Data cannot be loaded”), so you may encounter some issues here.
Forms
Forms allows you to create data entry/view forms. From my testing, it is not at all as polished or capable as MS Access, but it does work. I was able to create a data-entry form for a simple table just by using the wizard alone in just a few clicks. I was suprised to see that it does support major/minor forms, or forms/sub-forms.
Reports
Reports allows you to define reports visually. The wizards have a few predefined looks that personally I didn’t like that much, but luckily you can define reports using the design view without the wizard, though.
Other ways of interacting with OO
Similar to Microsoft Office products, OO does have a well-documented API. You can control OO from a variety of languages, including C, Perl, and Python among others. In fact, you can actually programatically create OO documents. So at least in theory, you could programatically run Base reports from a batch job.
Conclusion
OO Base is an interesting project – it definitely does have some rough edges (bugs), at least when interacting with Oracle databases using the JDBC thin driver. My testing was done with version 3.3 – I think I plan to check back in a bit to see if these bugs have been fixed. It may have a use as a query-builder and in querying the database, though these features are in Oracle Developer and Tora as well as most other modern IDEs.
Gary Sadler, Sr. Staff Consultant
Every DBA should include Datapump, Oracle’s cross-platform data migration utility, in his toolbox, especially since the legacy export utility is slated for obsolescence. Datapump expdp has a variety of uses, from database upgrades to QA refreshes and logical backups. A great place to start is with this script which has everything you need to do a full, consistent database export including data or just metadata. Of course we’ve taken care of the part of scripting that everyone loves, error handling. If we can advise on the best ways to use Datapump and take care of implementation for you, please contact us immediately.
Jay Stanley, Sr. Staff Consultant
About 10 years ago, Oracle Support released a completely new version of their support website. The previous one was based on straight HTML, served by what appears to be plsql-server pages, aka Oracle HTMLDB, aka the ‘htp’ package. It was quite functional, but it did look a bit dated, and I’m sure there were features that product management wished for, but simply couldn’t do because of the technology.
At the time, I was pleased that Oracle decided to release a new version, but this sense of well-being was turned on its head when I realized that it exclusively used Macromedia Flash technology. The 1st release required Flash, and would not work without it. I also frankly didn’t like their UI design; to me, it was cluttered, confusing at 3:00am in the morning, and the workflow – say for filing an SR – could have been better-done.
Oracle database administrators tend to be very concerned about security in general for obvious reasons. We also tend to have workstations that have at least partial access to the databases that we administer – often full access – and so we need to be extra-careful with regards to workstation security. Macromedia Flash has been ridden with extremely serious security bugs from the time it was released throughout its history. Requiring the workstation used by DBAs to have Flash installed really made no sense to me.
I actually doubt if there was ever a day in history, in which there were no known high-level vulnerabilities to Flash. It was for this reason that I really didn’t approve of Oracle using Flash as part of the technology stack – especially for their support website.
At the time, I did complain to them, and I’m sure others did as well. Soon after, they did come out with an HTML-only website that, though it wasn’t as functional as the Flash version, did well-enough. I still didn’t really like the UI, though.
I’ve learned that Oracle has released a new version of their support website recently for mobile clients, like cellphones/tablets. If you go to https://support.oracle.mobi, you’ll be directed to that website.
What’s nice, is that it actually works quite well even for full-sized browsers! It turns out that I’m using the mobile version far more often than the normal version for full-sized browsers; the layout is extremely simple and easy to use, it’s very very fast, and when you find an interesting article it displays it more clearly. It also doesn’t clutter up your display with a lot of options that are rarely if ever needed, and you can shrink the window quite small on a laptop screen to occupy less space while keeping it legible.
The new site isn’t using HTML5 responsive web design; it’s a completely different website. However, it does work quite well.
I’d suggest that if you do have a support contract with Oracle, to try out their new mobile site!
The address is: https://support.oracle.mobi
Mike Dean, Sr Staff Consultant
Managing database user accounts in a large enterprise can be very challenging. Creating individual accounts for every person in every database can be very time-consuming for DBAs and managing different passwords in every database is time-consuming for both DBAs and users. When people leave the enterprise, it is critical to remove their access immediately but this can be difficult if you have many databases. However, the cost of not properly managing these accounts is that your databases are more vulnerable to security breaches. Oracle offers a solution to this in the form of Enterprise User Security (EUS) which is part of the Oracle Identity Management product. With EUS, user accounts are created and managed in an LDAP repository called Oracle Internet Directory (OID). OID is responsible for authenticating the users as well as managing the access rights of each user.
I recently wrote a white paper called “Implementing Oracle 11g Enterprise User Security” that explains how to install and configure EUS. In this paper, I will give step-by-step instructions on how to install the software components required to run Oracle Identity Management and also how to implement Enterprise User Security to handle a typical real-world scenario in which different people need different levels of access to a number of databases.
You can find it here: http://www.dbspecialists.com/presentations.html#implementing_oracle_11g_enterprise_user_security
I hope you enjoy it. Please feel free to give feedback
Mike Dean, Sr Staff Consultant
Using a Physical Standby database (aka Data Guard) is one of the most effective ways to protect your database and improve its availability. My colleague Jay Stanley wrote a great blog on this subject: http://www.dbspecialists.com/blog/database-backups/need-uptime-use-and-oracle-physical-standby-database/.
The Standby database can be activated in the event of a disaster or it can be done for maintenance reasons, such as upgrading the host OS. In this case, you can do a switchover to the Standby, upgrade the Primary host and then switch back to the Primary, all with potentially just a few minutes of downtime and zero data loss. A failover occurs when the Primary becomes unavailable and the Standby needs to be used for production operations. This is not typically a planned exercise and is done after some sort of disaster renders the Primary unavailable. This could result in data loss, depending on the protection mode that has been configured.
In this blog, I will demonstrate how to accomplish several common tasks for maintaining Physical Standby databases, including how to do both a switchover (for maintenance) and a failover (for disaster). There are actually several different ways to accomplish these tasks: Grid Control, Data Guard command line (DGMGRL) and SQLPlus. Because of the nature of the remote DBA work that I do, I like to use SQLPlus as it is the one tool that I can count on at any client site.
I highly recommend that you practice all of these commands in a non-production environment. All of this was done using 11.2.0.2 Enterprise Edition on 64-bit linux.
Start a Standby Database in Redo Apply Mode with no delay
startup mount;
alter database recover managed standby database disconnect;
Start the Standby Database in Redo Apply Mode with a 120 minute delay
startup mount;
alter database recover managed standby database DELAY 120 disconnect;
Start the Standby Database in Real-Time Apply Mode
In order to use Real-Time Apply, the Standby database must have Standby Redo Logs. For more details, see Oracle Support Note: 828274.1 Data Guard Real-Time Apply FAQ.
startup mount;
alter database recover managed standby database using current logfile disconnect;
Stop Redo Apply and open the Standby Database for Read Only access
alter database recover managed standby database cancel;
alter database open read only;
Perform a Switchover to a Physical Standby database.
Step 1: Make sure everything is ready
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the primary database.
Select switchover_status from v$database;
SWITCHOVER_STATUS
-----------------
TO STANDBY
1 row selected
A value of TO STANDBY or SESSIONS ACTIVE indicates that the primary database can be switched to the standby role. If neither of these values is returned, a switchover is not possible because redo transport is either misconfigured or is not functioning properly. Shutdown the application, disconnect all sessions and stop job processing. You want to ensure that there are no active transactions occurring in order to minimize the time it takes to switchover. If you have a delay configured, cancel and restart Redo Apply without it.
select delay_mins from v$managed_standby;
Note: According to the docs, this should show the delay but in my testing, this value was always zero. However, this message in the alert log indicates that there is a delay:
Media Recovery Delayed for 120 minute(s) (thread 1 sequence 23)
alter database recover managed standby database cancel;
alter database recover managed standby database NODELAY disconnect;
Check to see that the Standby has received and processed the latest archive log from the Primary. Run this query on the Standby and compare against the latest sequence generated from the Primary
select al.thrd "Thread",
almax "Last Seq Received",
lhmax "Last Seq Applied"
from (select thread# thrd, max(sequence#) almax
from v$archived_log
where resetlogs_change#=(select resetlogs_change# from v$database)
group by thread#) al,
(select thread# thrd, max(sequence#) lhmax
from v$log_history
where first_time=(select max(first_time) from v$log_history)
group by thread#) lh
where al.thrd = lh.thrd;
Thread Last Seq Received Last Seq Applied
---------- ----------------- ----------------
1 74 74
Run this on the Primary to see the latest sequence generated. The last sequence applied to the Standby should be within 1 or 2 of this number:
SQL> select thread#, sequence# from v$thread;
THREAD# SEQUENCE#
---------- ----------
1 75
If there is big difference between the last log generated and the last one applied, it could be because the Standby is not processing fast enough or there could be a gap in the Archive logs. You can check for this by querying v$archive_gap. You should resolve any such issues prior to doing the switchover.
Step 2: Convert the Primary to Standby:
alter database commit to switchover to physical standby with session shutdown;
shutdown abort;
startup mount;
Step 3: Convert the standby to Primary:
Query the SWITCHOVER_STATUS column of the V$DATABASE view on the standby database.
SELECT SWITCHOVER_STATUS FROM V$DATABASE;
SWITCHOVER_STATUS
-----------------
TO_PRIMARY
A value of TO PRIMARY or SESSIONS ACTIVE indicates that the standby database is ready to be switched to the primary role. If neither of these values is returned, verify that Redo Apply is active and that redo transport is configured and working properly. Continue to query this column until the value returned is either TO PRIMARY or SESSIONS ACTIVE.
alter database commit to switchover to primary with session shutdown;
alter database open;
Step 4: Start Redo Apply on the new physical standby database and any others that exist.
alter database recover managed standby database using current logfile disconnect;
At this point, the databases have switched roles. Confirm that the new Standby is receiving and applying logs from the new Primary. You can do the same steps again to reverse them back to their original roles. You should take a backup of the new Primary now.
Perform a Failover to a Physical Standby Database
Step 1: Flush any unsent redo from the primary database to the target standby database.
If the primary database can be mounted, it may be possible to flush any unsent archived and current redo from the primary database to the standby database. If this operation is successful, a zero data loss failover is possible even if the primary database is not in a zero data loss data protection mode. Mount, but do not open the primary database. If the primary database cannot be mounted, go to Step 2.
Issue the following SQL statement at the primary database:
alter system flush redo to <Standby DB Name>;
For <Standby DB Name>, specify the DB_UNIQUE_NAME of the standby database that is to receive the redo flushed from the primary database. This statement flushes any unsent redo from the primary database to the standby database, and waits for that redo to be applied to the standby database. If this statement completes without any errors, go to Step 3. If the statement completes with any error, or if it must be stopped because you cannot wait any longer for the statement to complete, continue with Step 2.
Step 2: Verify that the standby database has the most recently archived redo log file for each primary database redo thread and that there are no gaps.
Query the V$ARCHIVED_LOG and v$archive_gap view on the target standby database to obtain the highest log sequence number for each redo thread. If necessary, manually copy archive logs to the Standby and register them.
alter database register physical logfile '<full path and file name>';
Step 3: Stop Redo Apply and finish applying all received redo data.
alter database recover managed standby database cancel;
alter database recover managed standby database finish;
Step 4: Open the new Primary database
4a) If the “alter database…finish” statement in Step 3 fails, try to resolve the problem. If it cannot be resolved a failover can still be performed (with some data loss) by issuing the following SQL statement on the target standby database:
alter database activate physical standby database;
alter database open;
4b) If the “alter database…finish” completes without errors, then you can do a switchover with no data loss. This allows you the option of switching back to the original primary after you resolve whatever caused it to become unavailable.
alter database commit to switchover to primary with session shutdown;
alter database open;
At this point, you should take a backup of the new Primary database. If you have other Physical Standby databases, then you can restart the redo apply now.
Flashback a Failed Primary Database into a Physical Standby Database
In the event of a failover, the original Primary database will need to be rebuilt or flashed back before it can become a Standby. The following steps assume that a failover has been performed to a physical standby database and that Flashback Database was enabled on the old primary database at the time of the failover. This procedure brings the old primary database back into the Data Guard configuration as a physical standby database.
Step 1: On the new primary database, issue the following query to determine the SCN at which the old standby database became the new primary database:
Select to_char(standby_became_primary_scn) from v$database;
Step 2: Flashback and convert the database to a physical standby database.
Issue the following statements on the old primary database:
shutdown immediate;
startup mount;
flashback database to scn <SCN from Step 1>;
alter database convert to physical standby;
shutdown immediate;
startup mount;
alter database recover managed standby database using current logfile disconnect;
Step 3: Start transporting redo to the new physical standby database.
Configure the log_archive_dest_2 parameter and enable it:
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_n=ENABLE;
On the new standby database, you may also need to change the LOG_ARCHIVE_DEST_n initialization parameters so that redo transport services do not transmit redo data to other databases. Confirm that the new Standby is receiving and applying logs from the new Primary.
Here at Database Specialists, we all have many years of experience working with Standby databases. If you have questions or problems with your database, feel free to contact us and we will give you a hand.
Jay Stanley, Sr. Staff Consultant
Introduction
Early in my career, when I was first introduced to Unix, my mentor at the time recommended that I use a text editor called emacs, but also learn how to use vi as well. I personally like both editors, but I prefer emacs for coding. Over the decades, I’ve continued to use emacs; I use it for nearly everything work-related that doesn’t fit in a web browser these days. I use it for email (GNUS), writing presentations and papers (org-mode), and coding every possible language and system imaginable(perl-mode, SQLi mode, gnuplot-mode, various lisp-modes, and more). I’ve found that I’m usually much more productive using emacs than using a full-featured integrated development environment like Eclipse, SQL*Developer, or Toad except in some very few special cases. Over the years, there have been many changes and improvements to the original SQL mode and related pl-sql mode; these have all been superseeded with a new mode called SQLi mode. SQLi mode has support for most databases today, including Oracle and mySQL among many others.
In this quick tutorial, I’ll show how to set up emacs version 24.2.1 to work with an Oracle database, and what you can do with this setup.
What does SQLi mode do?
SQLi mode makes it easier to interact with sql*plus.
Developing and administering Oracle ends up going something like this:
- Write a script or SQL statement
- Start a sqlplus session in another window
- Test your script in the new window by invoking
@scriptname
- Look at the output to see what went wrong
- Fix script
- Switch back to your editing buffer, and repeat from step 3 above.
It’s not very fun, because of the constant switching between the terminal in which you’re running sqlplus, and the terminal you’re editing in. It’s also not fun having to retype the SQL file name over & over again. SQLi mode automates this so that you can send either the latest statement, or a highlighted region, or an entire buffer to sqlplus with just a few quick keystrokes. It gives you a very useful ability to re-execute previous bits of SQL you’ve used. Finally, it highlights and color-codes your SQL and pl/sql code which makes it very nice to code in – for example, it becomes very easy to see comments, as they show up in red.
SQLi mode comes automatically with Emacs v24, so there’s nothing extra that you need to install.
Finding more in-depth help about SQLi mode
Just enter M-x sql-help[return] to open a buffer with documentation about SQLi mode.
Configuring sql-mode to access an Oracle database
The easiest way to set it up is to execute configure-group for the SQL group; [escape] x customize-group [return] SQL [return], or M-x customize-group [return] SQL [return]. A buffer will be created that has some options you’ll probably want to change as follows:
| Option |
Change/description |
| Sql Database |
change to your target database name |
| SQL Oracle Program |
Full path to ’sqlplus’ |
| SQL Password |
oracle account password |
| SQL Product |
set to ‘Oracle’ |
| SQL Server |
database host (or localhost if using port forwarding) |
| SQL User |
oracle username |
Note that I’ve been able to get this working even on Windows 7, under emacs running under cygwin/X, with sqlplus installed under windows. Under that environment, the cygwin path to sqlplus will need to be configured for the SQL Oracle Program; for me it is /cygdrive/c/product/11.1.0/client_2/SQLPLUS.EXE.
If you do not wish to be prompted for login credentials, you can optionally set the (defalias 'sql-get-login 'ignore) in your .emacs initialization file.
If you do not have global-font-lock-mode set, you may wish to do that so that whenever you visit some SQL, it will automatically show up displayed in color.
Starting it up
First, start the *SQL* buffer, which actually runs sqlplus. Just type M-x sql-oracle[return] – unless you have set sql-get-login, it will prompt for the username,password, and database; if you have set the variables above they will be the defaults.
Normally, this *SQL* buffer is not directly manipulated; instead, start up a new buffer/file. M-x find-file[return] /home/jstanley/test.sql You’ll notice that it will automatically be put into SQL[Oracle] mode.
Then, start typing some SQL that you’d like to work with;
select object_name from user_objects;
You can then use C-c C-b to send this to your sqlplus session!
You can use the following key bindings to interact with the your sql process:
| Key binding |
What it does |
| C-c C-b |
sql-send-buffer |
| C-c C-c |
sql-send-paragraph |
| C-c C-r |
sql-send-region |
| C-c C-s |
sql-send-string |
| C-M-q |
prog-indent-sexp |
| C-c C-l a |
sql-list-all |
| C-c C-l t |
sql-list-table |
| C-c TAB |
sql-product-interactive |
If you get an error that says: No sql process started, then in your test.sql buffer, enter: M-x sql-set-sqli-buffer RET *SQL* RET
The sql-list-all command basically lists all of the objects in the USER_OBJECTS view.
The sql-list-table command lists only the tables owned by the current user.
Mike Dean, Sr Staff Consultant
In this blog I will take a look at the newest version of Oracle Enterprise Manager, officially known as Oracle Enterprise Manager Cloud Control 12c and will give step-by-step instructions on how to install and configure it. I wrote a similar blog for Grid Control which can be found here: http://www.dbspecialists.com/blog/database-monitoring/installing-11g-grid-control-on-linux.
The basic architecture of Cloud Control is the same as Grid Control. There is a central repository and one or more Oracle Management Servers (OMS) that coordinates data received from the hosts. There are agents on each monitored host that collect data about the targets on the host and send that data to the OMS. The agents are also responsible for executing any commands that are initiated from Cloud Control.
The concept of plug-ins has been around since before Cloud Control but they seemingly take on a more important role in the Cloud Control architecture. The core Enterprise Manager Cloud Control features for managing and monitoring Oracle technologies, such as Oracle Database, Oracle Fusion Middleware, and Oracle Fusion Applications, are now provided through plug-ins that can be downloaded and deployed using the new Self Update feature. This new “pluggable” framework enables Cloud Control to be updated with management support for the latest Oracle product releases, without having to wait for the next Cloud Control release to provide such functionality. For example, when a new version of Oracle Database is released, you can download and deploy the latest Oracle Database plug-in, which will include management support for the latest release. The four plug-ins that get installed by default are: Oracle Database, Oracle Fusion Middleware, My Oracle Support and Oracle Exadata. There are quite a few other plug-ins available as well, some for Oracle products and some not. For example, there is a plug-in for IBM DB2, Microsoft SQL Server and several EMC storage devices. You can see the complete list of plug-ins available at http://www.oracle.com/technetwork/oem/extensions/index.html#OEM12c
There are some new features in Cloud Control that may be nice to have, but nothing that stands out as something I need to have. It is really similar in functionality to Grid Control, which is not a bad thing because I have always liked Grid Control. You can see a complete list of the new features here: http://docs.oracle.com/cd/E24628_01/doc.121/e25353/whats_new.htm#CEGIFFGA
Step 1) Install Oracle Database 11.2.0.2 and create a database to be used for the Cloud Control repository.
Step 2) Download the Cloud Control software
Go to technet.oracle.com, click Download, Enterprise Management and then Oracle Enterprise Manager. Select Linux x86-64, Accept the license agreement and download all 3 files for Cloud Control 12 Release 2 (12.1.0.2). Unzip all 3 files into a staging location
Step 3) Install Cloud Control on the OMS host
From the staging location where you unzipped the files, execute ./runInstaller to bring up the Cloud Control Installation GUI. The first screen prompts you to enter your Oracle Support credentials so you can download and install the updates. If you have a valid Oracle Support contract, I suggest you take this option as it saves you the trouble of separately installing any recommended/mandatory patches. After selecting this option, the Installer connected to Oracle Support, downloaded patch number 14145094 and proceeded with the main installation.
The installation of Cloud Control is slightly different than Grid Control in that you don’t need to separately install WebLogic Server as Cloud Control now does that for you. In my case, however, I had WebLogic already installed for other reasons and I assumed that Cloud Control would use my existing installation. No such luck. Cloud Control requires its own installation of WebLogic. According to the installation guide:
You must ensure that the Oracle WebLogic Server 11g Release 1 (10.3.5) installed by the Enterprise Manager Cloud Control Installation Wizard or by you is dedicated for Enterprise Manager Cloud Control. You must not have any other Oracle Fusion Middleware product installed in that Middleware home. Enterprise Manager Cloud Control cannot coexist with any Oracle Fusion Middleware product in the same Middleware home because the ORACLE_COMMON property is used by both the products.
During the Cloud Control installation, it will also install the Cloud Control Agent and will prompt you to specify the install location. In my case, I installed Cloud Control at /opt/oracle/product/Cloud_Control/Middleware and the Agent at /opt/oracle/product/Cloud_Control/agent. It will also ask if you want to install any plug-ins in addition the 4 default ones.
After the installation is complete, the following URLs are available:
Cloud Control: https://mdlinux:7799/em
WebLogic Admin server: https://mdinux:7101/console
In order to stop or start Cloud Control on the OMS host, run /etc/init.d/gcstartup stop | start. You will be prompted for the unix password of the user that installed the software , usually oracle
Step 4) Install the agent on any additional nodes you want to manage
The Agent installation files come with the Cloud Control software. Use the “emcli” command to get the proper file for the platform, transfer that file to the new node and then install using the agentDeploy.sh script. There are other ways to install the agent as well but this is the way I did it. Note that “mdlinux” is the OMS host and “mdlinux-vm2″ is the host that I am adding
[oracle@mdlinux ]$ emcli login -username=sysman -password=oracle123
Login successful
[oracle@mdlinux ]$ emcli sync
Synchronized successfully
[oracle@mdlinux]$emcli get_agentimage -destination=/tmp -platform="Linux x86-64" -version=12.1.0.2.0
Platform:Linux x86-64
Destination:/tmp
=== Partition Detail ===
Space free : 387 GB
Space required : 1 GB
.
.
.
This created the file /tmp/12.1.0.2.0_AgentCore_226.zip on the OMS host which I transfer to the new host. After transferring, I unzip, edit the agent.rsp file and then run agentDeploy.sh
[oracle@mdlinux-vm2]$ unzip 12.1.0.2.0_AgentCore_226.zip
Archive: 12.1.0.2.0_AgentCore_226.zip
inflating: unzip
inflating: agentDeploy.sh
inflating: agentimage.properties
inflating: agent.rsp
extracting: agentcoreimage.zip
.
.
.
Edit the agent.rsp and specify values for
OMS_HOST=mdlinux
EM_UPLOAD_PORT=4900
AGENT_REGISTRATION_PASSWORD=oracle123
You can find the upload port by running “emctl status agent” from the $AGENT_HOME/bin (in my case /opt/oracle/product/Cloud_Control/agent/agent_inst) on the OMS server and looking for the port in the REPOSITORY_URL
Repository URL : https://mdlinux.localdomain:4900/empbs/upload
[oracle@mdlinux-vm2 ]$ ./agentDeploy.sh AGENT_BASE_DIR=/u01/app/oracle/product
RESPONSE_FILE=/tmp/agent.rsp
The last step failed so I manually secured the agent and added the targets:
Performing the agent configuration...
Executing command: /u01/app/oracle/product/core/12.1.0.2.0/oui/bin/runConfig.sh
ORACLE_HOME=/u01/app/oracle/product/core/12.1.0.2.0
RESPONSE_FILE=/u01/app/oracle/product/core/12.1.0.2.0/agent.rsp ACTION=configure
MODE=perform COMPONENT_XML={oracle.sysman.top.agent.11_1_0_1_0.xml}
RERUN=true
Configuration Log Location:
/u01/app/oracle/product/core/12.1.0.2.0/cfgtoollogs/cfgfw/CfmLogger<timestamp>.log
ERROR: Agent Configuration Failed SEVERE:emctl secure agent command has failed with status=1
I followed the below steps to manually secure and add the host and agent targets:
/u01/app/oracle/product/agent_inst/bin/emctl secure agent
/u01/app/oracle/product/agent_inst/bin/emctl start agent
/u01/app/oracle/product/agent_inst/bin/emctl config agent addinternaltargets
The agent is now installed on mdlinux-vm2 and the host can be monitored by Cloud Control. In order to stop, start or status the Agent on either the OMS host or a monitored host, run
$AGENT_HOME/bin/emctl status | stop | start agent
[oracle@mdlinux-vm2 ]$ /u01/app/oracle/product/agent_inst/bin/emctl status agent
Oracle Enterprise Manager Cloud Control 12c Release 2
Copyright (c) 1996, 2012 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 12.1.0.2.0
OMS Version : 12.1.0.2.0
Protocol Version : 12.1.0.1.0
Agent Home : /u01/app/oracle/product/agent_inst
Agent Binaries : /u01/app/oracle/product/core/12.1.0.2.0
Agent Process ID : 14366
Parent Process ID : 14322
Agent URL : https://mdlinux-vm2.localdomain:3872/emd/main/
Repository URL : https://mdlinux.localdomain:4900/empbs/upload
Started at : 2012-10-12 09:20:48
Started by user : oracle|
Last Reload : (none)
Last successful upload : 2012-10-12 10:57:53
Last attempted upload : 2012-10-12 10:57:53
Total Megabytes of XML files uploaded so far : 0.51
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0
Available disk space on upload filesystem : 36.81%
Collection Status : Collections enabled
Heartbeat Status : Ok
Last attempted heartbeat to OMS : 2012-10-12 10:57:11
Last successful heartbeat to OMS : 2012-10-12 10:57:11
Next scheduled heartbeat to OMS : 2012-10-12 10:58:11
---------------------------------------------------------------
Agent is Running and Ready
Step 5) Use Cloud Control to discover the targets
Log into Cloud Control as sysman
Click on Targets, then Hosts and you should see the new host listed
Click on Setup, Add Target, Add Targets Manually
Add Non-Host Targets using Guided Process
Target Type=Oracle Database, Listener and Automatic Storage Management
Add using Guided Discovery
Specify the host and click Continue
After it finishes with the Auto Discovery process, it will show you the database(s), listener and ASM instances on that host. Select whichever ones you want to add, specify the password for the dbsnmp user (make sure the account is not locked) and then Test Connection. If successful, click Next, then Next then Save.
You should now see the new targets in Cloud Control
|
|
Recent Comments