Creating RMAN Backup Catalog

It can soud repetitive, but always good to have notes about 🙂

  1. Create Schema for Catalog on CatalogDB:
-- Create the user
create user RMAN_MYDB identified by &PASS;
-- Grant/Revoke role privileges
grant recovery_catalog_owner to RMAN_MYDB;
-- Grant/Revoke system privileges
grant create session to RMAN_MYDB;

2. Create catalog and register database:

-- Conected to target Database via RMAN
RMAN> connect catalog rman_mydb/password@catdb.grepora.net:1521/catalogdb

connected to recovery catalog database

RMAN> CREATE CATALOG;

recovery catalog created

RMAN> REGISTER DATABASE;

database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

Well done!
Matheus.

Mount Diretory from Remote RHEL7 Server (NFS)

Quick Post: To mount a directory via NFS from a RHEL7 remote server:

Souce Host:

[root@sourcehost ~]# cat /etc/exports
/oracle/sharedir targethost(rw,no_root_squash,insecure)
[root@sourcehost ~]# /bin/systemctl restart nfs.service

* Note: The “/bin/systemctl” is the new by RHEL7. For other versions you can just use “service nfs restart”.

Target Host:

[root@targethost ~]# mkdir -p /sourcehost/sharedir
[root@targethost ~]# mount -t nfs sourcehost:/oracle/sharedir /sourcehost/sharedir
[root@srac-his ~]# df -h /sourcehost/sharedir
Filesystem Size Used Avail Use% Mounted on
sourcehost:/oracle/sharedir
100G 279M 100G 1% /sourcehost/sharedir

 

Have a nice weekend!
Matheus.

EXP-00079 – Data Protected

A quick one: I began to have this problem on 12c’s backup catalog schemas. The reason is that by now all information is protected by policies (VPD). The error:

EXP-00079: Data in table "&TABLE" is protected. Conventional path may only be exporting partial table.

The solution:

catalogdb> GRANT exempt access policy TO &exp_user;
Grant succeeded.

Hugs!
Matheus.

VKTM Hang – High CPU Usage

Today a database (RHEL 6, single instance, 11.2.0.4) suddently started to “explode” CPU on VKTM process (100% CPU).
After some minutes lost (completely) in support.oracle.com (there was just a few notes about binary permissions on Solaris), I decided to make a McGayver by myself. 🙂

By Oracle words: “VKTM acts as a time publisher for an Oracle instance. VKTM publishes two sets of time: a wall clock time using a seconds interval and a higher resolution time (which is not wall clock time) for interval measurements. The VKTM timer service centralizes time tracking and offloads multiple timer calls from other clients.

This way, my solution:

SQL> alter system set "_high_priority_processes"='LMS*' scope=spfile;
System altered.

And restart the database, of course.
So, VKTM is no more a “priority” process. The problem was solved. 🙂

Another possibility is to disable VKTM (undocumented parameter “_disable_vktm” – boolean). But I wanted to keep it running, changing less as possible of database configuration, just reducing priority.

KB:
Master Note: Troubleshooting Oracle Background Processes (Doc ID 1509616.1)
Great post about hidden parameters: http://oracleinaction.com/undocumented-params-11g/
Oficial one: http://www.orafaq.com/parms/index.htm

Hugs!
Matheus.

Charsets: Single-Byte vs Multibyte Encoding Scheme Issue

Sad history:

IMP-00019: row rejected due to ORACLE error 12899
IMP-00003: ORACLE error 12899 encountered
ORA-12899: value too large for column "SCHEMA"."TABLE"."COLUMN" (actual: 61, maximum: 60)

To understand: It happens when the export/import is being made by different charsets. Usually when the destination is a superset with “multibyting” and the source is a single-byte one. The reason is that as more as the charset is not specific, more bits are used to represent a charcter (c-ç, a-ã, o-õ-ô, for example), this way, the columns that uses as data length byte will be different sized between theese databases.

Of course, as more specific a charset configuration is, much better for performance constraints it’ll be (specially for sequencial reads), because the databases needs to work with less bytes in datasets/datablocks for the same tuples, in a simple way to explain. Otherside, this is a quite specific configuration. The performance issues are mostly related to more simple tunings (sql access plan, indexing, statistics or solution architecture) than this kind of details. But, it’s important to mention if you’re working in a database that is enough well tuned…

For more information, I recommend this (recent) documentation: https://docs.oracle.com/database/121/NLSPG/ch2charset.htm. Please, invest your time to understand the relation between “Single-Byte Encoding Schemes” and “Multibyte Encoding Schemes” in this doc.

The follow image ilustrates in a simple way the difference of byting used to address more characters (a characteristic of supersets):

nls81023

Ok, doke!
And the solution is…

Let’s summarize the problem first: The char (char, varchar) columns uses more bytes to represent the same characters. So the situations where, in the source, the column was used by the maximum lengh, it “explodes” the column lengh in the destination database with a multibyte encoding scheme.
For consideration, I’m not using datapump (expdp/impdp or impdb with networklink) just because it’s a legacy system with long columns. Datapump doesn’t support this “deprecated” type of data.
So, my solution, for this pontual problem occouring during a migration was to change the data lengh of the char columns from “byte” to “char”. This way, the used metric is the charchain rather than bytesize. Here is my “kludge” for you:

select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' MODIFY '||COLUMN_NAME||' CHAR('||data_length||' CHAR);'
from dba_tab_cols where DATA_TYPE='CHAR' and owner='&SCHEMA'
union all
select 'ALTER TABLE '||owner||'.'||TABLE_NAME||' MODIFY '||COLUMN_NAME||' VARCHAR2('||data_length||' CHAR);'
from dba_tab_cols
where DATA_TYPE='VARCHAR2' and owner='&SCHEMA';

 

And it works!
Hugs and see ya!
Matheus.

GrepORA at GUORS!

Hi all,
Last 12’th occoured in Porto Alegre-RS/Brazil the GUORS (Grupo de Usuários de Tecnologia Oracle Rio Grande do Sul – Local Oracle Technology Users Group) annual meeting.

The event counts with the follow speeches (and my personal opinion):

1. Oracle 12c New features by  Alex Zaballa – Oracle ACE Director:
A quick view of 12c new features (again). It was a good presentation, limited, however, by the short time to explore all possibilities… Particularly, I’m full of “new features” speeches of a product realeased more than 2 years ago, but Alex fortunatey bring some (fresh) news about 12.2 and his expectations about it. Considering his experience, it was a good time.

Alex share his slides here: http://www.slideshare.net/alex_zaballa
I’d like to share my basis link about new features (as you can expect, it’s on Oracle Base): https://oracle-base.com/articles/12c/articles-12c

2. SQL Tuning e Melhores Práticas (SQL Tuning Best Practices) by Gustavo Braga – DELL
A good and atractive speech by Gustavo about his experiences with SQL Tuning. It wasn’t a “database view” oriented speech, much more thinking about the Dev way. Gustavo shared his great experience with some tools he use by working in a quality-oriented company and some of his personal practices. It was a good reflection, always pertinent to the DBA daily challanges.

3. Oracle Maximum Availability Architecture by Carlos Pimentel – DELL
Much more about 11g new features considering the evolution to 12c about OMAA. Carlos pointed the relevance of services, scan_listener and the different uses you can make to an Active Dataguard. It can sound overdid, but we know, most of the legacy systems and small to medium companies are not compliance yet to this patterns. Carlos is an experienced DBA and his experiences improve the subjects of his speech.

 4. Oracle 12c – Data Redaction by Alex Zaballa – Oracle ACE Director
Another 12c speech of Alex Zaballa, this time about Oracle Data Redaction, a “security-oriented” new feature. Alex showed (hands on) how to configure and use the feature, however also showed how to “kludge/bypass” the masking with a simple SQL if the user has access to SQL (discovered by David Litchfield and published http://www.davidlitchfield.com/Oracle_Data_Redaction_is_Broken.pdf). So, it was nice to know that the features exists, but a little bit unusable, right?

Alex share his slides here: http://www.slideshare.net/alex_zaballa

IMG-20151112-WA0005.jpgPorto Alegre GUORS Meeting – November 12, 2015

So, it was a nice afternoon to share/learn some stuffs and to find frieds. We expect the community remains active and we’re here to help on it! 🙂

Have a nice day!
Matheus.

WebLogic AdminServer Startup stopped at “Initializing self-tuning thread pool”

After starting AdminServer, it remains with starting status and stopped writing in log file in:

Check the disk space used, to make sure that there are no partitions with 100% utilization, including /tmp.
After them, make sure the owner of the weblogic (oracle) has have write permission of “/tmp”

[root@app1xptoosb1 /]# ls -tlhr / |grep tmp
drwxr-xr-x 5 root root 4.0K Nov 15 09:11 tmp

If the owner of weblogic does not have write permission must be set, because the application server writes some temporary files in the directory:

[root@app1xptoosb1 /]# chmod 777 /tmp

[root@app1xptoosb1 /]# ls -tlhr / |grep tmp
drwxrwxrwx 10 root root 4.0K Nov 18 09:44 tmp

Jackson.

OGG-01934 Datastore repair failed, OGG-01931Datastore ‘dirbdb’ cannot be opened

After change GoldenGate 12c to ACFS filesystem, got eternal WARNING OGG-01931, even Datastore is created:
WARNING OGG-01931 Oracle GoldenGate Manager for Oracle, mgr.prm: Datastore ‘dirbdb’ cannot be opened. Error 2 (No such file or directory).

Complete solution steps to resolve the issue on SHARED FILESYSTEM:
1. Login to ggsci prompt and stop all OGG processes including jagent and manager
2. Run “delete datastore” command. Confirm delete of datastore
3. Run “CREATE DATASTORE SHM” command
4. Start all OGG processes; start manager, start *, start jagent

ON GGSCI CREATE DATASTORE SHM [ID n]
Indicates that the data store should use System V shared memory for interprocess communications.

Maiquel.

News about the Blog!

If you used to follow MatheusDBA blog, you may have noticed that the URL, name and categories changed this week. The reason is that the blog is passing to a new moment. But don’t worry, it’s just to become better than already is.

A few friends and I decided to join our blogs in one, and centralize the posts. As result, you can expect more frequently posts, different styles of posting and other subjects like GoldenGate, Fusion Middleware and Exadata covered more deeply.

Business-Process-Improvement1

Please, take a look on “About” section to more information about the change.
Also look at “Members” to know the new authors.

I’m going to continue blogging here, at GrepOra.com.
The MatheusDBA.com.br URL is now beeing used as my personal website.

I Hope you like it!
Cheers to new phase!

Best wishes,
Matheus.