Search This Blog

Thursday, September 21, 2017

RMAN: specification does not match any datafile copy in the repository

We copied one file from production Exadata to standby Exadata:

$ rman target / auxiliary sys@DWH_STB

Recovery Manager: Release 12.1.0.2.0 - Production on Thu Sep 21 14:26:52 2017

connected to target database: SPUR (DBID=496990843)
auxiliary database Password:
connected to auxiliary database: SPUR (DBID=496990843)

RMAN> run
2> {
3> allocate           channel prmy01 type disk PARMS="BLKSIZE=10485760";
4> allocate           channel prmy02 type disk PARMS="BLKSIZE=10485760";
5> allocate auxiliary channel stby01 type disk PARMS="BLKSIZE=10485760";
6> allocate auxiliary channel stby02 type disk PARMS="BLKSIZE=10485760";
7> backup as copy
8> datafile 790 auxiliary format '+RECOC1'
9> datafile 824 auxiliary format '+RECOC1'
10> ;

11> }

using target database control file instead of recovery catalog
allocated channel: prmy01
channel prmy01: SID=3035 device type=DISK

allocated channel: prmy02
channel prmy02: SID=53 device type=DISK

allocated channel: stby01
channel stby01: SID=509 device type=DISK

allocated channel: stby02
channel stby02: SID=762 device type=DISK

Starting backup at 21-09-2017 14:28:07
channel prmy01: starting datafile copy
input datafile file number=00790 name=+DATAC1/SPUR/DATAFILE/p2_proxy_log_2017.1088.937456961
channel prmy02: starting datafile copy
input datafile file number=00824 name=+DATAC1/SPUR/DATAFILE/p2_proxy_log_2017.517.937459103
output file name=+RECOC1/SPURSTB/DATAFILE/p2_proxy_log_2017.980.955290489 tag=TAG20170921T142807
channel prmy02: datafile copy complete, elapsed time: 00:02:45
output file name=+RECOC1/SPURSTB/DATAFILE/p2_proxy_log_2017.701.955290489 tag=TAG20170921T142807
channel prmy01: datafile copy complete, elapsed time: 00:03:15
Finished backup at 21-09-2017 14:31:23
released channel: prmy01
released channel: prmy02
released channel: stby01
released channel: stby02

RMAN> list copy of datafile 790;

specification does not match any datafile copy in the repository

RMAN> list copy of datafile 824;

specification does not match any datafile copy in the repository



Where are these two files ? Why such files are absent ?
Solution: Oracle don't register datafiles copied via auxiliary channels.
So you cannot find these files nether in prod or standby controlfiles.

And therefore you should remove these files manually.

Wednesday, September 6, 2017

ORA-06502: PL/SQL: character string too small

I did the migration of the application from IBM Power to Exadata.
Data were transferred using DataPump.
There were no serious errors after import. All software components were compiled after import.

But the the customer complained error: "ORA-06502: PL/SQL: character string too small"





1. 


One of reason of ORA-6502 was found quickly, the query

col PROPERTY_NAME for a30
col PROPERTY_VALUE for a36
select PROPERTY_NAME,PROPERTY_VALUE from database_properties order by 1;

showed difference in NLS_LENGTH_SEMANTICS between source and target databases.

Source database was NLS_LENGTH_SEMANTICS='CHAR', but on the Exadata database was ='BYTE'.

The command
alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both sid='*';
was completed successfully. And there were no errors in alert.log and in sqlplus session.
But ORA-6502 was appeared and  again !
It  disappeared after the database was recreated with NLS_LENGTH_SEMANTICS='CHAR' in parameter file.


But ORA-6502 don't disappeared again !

2.


We spend a week and found the 2nd underwater stone. It was the special PL/SQL parameter !
If you look into ALL_PLSQL_OBJECT_SETTINGS, then you'll see NLS_LENGTH_SEMANTICS column. This means, that oracle PL/SQL code depends of NLS_LENGTH_SEMANTICS settings !

The query showed:

select NLS_LENGTH_SEMANTICS from ALL_PLSQL_OBJECT_SETTINGS where owner=...

NLS_LENGTH_SEMANTICS

--------------
BYTE

Where the  'BYTE' become from ?

We did 
alter session set NLS_LENGTH_SEMANTICS='CHAR';
alter package T_BSCMS compile body;
and ORA-6502 disappeared !

So, problem was solved, but where the BYTE become from ?

After we moved rows with DataPump the customer admin copied and compiled some the package bodies using SQL Developer. 
As you guess, SQL Developer uses 'BYTE' as default setting !
Look: 

Tools -> Preferences -> Database -> NLS -> Length