I like show a simple way, on Unix/Linux box, to set Window title when you are using Putty. As easy as use printf. It works fine with KSH (Korn Shell).
With the next statement we set “This is my session” like title;
printf "\033]0;This is my session\007"
HTH – Antonio NAVARRO
This morning I was inserting rows in a table with unique indexes, where it found a duplicate key (natural or composite) it abort the execution. I needed insert all rows excluding the rows, of course, maybe drop the index or filter the rows to insert can be a solution, but in my case the hint IGNORE_ROW_ON_DUPKEY_INDEX was the solution. It only work for insert statement. This hint cause the statement ignore a unique key violation and does not cause statement termination. Please let me show a example.
Create a new table with 3 rows;
dbp12> create table anr1 (col1 number);
Tabla creada.
dbp12> insert into anr1 values (1);
1 fila creada.
dbp12> insert into anr1 values (5);
1 fila creada.
dbp12> insert into anr1 values (6);
1 fila creada.
dbp12> select * from anr1;
COL1
__________
1
5
6
Create a second table with 3 rows, the first row have value 1.
dbp12> create table anr2 (col1 number); Tabla creada. dbp12> insert into anr2 values (1); 1 fila creada. dbp12> insert into anr2 values (2); 1 fila creada. dbp12> insert into anr2 values (3); 1 fila creada.
Show values for both tables, and perform commit;
dbp12> select * from anr1;
COL1
__________
1
5
6
Transcurrido: 00:00:00.29
dbp12> select * from anr2;
COL1
__________
1
2
3
Transcurrido: 00:00:00.51
dbp12> commit;
Confirmación terminada.
Create a unique index on the first table;
dbp12> create unique index anr1inx on anr1 (col1); Índice creado.
Now perform a insert/select command, the first value must fail and abort the execution;
dbp12> insert into anr1 select * from anr2;
insert into anr1 select * from anr2
*
ERROR en línea 1:
ORA-00001: restricción única (ANTONION.ANR1INX) violada
dbp12> select * from anr1;
COL1
__________
1
5
6
Transcurrido: 00:00:00.28
One more timen, now with the ignore_row_on_dupkey_index hint;
dbp12> insert /*+ ignore_row_on_dupkey_index (anr1,ANR1INX) */ into anr1 select * from anr2;
2 filas creadas.
dbp12> select * from anr1;
COL1
__________
1
5
6
2
3
In this case the values 2 and 3 have been inserted.
HTH – Antonio NAVARRO
Today I needed create an auxiliary instance to restore a pairs of datafiles, for this purpose minimal configurations is enough. Oracle documentations says that only one parameter to start (startup nomount in mi case) is db_name, the rest of parameters are setting on defaults. Please look at down the next text;
thunder-3@:$PWD> cat initreco.ora ### ### The only one parameter needed is db_name ### *.db_name='reco' thunder-3@:$PWD> sqlplus '/ as sysdba' SQL*Plus: Release 11.2.0.3.0 Production on Tue Aug 30 13:02:47 2016 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started. Total System Global Area 329990144 bytes Fixed Size 2158976 bytes Variable Size 255856256 bytes Database Buffers 67108864 bytes Redo Buffers 4866048 bytes
HTH – Antonio NAVARRO.
Today I get the next error registing a new database (12.1.0.5) in a RMAN catalog;
connected to target database: FINANCIAL (DBID=5127695875) connected to recovery catalog database PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old PL/SQL package RMAN.DBMS_RCVCAT version 11.02.00.04 in RCVCAT database is too old RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of allocate command at 08/26/2016 09:59:25 RMAN-06429: BCKCAT database is not compatible with this version of RMAN
This is a typical error when a new greater version database is including into the RMAN catalog. The solution is typical too, is very easy, only execute an upgrade command like show below;
RMAN> upgrade catalog; recovery catalog owner is RMAN enter UPGRADE CATALOG command again to confirm catalog upgrade RMAN> upgrade catalog; recovery catalog upgraded to version 12.01.00.05 DBMS_RCVMAN package upgraded to version 12.01.00.05 DBMS_RCVCAT package upgraded to version 12.01.00.05 RMAN> RMAN> RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete RMAN>
HTH – Antonio NAVARRO
Today I like show the way of see the last used block in a table (exten too) when we perform, by example, an insert, remember that if it have free blocks at the beginning or in the middle it would use them and not neccesary go to above the hwm (high watermark).
For this proposer we’re going to use the dbms_space.unused_space package.
From Oracle Doc we can see the description for the next fields, this attributes get us the data which we are looking for;
last_used_extent_ file_id -> Returns the file ID of the last extent which contains data.
last_used_extent_ block_id -> Returns the starting block ID of the last extent which contains data.
last_used_block -> Returns the last block within this extent which contains data.
The next script is a simple example of how it works, you need pass the table owner, and table name;
set serveroutput on
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space('&prop.','&tabname','TABLE',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('LAST USED EXTEN AND BLOCK');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);
end;
/
set serveroutput off
HTH – Antonio NAVARRO
Actually I’m upgrading a database (Release 9.2.5.0) to 12c, It is a small database because of this I prefer perform a (traditional) exp and imp that upgrade to a 10g and after that upgrade to 12c.
The problem I have found is when generate the dmp file, the file system have 20 gigas of free space and the dmp file size is greater. There are some solutions, in my case I selected compress the dmp file while it is generated using a unix pipe. Here I show the script;
orange-3@> cat dumper.sh
#!/bin/ksh
export ORACLE_SID=FINAN
export ORACLE_HOME=/oracle/server/925
DIRECTORY=/dummy; export DIRECTORY
FILENAME=${DIRECTORY}/fichero_grande.dmp.gz; export FILENAME
LOG=${DIRECTORY}/fichero_grande.log; export LOG
PIPE=${DIRECTORY}/export_pipe.dmp; export PIPE
test -p ${PIPE} || mknod ${PIPE} p
chmod +rw ${PIPE}
cat $PIPE | /usr/bin/gzip > ${FILENAME} &
${ORACLE_HOME}/bin/exp log=${LOG} buffer=104857600 file=${PIPE} owner=FINANCIAL << EOF
financial/xxxxxxxxxx
EOF
HTH – Antonio NAVARRO
I like to show the way to get the list the avaliable agents in Enterprise Manager Cloud 12C from command line, of course, it is very easy from graphical enviroment.
First of all we must log;
saturn> ${OMS_HOME}/bin/emcli login -username=’SYSMAN’ -password=’xxxxxxx’
after that using emcli and the option get_supported_platforms get the report;
saturn> ${OMS_HOME}/bin/emcli get_supported_platforms
———————————————–
Version = 12.1.0.5.0
Platform = HP-UX PA-RISC (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Microsoft Windows x64 (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Oracle Solaris on x86-64 (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Microsoft Windows (32-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Oracle Solaris on SPARC (64-bit)
———————————————–
Version = 12.1.0.5.0
Platform = Linux x86-64
———————————————–
Version = 12.1.0.5.0
Platform = HP-UX Itanium
———————————————–
Version = 12.1.0.5.0
Platform = Linux x86
———————————————–
Version = 12.1.0.5.0
Platform = IBM: Linux on System z
———————————————–
Version = 12.1.0.5.0
Platform = IBM AIX on POWER Systems (64-bit)
———————————————–
Platforms list displayed successfully.
HTH – Antonio NAVARRO
There are de basic commands for recover database when us are using third-party snapshots or storage snapshot (please remember not all san/nas storage providers support this feature, be careful). I posted about this funcionality before, maybe the best is you don’t need put the database in backup mode, I remember the old times when you need execute begin backup and end backup. Let me show the next commands;
For recover database using the last snapshot taken use;
recover database;
For recover database using a particular snapshot use;
recover database until time ’01/08/2016 17:00:00′ snapshot time ’01/08/2016 16:00:00′;
For incomplete recovery of database, using snapshot plus archive log (why not, can too be redolog file/s) use;
recover database until cancel snapshot time ’01/08/2016 16:00:00′;
HTH – Antonio NAVARRO
sql1 