Oracle OEM 13c Metric Collection Error..Or..”Show Me The Graphs!”

When implementing the OEM 13C version, we have a server where we house about 16 databases. Adding the first bunch of the databases went like a breeze, but when all was said and done, we had 3 databases which didn’t want to be monitored..Removing, adding again, all to no avail..

Long story short:

The error message:

Message=Metric evaluation error start – Received an exception when evaluating sev_eval_proc for:Target name = [DBNAME]_SYS, metric_name = Response, metric_column = Status; Error msg = Target encountered metric erros; at least one member in in metric error.

(Yes, there are typos in the error message..for once, their not mine ūüėČ )

Selection_001

It is not the agent, it is not the (shared) oracle home…it’s the specific database self:

In 13c cloud control, database target status shows metric collection error as below:
“Dynamic Category property error, Get dynamic property error, No such metadata, Computation of a critical dynamic property failed. Retries Completed”

Computing dynamic properties of the target fails with below error:

<AGENT_INST_HOME>/bin>./emctl reload agent dynamicproperties DBNAME:oracle_database
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD reload error:Target oracle_database.orcl is broken: 
Target {oracle_database.orcl} is broken: Dynamic Category property error,
Get dynamic property error,No such metadata - No valid queryDescriptor 
or executionDescriptor found for target [oracle_database.orcl$57] is 
broken: Dynamic Category property error,Get dynamic property error,
 No such metadata - No valid queryDescriptor or executionDescriptor 
found for target [oracle_database.DBNAME$66]

*Note: the dbname is Case Sensitive!

The dynamic properties are these:

There is no bundle_series column in dba_registry_history table for the problematic database as below:

SQL> desc dba_registry_history

Name Null? Type
----------------------------------------- -------- ----------------------------
ACTION_TIME TIMESTAMP(6)
ACTION VARCHAR2(30)
NAMESPACE VARCHAR2(30)
VERSION VARCHAR2(30)
ID NUMBER
COMMENTS VARCHAR2(255)

How to fix?

Fairly easy: just execute catbundle.sql on problematic database to ensure that bundle_series column exists in dba_registry_history table.

Example:

Log in as sys on the database.

SQL*Plus: Release 11.2.0.4.0 Production on Mon May 9 12:32:26 2016
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> @?/rdbms/admin/catbundle.sql
Enter value for 1: cpu
Enter value for 2: apply

PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

....<snip>....

SQL> COMMIT;

Commit complete.

SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/oracle/11.2.0.4/base/cfgtoollogs/catbundle/catbundle_CPU_DBNAME_APPLY_2016May09_12_32_44.log

SQL>

Or just check the dba_registry_history table again:

SQL> desc dba_registry_history
Name Null? Type
---------------------------------------------------------------------------
ACTION_TIME TIMESTAMP(6)
ACTION VARCHAR2(30)
NAMESPACE VARCHAR2(30)
VERSION VARCHAR2(30)
ID NUMBER
BUNDLE_SERIES VARCHAR2(30)
COMMENTS VARCHAR2(255)

Tadaa!!

Re-run the metric collection again:

$ ./emctl reload agent dynamicproperties DBNAME:oracle_database
Oracle Enterprise Manager Cloud Control 13c Release 1
Copyright (c) 1996, 2015 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------
EMD recompute dynprops completed successfully

Refresh the overview page in OEM, and all should be green..

 

 

Advertisements
Posted in Databases, Technical Stuff | Tagged , , | 6 Comments

Integrate OEM 13c With MS Active Directory..Or..”One Account To Rule Them All…”

There are some instructions on the internet floating around in order to integrate the OEM 13c (or basically Weblogic 12c) and OEM 13c with Microsoft Active Directory/LDAP.

However in our case things went not as smoothly as advertised (Duh!). We have two OEM installations (OEM 12c and OEM 13c) in our company, but using the same AD. One would expect to just copying the settings from the working installation (OEM 12c) to the new OEM (13c) and everything would be peachy..Alas…This was not so much what was happening on our site.

With the aid of Oracle Support (credit where credit is due!) we (they) where able to solve this issue..

Both for future reference and documentation I’m sharing the settings we needed to set to make the stack play together nicely..

I’m not going to re-write the excellent instructions provided by oracle with the video located here: Oracle AD instructions

When following the above instruction don’t work, continue with my experiences as a last resort before contacting Oracle Support..

Start with the basics and open the WebLogic Console in the browser:

Selection_013Log in, and navigate to the page indicated by the green bars:

Selection_014

The key is in the configuration parameters used (once again: duh!). Below are the settings which worked for our site:

 *note* anything NOT mentioned should be either default, or the same as in the tutorial video from oracle

Host:  <Domain Controller without domain name e.g. DC01 instead of DC01.domain.com >
Port:  389
Principal:  CN=XXXX,OU=Service Accounts,DC=company,DC=com
User Base DN:  DC=company,DC=com
All Users Filter:  <cleared/empty>
User From Name Filter: (&(sAMAccountName=%u)(objectclass=person))
User Name Attribute=sAMAccountName
 
Group Base DN:  OU=OEM,OU=Groups,OU=Global Accounts,OU=Company_Locations,DC=company,DC=com
All Groups Filter: <cleared/empty>
Group From Name Filter: (&(cn=%g)(objectclass=group))
Group Membership Searching= <select> limited
Max Group Membership Search Level=3
Static Group DNs from Member DN Filter=(&(member=%M)(objectclass=group))
Save

restart OEM with:
./emctl stop oms -all -force
./emctl start oms
This is what did it for us, hope it helps.

If not in your case…revert to Oracle Support or Google some more ūüėČ

Posted in Oracle Enterprise Manager, Technical Stuff | Tagged , | Leave a comment

Configure NetBackup For Oracle RAC..Or..”Back Me Up!”

When configuring Netbackup to backup a RAC database, we ran into the following error:

1/29/2016 3:28:02 PM - Info bphdb(pid=2717) INF - Database status is: SHUTDOWN
1/29/2016 3:28:02 PM - Info bphdb(pid=2717) INF - Database open mode is: NOT MOUNTED
1/29/2016 3:28:02 PM - Info bphdb(pid=2717) INF - ARCHIVE LOG mode is: UNKNOWN
1/29/2016 3:28:12 PM - Error bpbrm(pid=13636) from client <servername>: ERR - Script exited with status = -5407 <Database is in the wrong state (must be OPEN) for the requested action>
1/29/2016 3:28:12 PM - Error bpbrm(pid=13636) from client <servername>: ERR - bphdb exit status = 5407: Database is in the wrong state (must be OPEN) for the requested action
1/29/2016 3:28:14 PM - Info bphdb(pid=2717) done. status: 5407: Database is in the wrong state (must be OPEN) for the requested action
1/29/2016 3:28:14 PM - end writing
Database is in the wrong state (must be OPEN) for the requested action(5407)

In Netbackup the node was automagically picked up by the Netbackup client, which reads the /etc/oratab. In the oratab this database was known by it’s SCAN name. However when trying to connect, the connection to the node is random due to the nature of RAC. When the backup starts, the check is failing, resulting in a somewhat wrong error message of the client.

Now the question is: how to fix this?

Delete all the references in Netbackup of this database (to avoid confusion, since the listing of the /etc/oratab will be propagated again by the client to the server in due time).

Make sure the node/instance name is NOT in the /etc/oratab.

+ASM:/oracle/11.2.0.4/grid:N # line added by Agent
dummydb0:/oracle/11.2.0.4/base/db/dbhome_1:N # line added by Agent

In this case: the instance is dummydb01, the SCAN name is dummydb0.

In Netbackup:

sshot01.png

Add the instance MANUALLY by providing the information above. Make sure to provide the credentials.

sshot02.png

sshot03.png

On the oracle database server:

Make sure the added INSTANCE is reachable by adding this to the listener.ora.

$ vi listener.ora

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(GLOBAL_DBNAME=dummy01)
(SID_NAME=dummydb01)
(ORACLE_HOME=/oracle/11.2.0.4/base/db/dbhome_1)
)
)

$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 29-FEB-2016 12:11:38

Copyright (c) 1991, 2013, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 12-OCT-2015 02:51:52
Uptime 140 days 10 hr. 19 min. 45 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/11.2.0.4/grid/network/admin/listener.ora
Listener Log File /oracle/base/diag/tnslsnr/<servername>/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xx.xx.xx.xx)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Instance "dummydb01", status READY, has 1 handler(s) for this service...
The command completed successfully

Configure the RMAN settings:

RMAN> show all;

using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DUMMYDB0 are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO 'SBT_TAPE';
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE SBT_TAPE PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE 'SBT_TAPE' TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';

Make sure the settings in bold are set.

The PARALLELISM is set to 1, further testing should show if the backups are faster by a higher number. For now we just want to get this proces to work.

For testing purposes, create a manual backup in netbackup. When this is finished, this backup is visible in RMAN:

RMAN> list backup summary;
<..>
6545 B 0 A SBT_TAPE 29-FEB-16 1 1 NO TAG20160229T115522
6546 B F A SBT_TAPE 29-FEB-16 1 1 NO TAG20160229T141956
6547 B A A SBT_TAPE 29-FEB-16 1 1 NO TAG20160229T142121
6548 B F A SBT_TAPE 29-FEB-16 1 1 NO TAG20160229T142157
6549 B F A SBT_TAPE 29-FEB-16 1 1 NO TAG20160229T142314
6550 B F A SBT_TAPE 29-FEB-16 1 1 NO TAG20160229T142351

RMAN>

Validate the backup (from the “tape”):

RMAN> run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=<nodeName),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=<netbackup_server>,NB_ORA_CLIENT=<nodeName>';
restore database validate;
release channel t1;
}

using target database control file instead of recovery catalog
allocated channel: t1
channel t1: SID=606 instance=dummbydb01 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 7.6 (2013111313)

sent command to channel: t1

Starting restore at 29-FEB-16

channel t1: starting validation of datafile backup set
channel t1: reading from backup piece bk_dDUMMYDB01_u9sqv4u1b_s8508_p1_t905082923

For more information about restoring, see the other post on my blog!

Posted in Databases | Tagged , , | Leave a comment

Java, SSHJ and EDDSA..Or…”Where Did I Left My Keys?”

For a long time I kept putting Oracle Database related posts, but as one can see in my profile, I also love to dabble with Java programming..While developing a custom way of getting an alert for a specific purpose (OEM let me down and I decided to have one created completely customized to my environment and because I love to program in my spare (oh, trust me, it’s SPARE) time..

Long story short: I needed ssh access to a machine (with Java) and chose to use sshj instead of the jsch with I have more experience with. but is kind of…outdated? Don’t get me wrong, it still does what’s advertised, but it’s documentation is kind of sparse to say the least..

After adding sshj-0.15.0.jar (along with the supporting libs) to my build-path I gave my litte setup a spin..

Supporting libs:
– slf4j-simple-1.7.14.jar
– slf4j-api-1.7.14.jar- bcprov-jdk15on-154.jar (optional)

and she-bang it went….

[main] INFO net.schmizz.sshj.common.SecurityUtils – BouncyCastle registration succeeded
Exception in thread “main” java.lang.NoClassDefFoundError: net/i2p/crypto/eddsa/spec/EdDSAParameterSpec
at net.schmizz.sshj.common.KeyType.<clinit>(KeyType.java:176)
at net.schmizz.sshj.transport.verification.OpenSSHKnownHosts$EntryFactory.parseEntry(OpenSSHKnownHosts.java:210)
at net.schmizz.sshj.transport.verification.OpenSSHKnownHosts.<init>(OpenSSHKnownHosts.java:69)
at net.schmizz.sshj.SSHClient.loadKnownHosts(SSHClient.java:616)
at net.schmizz.sshj.SSHClient.loadKnownHosts(SSHClient.java:596)
at org.blackbox.JTableSpaceCheck.units.remote.RemoteConnect.getTNSNames(RemoteConnect.java:31)
at org.blackbox.JTableSpaceCheck.JTableSpaceCheck.main(JTableSpaceCheck.java:21)
Caused by: java.lang.ClassNotFoundException: net.i2p.crypto.eddsa.spec.EdDSAParameterSpec
at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:331)
at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
… 7 more

I googled and googled, and although I did found the site with some java classes with the EDDSA encryption, and also found out, this was some encryption schema, I did not find the JAR file I could slam onto my project. Sure, I could download some source from a GITHUB site, and create a JAR myself, I would probably keep running in the missing ‘EdDSAParameterSpec’ file..since I couldn’t find this.

Until I found this site: I2P

On this site it is not really apparent, but, yes, the JAR file we (since you stumbled on this page) are looking for is hidden..

Download the package i2pinstall_0.9.24.jar (based on the platform of choice, I choose linux since I develop on this platform) and follow the instructions to “install” this file. I choose the graphical version, and unpacked the goodies in my homedir in a tmp folder.

Selection_002.png

After the installation (unpacking is more apt) the folder <install_loc>/i2p is created, and within this the folder lib. Navigate to this folder, and lo and behold: there is a JAR file called: i2p.jar.

This file is the one we need/want: it contains the EDDSA classes, including the spec file. Copy this file into the classpath to build the app, and the pesky errorstack will be gone!

It took me some searching, and since Google didn’t help me out in the speedy way it normally does, I hope to contribute in some small way to help you out..

Happy coding!

Posted in Java, Technical Stuff | Tagged , , | 2 Comments

Duplicate Controlfile To FRA (ASM)..Or..’Make It A Double!’

In a previous post we restored a database which was accidentally (a-hum) dropped from a Netbackup. In this procedure we focused on the restore, but this left us with a database running on only one control file. This is so called: not-best-practice, and since I was the one leaving the reader (yes, that would be you) with a potential dangerous situation, this post is created to remedy this.

Log into the database as sys and check the current status of the control file:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATADG/dummydb01/controlfile/current.309.901542237

SQL>

Yep. It’s for real…we are running on just one controlfile. Not good. Time to fix.

Note: Make sure you are running with a spfile. If not, start from a running instance and create one from memory and bounce and start from the spfile.

 

SQL> alter system set control_files='+DATADG/dummydb01/controlfile/current.309.901542237','+FRADG' scope=spfile;

SQL> shutdown immediate;

SQL> startup nomount;

This is needed since we defined a location (FRA) where no controlfile is yet to be found. Hence the database will not start.

Now connect RMAN to the database:

$ rlwrap rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 14:07:56 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DUMMYDB01 (not mounted)

RMAN> restore controlfile from '+DATADG/dummydb01/controlfile/current.309.901542237';

Starting restore at 19-JAN-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=926 device type=DISK

channel ORA_DISK_1: copied control file copy
output file name=+DATADG/dummydb01/controlfile/current.309.901542237
output file name=+FRADG/dummydb01/controlfile/current.1590.901548509
Finished restore at 19-JAN-16

Exit RMAN, and connect with SQL (as sys):

SQL> alter system set control_files='+DATADG/dummydb01/controlfile/current.309.901542237','+FRADG/dummydb01/controlfile/current.1590.901548509';

SQL> shutdown immediate;

SQL> startup;

SQL> startup
ORACLE instance started.
Total System Global Area 1.0689E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            3690989952 bytes
Database Buffers         6979321856 bytes
Redo Buffers               16900096 bytes
Database mounted.
Database opened.

Double check (since the startup confirms it already) the control_files:

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
+DATADG/dummydb01/controlfile/current.309.901542237
+FRADG/dummydb01/controlfile/current.1590.901548509

SQL>exit.

And this is one of the ways of doing this.

 

Posted in Databases, Technical Stuff | Tagged , | Leave a comment

Restore Oracle database from Netbackup..Or…”Gimme Back My Database!”

When switching over from regular FRA backups to Netbackup, things changed a bit in the way restores are done. At least at our site. The good news is, although the backup and scheduling is not the task of the DBA anymore, but of the storage/backup manager, the DBA still maintains control of the restore procedure.

Although it is kind of sad to read in the manual of Netbackup under the restore procedure: “Contact your DBA..”

So in order to fill in this “gap” in the documentation, this post will document the steps needed to restore a full database from a Netbackup.

Before attempting to restore the database make sure:

  • The control-file is also backed up and available in the backup-list.
  • There is enough space to hold the restored database.
  • The DBID is known (see other post to find the DBID from the backup).

The restore will be done on the machine where the database was located (we simulated a complete loss by dropping the database) and the user ORACLE will do this restore. This makes sure the environment is set correctly.

Step 1: Get the SPFILE from the backup.

Create a dummy pfile in the location $ORACLE_HOME/dbs:

$ vi initdummydb.ora

Paste the following into this file:

db_name='dummydb'
memory_target=1G
processes = 150
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest_size=2G
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
compatible ='11.2.0'

Save and close.

Startup the dummydb instance in nomount mode.

sql> startup nomount;

Since there is no spfile, the instance will use the pfile just created, and defined by the SID we have set.

Now we can either let the RMAN figure out which of the many files contain the spfile/controlfile, or we can provide a small hint, speeding things up a bit. As you may guess, the lookup through a lot of backups takes a long time.

Determine the file we need:

$ /usr/openv/netbackup/bin/bplist -C servername -t 4 -l -R /

-rw-rw---- oracle    dba          47972352 Nov 17 12:53 /c-1089834388-20151117-05
-rw-rw---- oracle    dba          47972352 Nov 17 12:52 /ctrl_dDUMMYDB01_uvbqmg5vk_s4075_p1_t896014324
-rw-rw---- oracle    dba          47972352 Nov 17 12:51 /c-1089834388-20151117-04
-rw-rw---- oracle    dba           9699328 Nov 17 12:50 /arch_dDUMMYDB01_uv9qmg5sb_s4073_p1_t896014219
-rw-rw---- oracle    dba          47972352 Nov 17 12:49 /c-1089834388-20151117-03
-rw-rw---- oracle    dba          16777216 Nov 17 12:48 /bk_dDUMMYDB01_uv7qmg5om_s4071_p1_t896014102
-rw-rw---- oracle    dba          47972352 Nov 17 10:09 /c-1089834388-20151117-02
-rw-rw---- oracle    dba          47972352 Nov 17 10:08 /ctrl_dDUMMYDB01_uv4qmfsd8_s4068_p1_t896004520
-rw-rw---- oracle    dba          47972352 Nov 17 10:07 /c-1089834388-20151117-01
-rw-rw---- oracle    dba          71565312 Nov 17 10:06 /arch_dDUMMYDB01_uv2qmfs9c_s4066_p1_t896004396
-rw-rw---- oracle    dba          47972352 Nov 17 10:05 /c-1089834388-20151117-00
-rw-rw---- oracle    dba         61254912K Nov 17 09:39 /bk_dDUMMYDB01_uv0qmfqlk_s4064_p1_t896002740

*UPDATE*: The file we need is the LATEST¬†/c-1089834388-20151117-05 file..otherwise we will run into errors like: “ORA-01152: file XX¬†was not restored from a sufficiently old backup”.

If the list is to extensive (we have one client backing up a lot of databases) try the following:

/usr/openv/netbackup/bin/bplist -C client_name -t 4 -l -R -s 01/18/2016 -e 01/18/2016 -I -keyword "*dummy*" /

This will provide a list of backups started and ended on 18th of January and have the case-insensitive¬† phrase “dummy”.

We need the /c-1089834388-20151117-05 file, so lets see if we can get this from netbackup:

RMAN > run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
restore spfile to '/tmp/spfile' from '/c-1089834388-20151117-05';
release channel t1;
}

In short: We create a channel to the net-backup media, send some info so the agent knows a bit about the environment and we ask politely to get the spfile from the auto-backup where we say it is.

Output:

allocated channel: t1
channel t1: SID=194 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 7.6 (2013111313)

sent command to channel: t1

Starting restore at 20-NOV-15

channel t1: restoring spfile from AUTOBACKUP /c-1089834388-20151117-05
channel t1: SPFILE restore from AUTOBACKUP complete
Finished restore at 20-NOV-15

released channel: t1
RMAN>

The spfile is now located in the /tmp location. This is not really necessary but since you are reading this blog, it is better to be safe than sorry and take small steps..¬† ūüėČ .

Copy the spfile to the $ORACLE_HOME/dbs location and rename it to the original database name, eg spfiledummydb01.ora

NOTE: If the spfile is not in the backup, just take a deep breath, but don’t panic. The spfile is not the biggest loss, just continue with a new one.

Now we have the database started in nomount mode with a dummy pfile, and a restored spfile.

Bounce the database, and make sure to use the restored spfile to start the db in nomount mode (ergo: delete the dummy pfile before starting the db).

Connect rman to the instance (nomount) since we don’t have the control files yet.

$ rlwrap rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 11:31:25 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DUMMYDB01 (not mounted)
RMAN>

Using the bplist from before, we are going to restore the controlfile also to the /tmp location:

run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
restore controlfile to '/tmp/controlfile.ctl' from '/c-1089834388-20151117-05';
release channel t1;
}

When using ASM diskgroups, you would use the command:

run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
restore controlfile to '+DATADG' from '/c-1089834388-20151117-05';
release channel t1;
}

The output would be something like:

using target database control file instead of recovery catalog
allocated channel: t1
channel t2: SID=926 device type=SBT_TAPE
channel t2: Veritas NetBackup for Oracle - Release 7.6 (2013111313)
sent command to channel: t1
Starting restore at 19-JAN-16
channel t1: restoring control file
channel t1: restore complete, elapsed time: 00:01:05
Finished restore at 19-JAN-16

released channel: t1
RMAN>

Note: Rome was not build in one day, and neither was this document..hence the leap in dates.. ūüėČ

The restored controlfile is located in the /tmp, or in the +DATADG diskgroup. In case of a /tmp restore, place the controlfile in the correct location and rename if needed. Just make sure to reflect these changes in the pfile/spfile later on.

Make sure to use the control file found in the +DATADG location!

Create a pfile from the spfile, shutdown the database, and alter the pfile to reflect the correct location of the control file;

*.audit_file_dest='/oracle/11.2.0.4/base/db/dbhome_1/admin/DUMMYDB01/adump'
*.cluster_database=FALSE
*.compatible='11.2.0.0.0'
*.control_files='+DATADG/DUMMYDB01/controlfile/current.309.901542237'
*.core_dump_dest='/oracle/11.2.0.4/base/diag/rdbms/DUMMYDB01/DUMMYDB01/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATADG'
*.db_domain=''
*.db_file_multiblock_read_count=8
*.db_name='DUMMYDB01'
*.db_recovery_file_dest='+FRADG'
*.db_recovery_file_dest_size=245722M
*.diagnostic_dest='/oracle/11.2.0.4/base'
*.log_buffer=16285696# log buffer update
*.memory_max_target=10G
*.memory_target=10G
*.open_cursors=3500
*.optimizer_dynamic_sampling=2
*.optimizer_mode='ALL_ROWS'
*.optimizer_use_sql_plan_baselines=FALSE
*.pga_aggregate_target=512M
*.plsql_warnings='DISABLE:ALL'# PL/SQL warnings at init.ora
*.query_rewrite_enabled='TRUE'
*.recyclebin='OFF'
*.remote_listener='servername:1521'
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_manager_plan=''
*.result_cache_max_size=41952K
*.sga_target=8G
*.skip_unusable_indexes=TRUE

SQL> startup mount pfile='/oracle/11.2.0.4/base/db/dbhome_1/dbs/initdummydb01.ora';
ORACLE instance started.

Total System Global Area 1.0689E+10 bytes
Fixed Size                  2262656 bytes
Variable Size            3690989952 bytes
Database Buffers         6979321856 bytes
Redo Buffers               16900096 bytes
Database mounted.

SQL> create spfile from pfile='/oracle/11.2.0.4/base/db/dbhome_1/dbs/initdummydb01.ora';
File created.
SQL>

*UPDATE* We can either restore until RMAN runs out of archivelogs and throws an error, or we just declare where to stop:

$ rlwrap rman target /
RMAN> list backup of archivelog all;

 List of Archived Logs in backup set 2449
 Thrd Seq Low SCN Low Time Next SCN Next Time
 ---- ------- ---------- --------- ---------- ---------
 1 5086 6491516245 28-SEP-16 6491926033 29-SEP-16
 1 5087 6491926033 29-SEP-16 6491926067 29-SEP-16

With this information from the control file, we know we can restore until 5088(!) and using thread 1.

Now to get the database files back:

$ rlwrap rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jan 19 12:45:51 2016
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUMMYDB01 (DBID=1089834399, not open)

RMAN> run {
allocate channel t1 type 'SBT_TAPE' parms 'ENV=(NB_ORA_CLIENT=node1),SBT_LIBRARY=/usr/openv/netbackup/bin/libobk.so64';
send 'NB_ORA_SERV=NBServer,NB_ORA_CLIENT=node1';
SET UNTIL SEQUENCE 5088 THREAD 1;
restore database;
switch datafile all; # Update control file with new location of data files.
recover database;
}

allocated channel: t1
channel t1: SID=992 device type=SBT_TAPE
channel t1: Veritas NetBackup for Oracle - Release 7.6 (2013111313)

sent command to channel: t1

executing command: SET until clause

Starting restore at 19-JAN-16
Starting implicit crosscheck backup at 19-JAN-16
Crosschecked 23 objects
Finished implicit crosscheck backup at 19-JAN-16

Starting implicit crosscheck copy at 19-JAN-16
Crosschecked 61 objects
Finished implicit crosscheck copy at 19-JAN-16

searching for all files in the recovery area
cataloging files...
cataloging done

channel t1: starting datafile backup set restore
channel t1: specifying datafile(s) to restore from backup set
channel t1: restoring datafile 00001 to +DATADG/dummydb01/datafile/system.302.896027315
channel t1: restoring datafile 00002 to +DATADG/dummydb01/datafile/sysaux.265.896026599
channel t1: restoring datafile 00003 to +DATADG/dummydb01/datafile/undotbs1.275.896027083
.....
channel t1: restoring datafile 00061 to +DATADG/dummydb01/datafile/some_data.314.896027867
channel t1: reading from backup piece bk_dDUMMYDB01_ud1qrgm1h_s4513_p1_t901273649

channel t1: piece handle=bk_dDUMMYDB01_uddqrnfhr_s4525_p1_t901496379 tag=TAG20160118T233938
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:05

starting media recovery

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=355
channel t1: reading from backup piece arch_dDUMMYDB01_udfqrnfms_s4527_p1_t901496540

channel t1: starting archived log restore to default destination
channel t1: restoring archived log
archived log thread=1 sequence=355
channel t1: reading from backup piece arch_dDUMMYDB01_udfqrnfms_s4527_p1_t901496540
channel t1: piece handle=arch_dDUMMYDB01_udfqrnfms_s4527_p1_t901496540 tag=TAG20160118T234220
channel t1: restored backup piece 1
channel t1: restore complete, elapsed time: 00:01:05
archived log file name=+FRADG/dummydb01/archivelog/2016_01_19/thread_1_seq_355.256.901545167 thread=1 sequence=355
channel default: deleting archived log(s)
archived log file name=+FRADG/dummydb01/archivelog/2016_01_19/thread_1_seq_355.256.901545167 RECID=10007 STAMP=901545168
media recovery complete, elapsed time: 00:00:01
Finished recover at 19-JAN-16
released channel: t1

RMAN>
Recovery Manager complete

Start the database in open mode, resetting archivelogs:

SQL> alter database open resetlogs;

Database altered.

SQL>

And that’s basically it. The database is restored, and ready to be abused.

Note: in this example the database runs on only one control file, make sure to create a backup one in the FRA before releasing the database to the users.

Also: in case of a missing SPFILE, now is the time to adjust the database to your liking, not when users are accessing the database. Most values are dynamic, but a few important ones are not.

Posted in Databases, Technical Stuff | Tagged , , | Leave a comment

Retrieve DBID from an Oracle Netbackup..Or..”What’s The Number Again?”

Of course we make backups. Never ever things go wrong…But when they do..It goes south real fast real soon…One of the things easily overlooked is to note down the DBID of the database. Not needed on a daily basis, but at the time a recovery this is a crucial part of the restore. But where did we left this number?

There are of multiple ways of finding this out, but let’s say we only have the backup created with Netbackup. And we have of course(!!) backed up the control file either with the autobackup option “on” or manual.

The easy solution is to retrieve the control-file name from the backup list in the Netbackup catalog. The reason for this is simple, the default name format for an RMAN control-file autobackup is c-IIIIIIIIII-YYYYMMDD-QQ, where: IIIIIIIIII is the DBID.

Go to the server where the backup agent is installed, and find the command: bplist.

In this case it is located in:

/usr/openv/netbackup/bin/bplist

Fire off the following command:

/usr/openv/netbackup/bin/bplist -C client_name -t 4 -l -R /

e.g.: when the backup was created on the host Node1, this is the name you need to fill in at client_name.

This command will provide a list of the backup files, including the control-file:

-rw-rw---- oracle    dba          47972352 Nov 17 12:53 /c-1089834388-20151117-05
-rw-rw---- oracle    dba          47972352 Nov 17 12:52 /ctrl_dDUMMYDB01_uvbqmg5vk_s4075_p1_t896014324
-rw-rw---- oracle    dba          47972352 Nov 17 12:51 /c-1089834388-20151117-04
-rw-rw---- oracle    dba           9699328 Nov 17 12:50 /arch_dDUMMYDB01_uv9qmg5sb_s4073_p1_t896014219
-rw-rw---- oracle    dba          47972352 Nov 17 12:49 /c-1089834388-20151117-03
-rw-rw---- oracle    dba          16777216 Nov 17 12:48 /bk_dDUMMYDB01_uv7qmg5om_s4071_p1_t896014102
-rw-rw---- oracle    dba          47972352 Nov 17 10:09 /c-1089834388-20151117-02
-rw-rw---- oracle    dba          47972352 Nov 17 10:08 /ctrl_dDUMMYDB01_uv4qmfsd8_s4068_p1_t896004520
-rw-rw---- oracle    dba          47972352 Nov 17 10:07 /c-1089834388-20151117-01
-rw-rw---- oracle    dba          71565312 Nov 17 10:06 /arch_dDUMMYDB01_uv2qmfs9c_s4066_p1_t896004396
-rw-rw---- oracle    dba          47972352 Nov 17 10:05 /c-1089834388-20151117-00
-rw-rw---- oracle    dba         61254912K Nov 17 09:39 /bk_dDUMMYDB01_uv0qmfqlk_s4064_p1_t896002740

So now we now the DBID of this database: 1089834388.

Posted in Databases, Technical Stuff | Tagged , , | Leave a comment