(12c) RMAN-07539: insufficient privileges to create or upgrade the catalog schema

Another “The problem -> the fix” post. 🙂

# KB:
Upgrade Recovery Catalog fails with RMAN-07539: insufficient privileges (Doc ID 1915561.1)
Unpublished Bug 17465689 – RMAN-6443: ERROR UPGRADING RECOVERY CATALOG

# Problem

[oracle@databasesrvr dbs]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 21 14:17:09 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.
connected to target database: MYDB (not mounted)
RMAN> connect catalog catalog_mydb/catalog_mydb@catalogdb
connected to recovery catalog database
PL/SQL package CATALOG_MYDB.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is too old
RMAN> upgrade catalog
RMAN-00571: =========================================================
RMAN-00569: ============== ERROR MESSAGE STACK FOLLOWS ==============
RMAN-00571: =========================================================
RMAN-07539: insufficient privileges to create or upgrade the catalog schema
RMAN> exit

# Solution
– Connect on the catalog database with the 12c (local) OH:
(and don’t worry about the error on alter session).

[oracle@databasesrvr dbs]$ sqlplus sys/magicpass@catalogdb as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Tue Jul 21 14:21:02 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @?/rdbms/admin/dbmsrmansys.sql
alter session set "_ORACLE_SCRIPT" = true
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
alter session set "_ORACLE_SCRIPT" = false
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

– Then try to upgrade catalog again:

[oracle@databasesrvr dbs]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jul 21 14:21:27 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: MYDB (not mounted)
RMAN> connect catalog catalog_mydb/catalog_mydb@catalogdb
connected to recovery catalog database
PL/SQL package CATALOG_MYDB.DBMS_RCVCAT version 11.02.00.03 in RCVCAT database is too old
RMAN> upgrade catalog;
recovery catalog owner is CATALOG_MYDB
enter UPGRADE CATALOG command again to confirm catalog upgrade
RMAN> upgrade catalog;
recovery catalog upgraded to version 12.01.00.02
DBMS_RCVMAN package upgraded to version 12.01.00.02
DBMS_RCVCAT package upgraded to version 12.01.00.02.

Matheus.

Cloud Computing Assessment – Free

Hi folks!
I’ve been away a few days, right? My bad. I’m sorry.
But I have a good new. I’m preparing a new site where the content of this blog will be more efficiently allocated. Of course, the daily posts will continue. You’ll like it, I promise.

By now, I’d suggest you to make this assessment about Cloud Computing provided by Cloud-Institute.org.
The questions themselves generate some questions for reflection. Follow the link:

http://cloud-institute.org/cloud-open-exam.html

headerbild_open_exam

See ya!

Matheus.

ORA-15081: failed to submit an I/O operation to a disk

After some disk and a instance of RAC lost, the database was stuck with ORA-15081.
A recover was needed. #StayTheTip

# Error

dbsrvr:/home/oracle>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 29 19:51:37 2015
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.
Total System Global Area 1.3462E+10 bytes
Fixed Size 2239232 bytes
Variable Size 7214204160 bytes
Database Buffers 6241124352 bytes
Redo Buffers 4489216 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-15081: failed to submit an I/O operation to a disk

# Solution

SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.

Be happy with this! 😀

Matheus.

ORA-19751: could not create the change tracking file

Let’s make it simple to solve the problem:

# Error:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file: '+DGDATA/mydb/changetracking/ctf.470.859997781'
ORA-17502: ksfdcre:1 Failed to create file +DGDATA/mydb/changetracking/ctf.470.859997781
ORA-17501: logical block size 4294967295 is invalid
ORA-15001: diskgroup "DGDATA" does not exist or is not mounted
ORA-17503: ksfdopn:2 Failed to open file +DGDATA/mydb/changetracking/ctf.470.859997781
ORA-15001: diskgroup "DGDATA" does not exist or is not mounted
ORA-15001: diskgroup "DGDATA" does not exist or is not mounted

# Solution:

SQL> alter database disable BLOCK CHANGE TRACKING;
Database altered.
SQL> alter database open;
Database altered.

Then, after everything be OK, you fix the situation recrating a BCTF:

ALTER DATABASE ENABLE BLOCK CHANGE TRACKING USING FILE '+DGDATANEW';

MTFBWU!

Matheus.

ORA-27302: failure occurred at: sskgpcreates

# Error:

dbsrvr1:/home/oracle>srvctl start database -d mydb
PRCR-1079 : Failed to start resource ora.mydb.db
CRS-5017: The resource action "ora.mydb.db start" encountered the following error:
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpcreates
. For details refer to "(:CLSN00107:)" in "/grid/product/11.2.0.4/log/dbsrvr2/agent/crsd/oraagent_oracle/oraagent_oracle.log".
CRS-2674: Start of 'ora.mydb.db' on 'dbsrvr2' failed
CRS-2632: There are no more servers to try to place resource 'ora.mydb.db' on that would satisfy its placement policy

Seems the weeror is happening on dbsrvr2, right?
The doc below talks more about the error and the semaphores calculation:
Database Startup Fails with ORA-27300: OS system dependent operation:semget failed with status: 28 (Doc ID 949468.1)

Let’s make an adjust here:

[root@dbsrvr2 ~]# cat /etc/sysctl.conf |grep sem
kernel.sem = 250 32000 100 142
[root@dbsrvr2 ~]# vi /etc/sysctl.conf
[root@dbsrvr2 ~]# cat /etc/sysctl.conf |grep sem
kernel.sem = 250 32000 100 256
[root@dbsrvr2 ~]# sysctl -p

And try again:

dbsrvr1:/home/oracle>srvctl start database -d mydb
dbsrvr1:/home/oracle>

Well done! 😀

Matheus.

Database Migration/Move with RMAN: Are you sure nothing is missing?

Forced by the destiny to make a migration using backup/restore (with a little downtime), how to be sure nothing will be lost during the migration?
Here is a way: Create your own data just before migrating. 🙂

Seems like a kludge and it is.. haha.. But it works. Take a look:

# Original Database

SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict;
ORACLE instance started.
Total System Global Area 2689060864 bytes
Fixed Size 2229520 bytes
Variable Size 1996491504 bytes
Database Buffers 671088640 bytes
Redo Buffers 19251200 bytes
Database mounted.
Database opened.
SQL> create table matheus_boesing.migration (text varchar2(10));
Table created.
SQL> insert into matheus_boesing.migration values ('well done!');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> shu immediate;
SQL> exit;
$ rman target /
connect catalog rman_mydb/password@catalogdb
run { backup archivelog all;}

# Destination Database

$ rman target /
connect catalog rman_mydb/password@catalogdb
run { recover database;}
$ sqlplus / as sysdba
SQL> select count(1), to_char(CHECKPOINT_TIME, 'DD/MM/YYYY HH24:MI:SS') from V$DATAFILE_HEADER t
group by to_char(CHECKPOINT_TIME, 'DD/MM/YYYY HH24:MI:SS') order by 2;
COUNT(1) TO_CHAR(CHECKPOINT_
---------- -------------------
51 27/06/2015 22:15:28
-- All datafiles with synchronized headers...
SQL> alter database open read only; 
-- If needed, you can do more recover, this way...
Database altered.
SQL> select * from matheus_boesing.migration;
TEXT
----------
well done!
-- Means no more recover is needed :)
SQL> shutdown immediate;
SQL> alter database open resetlogs;
Database altered.

And be Happy! 😀

Matheus.

Sqlplus: Connect without configure TNSNAMES

Okey, you must to know, but is always useful to remmember that… If you don’t want to configure your TNSNAMES, you can connect directly to description of your database. This way:

sqlplus> conn matheus_boesing@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1531)))(CONNECT_DATA=(service_name=mydb)))
Enter password: ********
Connected.
sqlplus>

Based on this, I made two scripts, to connect with the sid (c.sql) or with the service_name (s.sql) and make my life easier. Here the scripts:

sqlplus>get c
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SID=&VSID)(server=dedicated)))'
5 disconnect
6 connect matheus_boesing@&&VDESC
7 set linesize 1000
8 set sqlprom '&&VSID> '
9 select instance_name, host_name
10 from v$instance;
11 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
12 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
13 UNDEFINE VDESC
14 UNDEFINE 1
15 UNDEFINE 2
16* UNDEFINE 3
sqlplus>get s
1 DEFINE VHOST = &1.
2 DEFINE VPORT = &2.
3 DEFINE VSID = &3.
4 DEFINE VDESC='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=&VHOST)(PORT=&VPORT)))(CONNECT_DATA=(SERVICE_NAME=&VSID)(server=dedicated)))'
5 prompt &VDESC
6 disconnect
7 connect matheus_boesing@&&VDESC
8 set linesize 1000
9 set sqlprom '&&VSID> '
10 select instance_name, host_name
11 from v$instance;
12 exec dbms_application_info.SET_MODULE('MATHEUS_BOESING','DBA');
13 alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
14 UNDEFINE VDESC
15 UNDEFINE 1
16 UNDEFINE 2
17* UNDEFINE 3
sqlplus>

It can be used like this:

sqlplus>@s mydb.domain.net 1531 mydb
(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=mydb.domain.net)(PORT=1531)))(CONNECT_DATA=(SERVICE_NAME=mydb)(server=dedicated)))
Enter password: ********
Connected.

Ok, but, let’s suppose you are working in a cluster and wants to connect directly to the another instance. I made the script below (ci.sql). It’s not beautiful, but is a lot hopeful:

sqlplus> get ci
1 DEFINE VINT = &1.
2 undefine VHOST
3 undefine VSID
4 VARIABLE VCONN varchar2(100)
5 PRINT ret_val
6 BEGIN
7 SELECT '@c '||host_name||' 1521 '||INSTANCE_NAME
8 INTO :VCONN
9 FROM gv$instance where INSTANCE_NUMBER=&VINT;
10 END;
11 /
12 set head off;
13 spool auxcon.sql
14 prompt set head on;
15 print :VCONN
16 prompt set head on;
17 spool off;
18* @auxcon
sqlplus>

As you see, you inform the inst_id you want to connect. It can be used like:

mydb> @instance
INSTANCE_NAME
------------------------------
mydb_2
mydb> @instances
INST_NUMBER INST_NAME
----------- ---------------------------------------
1 db2srvr2p.grepora.net:mydb_1
2 db1srvr1p.grepora.net:mydb_2
mydb> @ci 1
@c db2srvr2p.grepora.net 1521 mydb_1
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Enter password: ********
Connected.
mydb_1> @instance
INSTANCE_NAME
------------------------------
mydb_1

These scripts use to help me a lot on daily basis, and it’s exclusive.
I couldn’t find anything like this so far. So, I made it. 🙂

Matheus.

Windows: “ORA-12514” After Database Migration/Moving (Using DNS Alias)

It’s usual to use DNS Aliases pointing to scanlistener. This way, we create an abstraction/layer bewteen clients/application and the cluster where database is. Some activities like tierization/consolidation and database moving between clusters (converting to Pluggable, etc), would be much more transparent.

Buuuut, if after a database migration, all the services online and listening, your client is stucking with:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Remmember you are using DNS to make this layer. Have you tried to flush DNS Cache?
I faced this problem with a Windows Application. The solution:

C:\Users\matheus_boesing>ipconfig /flushdns
Windows IP Configuration
Successfully flushed the DNS Resolver Cache.

All working fine after that. 🙂

Matheus.

Create SPFILE on ASM from PFILE on Filesystem

Some basics, right?
Another thing that is not usual and everytime I do, someone be surprised: “shu” alias for “shutdown”:

SQL> create spfile='+DGDATA/MYDB/spfilemydb.ora' from pfile='/oracle/product/11.2/dbs/init_mydb.ora';
File created.
SQL> shu immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

The Bourleson Master also wrote about it. Take a look on a better detailed post about this subject: http://www.dba-oracle.com/concepts/pfile_spfile.htm.

Matheus.