Backup&Recovery(RMAN restore problem)

I took rman backup from RAC server to restore to another (new) RAC server.

 

This post is about some issues occurs face in RAC environment during the restore.

RMAN> restore database;

Starting restore at 09-OCT-15

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1.1

channel ORA_DISK_1.1: sid=108 instance=prod1 devtype=DISK

allocated channel: ORA_DISK_2.1

channel ORA_DISK_2.1: sid=107 instance=prod1 devtype=DISK

allocated channel: ORA_DISK_3.1

channel ORA_DISK_3.1: sid=106 instance=prod1 devtype=DISK

allocated channel: ORA_DISK_4.1

channel ORA_DISK_4.1: sid=105 (more...)

Network link in expdp

The export operation is performed with data that is consistent up to the specified SCN. If the NETWORK_LINK parameter is specified, then the SCN refers to the SCN of the source database.

Follow following steps to the export database using network-link.

Source database: demo

Target database: uat

Database platform : 11.2.0.4.0

Step 1 : Create a tnsnames.ora entry with source database.

demo =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL  (more...)

How to fix queries on DBA_FREE_SPACE that are slow

I found myself in a situation where OpsView a monitoring tool, was having difficulty monitoring the tablespaces for a particular pluggable database.

Upon investigation it was found the queries against the dictionary table DBA_FREE_SPACE were taking a very long time:

SQL> set timing on
SQL> select nvl(sum(dfs.bytes)/1024/1024,0) from dba_free_space dfs where dfs.tablespace_name = 'USERS';

NVL(SUM(DFS.BYTES)/1024/1024,0)
-------------------------------
 70.75

Elapsed: 00:00:10.98

There are 60 tablespaces in this pluggable database, which the time varied (more...)

Windows 10 Laptops

Teaching Oracle technology always has challenges. They’re generally large challenges because we ask students to run 4 GB Linux VM with Oracle Database 11g XE pre-configured for them. A number of the student computers aren’t up to the task of running the virtualization.

Installing VMware Workstation or Player and a 64-bit Linux operating system is the easiest way to discover a laptop that advertises itself as 64-bit when it truly isn’t. Most of the (more...)

Type Dependent Tree

While trying to explain a student question about Oracle object types, it seemed necessary to show how to write a dependency tree. I did some poking around and found there wasn’t a convenient script at hand. So, I decided to write one.

This assumes the following Oracle object types, which don’t have any formal methods (methods are always provided by PL/SQL or Java language implementations):

CREATE OR REPLACE TYPE base_t AS OBJECT
( base_id  NUMBER  (more...)

Substitutable Columns

Oracle’s substitutable columns are interesting and substantially different than Oracle’s nested tables. The benefit of substitutable columns is that you can create one for an object type or any subtypes of that object type. Unfortunately, you can’t create the same behavior with nested tables because Oracle’s implementation of collection types are always final data types and you can’t extend their behaviors.

The Oracle Database has three types of collections. Two are SQL scoped collection types (more...)

RegExp: Constraint to prevent spaces at the beginning or end.

Even though a space is a regular character, the client didn't want spaces at the beginning or end of a string. Any spaces in the middle were fine.
Of course this could be handled by the application, but it must also be implemented in the database. Using a check constraint with a regular expression will prevent the end user from entering unwanted data.

To try things out, let's just start with a simple table with (more...)

Rounding amounts, divide cents over multiple lines

In previous articles I wrote about dealing with a missing cent when you need to divide a certain amount over multiple lines. In these articles, links are at the bottom, I described a method to calculate the difference on the last row.
Then a question arose (as a comment):
What if for example i have 42 records and i wish to divide 100 by 42. I would get a rounded value of 2.38. If (more...)

Rounding Amounts, the missing cent: with the MODEL clause

Rounding amounts may lead to rounding-issues, I have written how this may be resolved in a previous blogpost using some analytic functions.
Playing around a little bit, I also came up with a method to resolve the rounding difference with the MODEL clause.

To create an example, first let's create a table with only three records in it.


SQL> create table t
2 as
3 select rownum + 42 id
4 from dual
5 connect (more...)

Conditional Compilation and Static Boolean

One of my pet-projects is LoggerUtil, which is a utility for Logger, which is an excellent logging tool for PL/SQL.
This post is not about Logger, but some dealings with Conditional Compilation.

With Conditional Compilation you can create a single code base to handle different functionalities depending on compiler flags.
The latest addition to LoggerUtil was a method to create a custom template. For this to work, LoggerUtil depends on a certain Logger Release (where (more...)

Deadlock with a Virtual Column

Virtual Columns are really cool. I like them a lot. If you've never heard of them, shame on you, learn about them.
In short: a Virtual Column is not a real column, it's an expression that looks like a column... more or less.
While using the Virtual Columns, we ran into a little oddity with them.

First of all let's start with the version of the database that I tested this on. Yes, I know (more...)

Creating Database Using DBCA Silent Mode

Creating database using DBCA silent mode? Learn how to provision a database in a non-interactive mode.

The post Creating Database Using DBCA Silent Mode appeared first on VitalSoftTech.

Splitting a comma delimited string the RegExp way, Part Three

The article read most often on this blog is called "Splitting a Comma Delimited String, the RegExp way".
On this blog there are two articles about this technique, one is about splitting up a single string into multiple rows and the other is about multiple strings into multiple rows.
Links to both articles are included at the bottom of this article.
It seems like there is a need for functionality like that frequently. And just (more...)

Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue!

Use DBMS_PARALLEL_EXECUTE to divide one huge task into multiple small tasks that can be executed at the same time. See how many ways are there to divide.

The post Want to SPEED Up Your Database Tasks? DBMS_PARALLEL_EXECUTE to the rescue! appeared first on VitalSoftTech.

Configure Oracle Exadata Write Back Flash Cache

In addition to improving read I/Os, Oracle Exadata Write back flash cache also provides the ability to cache write I/Os directly to PCI flash. Exadata storage software version 11.2.3.2.1 is the minimum version required to use write back flash cache. Grid infrastructure and database homes must run 11.2.0.3.9 or later to use with Write-back Smart […]

The post Configure Oracle Exadata Write Back Flash Cache appeared first on VitalSoftTech.

Oracle Database 10g Certifications to Expire in March 01, 2015

Oracle Database is one of the leading relational database management system and is used all over the world. It has various training’s, exams and certifications for its Database and other products. However due to regular update in Database, they have to redesign their trainings, certification and exams to add new features and to remove old […]

The post Oracle Database 10g Certifications to Expire in March 01, 2015 appeared first on VitalSoftTech.

Free e-book giveaway: “Oracle Database XE 11gR2 Jump Start Guide” (Packt Publishing, 2012)

Last week I reviewed “Oracle Database XE 11gR2 Jump Start Guide” (Packt Publishing, 2012) in this post. Packt guys did like the review, and today I am pleased to announce that I will be giving away two free e-copies of the book. All you need to do is just comment below the post and win [...]

Book review: “Oracle Database XE 11gR2 Jump Start Guide” by Asif Momen

About a year ago Packt Publishing approached me and offered to write a book on (one of the technologies in) Oracle 11g. After careful consideration I had to decline the offer. Which was the right decision as I have hardly had any spare time in the past 12 months. However, Packt guys have kept in [...]

Who is using your Undo space? – Improved Script

Add to Technorati FavoritesVer este articulo en Español

Hi folks!
I have extended the Undo usage scripts to include two additional indicators:
1) undo change vector size statistics
2) Used undo records/blocks

and support for RAC infrastructure, so you can spot the hungriest UNDO eaters for any given instance.

Then the script for Oracle 11g is as follows:
set pagesize 400
set linesize 140
col name for a25
col program for a50
col username for a12
col osuser for a12
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname  (more...)

Install Oracle Express XE 11gR2 on Oracle Linux 6.1 x64. (No bitmap index conversion on the fly!)

Some time ago, if you said you had installed Linux and, heavens forbid, Oracle on Linux, you’d be considered an utter geek. Fast forward 15 year. These days, when people (who have experience with other databases and operating systems) ask me about Oracle/Linux, I feel somewhat embarrassed. Let me illustrate what I mean. Install Oracle [...]