Tag: Database

Backup and Restore, Database, Errors and Bugs, Tools

DDBoost: sbtbackup: dd_rman_connect_to_backup_host failed

 , ,

A common error. It happens when the datadomain host or mtree is unreachable.
For the first situation, contact the OS/Network administrator. Is can be a firewall limitation, DNS miss (if using DNS hosting) or, in some cases, networks physically unreachable.

For the second case, try to [re]send user/pass to access datadomain:

Starting backup at 24-OCT-15
using target database control file instead of recovery catalog
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=191 instance=almdbdw_1 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Domain Boost API
allocated channel: ORA_SBT_TAPE_2
input datafile file number=00001 name=+DGMYDB/almdbdw/datafile/system.267.849463017
channel ORA_SBT_TAPE_1: starting piece 1 at 22-JUL-15
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_SBT_TAPE_1 channel at 10/24/2015 10:03:50
ORA-19506: failed to create sequential file, name="a4qcme1l_1_1", parms=""
ORA-27028: skgfqcre: sbtbackup returned error
ORA-19511: Error received from media manager layer, error text:
 sbtbackup: dd_rman_connect_to_backup_host failed
channel ORA_SBT_TAPE_1 disabled, job failed on it will be run on another channel

Sending user/password to acess data domain as follow and, after that, re-run the your action.

RUN {
ALLOCATE CHANNEL t1 TYPE SBT_TAPE PARMS 'BLKSIZE=1048576, SBT_LIBRARY=$ORACLE_HOME/lib/libddobk.so,ENV=(STORAGE_UNIT=$STORAGE_UNIT,BACKUP_HOST=$DATADOMAIN_HOST,ORACLE_HOME=$ORACLE_HOME)' FORMAT '%U-%d';
send 'set username $DDBOOST_USER password $PASSWORD servername $DATADOMAIN_HOST';
RELEASE CHANNEL t1;
}

Hugs!

Matheus.

Database, Errors and Bugs, Security, SQL | PLSQL

ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION

 , , , ,

An unexpected error, right?

SQL> CREATE PROFILE TEST_PROF LIMIT
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LIFE_TIME 180
PASSWORD_GRACE_TIME 30
PASSWORD_REUSE_MAX 15
PASSWORD_VERIFY_FUNCTION fnc_validation;
CREATE PROFILE TEST_PROF LIMIT
*
ERROR at line 1:
ORA-28004: invalid argument for function specified in PASSWORD_VERIFY_FUNCTION FNC_VALIDATION

That is a simple need. You have to use 3 parameters on function: username varchar2, password varchar2, old_password varchar2.

Matheus.

ASM, Database, Errors and Bugs

Adding datafile hang on “enq: TT – contention”

 , , ,

Yesterday a colegue asked me about “enq: TT – contention” event on his session that is adding a a datafile in a tablespace wich run out of space in a 11.1.0.7 Database.
I’ve faced this situation another time and decided to document it.

Oracle refer Bug 8332021 : CANNOT ADD A DF WHEN SESSIONS ARE REPORTING ORA-1653 ON 11.1.0.7 for this situation.

The pointing solutions are:
– “Apply Patch 8332021”
– “Alternatively, you can upgrade to 11.2.0.2 or higher as the patch is included in the 11.2.0.2 patch set.”

The not documented workaround (just for you, by Matheus :D) is:
– Cancel session adding datafile.
– Extend any datafile to resume sessions waiting (in resumable state).
– Readd datafile.
This extend action relieves the blocks and will allow you to add the datafile.

Hugs!

Matheus.

Database, Performance, SQL | PLSQL

DBA_TAB_MODIFICATIONS

 , , ,

Do you know the view “dba_tab_modifications”?
It’s very useful to know what has changed since the last stats gathering of a table and all decision/information that comes with… See the example below..

The only need is to run “dbms_stats.flush_database_monitoring_info” before cheking… take a look:

mydb> create TABLE matheus_boesing.test (nro number);
Table created.
mydb> begin
2 for i in 1..1000 loop
3 insert into matheus_boesing.test values (i);
4 end loop;
5 commit;
6 end;
7 /
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected
mydb> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES
---------------------- -------------- ---------- ---------- ----------
MATHEUS_BOESING test 1000 0 0

mydb> EXEC DBMS_STATS.GATHER_TABLE_STATS('MATHEUS_BOESING','test');
PL/SQL procedure successfully completed.
mydb> select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications where table_name ='test' and table_owner='MATHEUS_BOESING';
no rows selected

For more information: http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4149.htm

Have a nice day! 😀
Matheus.

ASM, Database, Errors and Bugs, Linux

ORA-15186: ASMLIB error function

 , , , ,

Almost a month away… My bad!
Here I go again, with a quick tip, that a passed today. Our kernel was ‘changed’ without advise and this began to happen:

ORA-15186: ASMLIB error function = [asm_init], error = [18446744073709551611], mesg = [Driver not installed]
ERROR: error ORA-15186 caught in ASM I/O path

The solution was is basically update the asmlibs, that is based on kernel version. For RHEL, the solution is well decribed here:

http://www.oracle.com/technetwork/server-storage/linux/asmlib/rhel6-1940776.html
https://access.redhat.com/solutions/315643

Just to remember: After the kernel change, a relink of your Oracle Home is higly recommended.

Have a nice day!
Matheus.

ASM, Database, Errors and Bugs, Linux, Tools

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.

Database, Errors and Bugs, RAC | Data Guard, SQL | PLSQL

ORA-02062: distributed recovery

 , , , ,

# Error/Alert

Errors in file /oracle/diag/rdbms/mydb/mydb2/trace/mydb2_reco_26083546.trc:
ORA-02062: distributed recovery received DBID e450df78, expected 0311e884

# Solution

begin
commit;
for d in (select local_tran_id from dba_2pc_pending) loop
dbms_transaction.purge_lost_db_entry( d.local_tran_id );
commit;
end loop;
end;
/

Matheus.

Errors and Bugs, Tools

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.

Database, Errors and Bugs, Linux, Migration | Upgrades | Patching

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.

Backup and Restore, Database, Migration | Upgrades | Patching, Tools

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.