Characterset again - this is beyond a joke now

So another week another characterset problem, this time it cleverly disguised itself to start with......

Here is what i was initially presented with

rman TARGET sys/password@DEMO AUXILIARY sys/password@DEMOS

run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel aux1 type disk;
duplicate target database for standby from active database
spfile
set cluster_database='false'
set db_unique_name='DEMOS'
set control_files='+DATA'
set db_create_file_dest='+DATA'
set db_recovery_file_dest='+FRA'
set fal_server='DEMOS'
set db_recovery_file_dest_size='45G'
set local_listener='(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = server)(PORT = 1521)))'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=DEMOS'
set log_archive_dest_2='service=DEMO async valid_for=(online_logfiles,primary_role) db_unique_name=DEMO_BW'
nofilenamecheck;
}

The standby starts to get created then comes back with:

ORA-19505: failed to identify file "+DATA/DEMOS/DATAFILE/o1_mf_msd_data_c708cz14_.dbf"
ORA-17502: ksfdcre:4 Failed to create file +DATA/DEMOS/DATAFILE/o1_mf_msd_data_c708cz14_.dbf
ORA-15124: ASM file name '+DATA/DEMOS/DATAFILE/MSD_DATA_STAGING??.452.929113961' contains an invalid alias name

So that looks like some rman/asm issue right.....

well no - turns out its characterset again

Looking back at the original database i was copying from - seems at some point someone went crazy on the keyboard - here is a look at DBA_TABLESPACES

select tablespace_name from dba_tablespaces where tablespace_name like 'MSD%'

MSD_DATA_STAGING??
MSD_DATA_STAGING

great......

A quick check shows me that this new tablespace is not used - probably because no-one could work out how to create things in it......

SQL> select distinct TABLESPACE_NAME from dba_segments where tablespace_name like 'MSD%'
  2  /

TABLESPACE_NAME
------------------------------
MSD_DATA_STAGING

So now we just want to drop the bad one - which i can just do right?

SQL> drop tablespace MSD_DATA_STAGING??;
drop tablespace MSD_DATA_STAGING??
                                *
ERROR at line 1:
ORA-00911: invalid character

no i can't - maybe double quotes can help?


SQL> drop tablespace "MSD_DATA_STAGING??";
drop tablespace "MSD_DATA_STAGING??"
*
ERROR at line 1:
ORA-00959: tablespace 'MSD_DATA_STAGING??' does not exist

And again no - so what to do?

Lets see what the tablespace name actually is

SQL> select tablespace_name,dump(tablespace_name) from dba_tablespaces where tablespace_name like 'MSD%';

TABLESPACE_NAME
------------------------------
DUMP(TABLESPACE_NAME)
--------------------------------------------------------------------------------
MSD_DATA_STAGING
Typ=1 Len=16: 77,83,68,95,68,65,84,65,95,83,84,65,71,73,78,71

MSD_DATA_STAGING??
Typ=1 Len=22: 77,83,68,95,68,65,84,65,95,83,84,65,71,73,78,71,239,191,189,239,19
1,189

So the two ?'s are actually 239,191,189 (whatever the hell that actually translates as).

So now what do i do.....?

We can;t drop the tablespace by id number (that only works for datafiles) so we have to drop it by name.....

PLSQL to the rescue!

This little code block fetches the 'bad' value into a variable and then just executes immediate the drop

  declare
  v_tbsname varchar2(50);
  begin
  select tablespace_name into v_tbsname from dba_tablespaces
   where tablespace_name like 'MSD%' and length(tablespace_name)=18;
  dbms_output.put_line(v_tbsname);
  execute immediate 'drop tablespace '||v_tbsname;
 end;




I run that and.......

SQL> /
MSD_DATA_STAGING??

PL/SQL procedure successfully completed.

Magic -it's gone!

And now the duplicate works.

Now i return to my safe place and await the next characterset issue - i know it's coming.....