Upgrading sn Migrating

 Study the Mike Dietricht’s blog, with a lot of videos, in particular Zero Downtime Migration tool (ZDM). :

https://mikedietrichde.com/

Itinerary to Data Scientist

This is the list of certifications I have followed on the path from DBA to data scientist:

Oracle Cloud Infrastructure 2021 Certified Architect Professional (1Z0-997-21)

Oracle Cloud Database Services 2021 Certified Specialist (1Z0-1093-21)

Oracle Cloud Database Migration and Integration Specialist (1Z0-1094–21)

Oracle Autonomous Database 2021 Specialist (1Z0-931-21)

Oracle Machine Learning using Autonomous Database 2021 Specialist (1Z0-1096-21)

Oracle Cloud Infrastructure Security 2021 Associate (1Z0-1104-21)

Oracle Cloud Platform Application Integration 2021 Specialist (1Z0-1042-21)

Oracle Cloud Infrastructure 2021 Certified Architect Associate (1Z0-1072-21) 1Z0-1042-21)

Oracle Cloud Infrastructure 2021 Certified Architect Professional (1Z0-997-21)

Oracle Cloud Database Services 2021 Certified Specialist (1Z0-1093-21)

Oracle Cloud Database Migration and Integration Specialist (1Z0-1094–21)

Oracle Autonomous Database 2021 Specialist (1Z0-931-21)

Oracle Machine Learning using Autonomous Database 2021 Specialist (1Z0-1096-21)

Oracle Cloud Infrastructure 2021 Certified Architect Associate (1Z0-1072-21)

Oracle Clourd Infrastructure 2021 Certified Architect Professional (1Z0-997-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=B210042D42359D33CB260E79DAE54808650FA4901D32CA89E7D8EEC3940EC3FA

Oracle Cloud Platform Application Integration 2021 Specialist (1Z0-1042-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=3F7FF037F3DBCFDE13DFB8E87735A396565AFC12F79E19B705F0EA58FEC274C2

————————————-

Oracle Cloud Infrastructure Security 2021 Associate (1Z0-1104-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=FA5557BF6EFD2F7F15FBB5FAF2B4791D20CD9A381CC22F24799B5264A76F4BA5

Oracle Cloud Database Services 2021 Certified Specialist (1Z0-1093-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=815B21E3AA29FB32D3E58748E12AC11010285923C28E3A328F8F1ED367B7E366

Oracle Cloud Database Migration and Integration Specialist (1Z0-1094–21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=BF1401EFB019FFFB05BDFB6C9E269997CC73995D4A29A2C07C30925F5F21F9C9

Oracle Autonomous Database 2021 Specialist (1Z0-931-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=E531555B94E028BC9D21327CBD99BC0AE9393A521CCD6E82E516AA8F8A4DECFB

Oracle Machine Learning using Autonomous Database 2021 Specialist (1Z0-1096-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=51995BB3D53949C1BBED2525FBDC5498E42AB63A76211AEC598D7DFA9769C380

Oracle Cloud Infrastructure 2021 Certified Architect Associate (1Z0-1072-21)

https://catalog-education.oracle.com/pls/certview/sharebadge?id=A0B291E7CBA58AB58A3DDCC627ED321D88B16F38B19C01D56995387ACD85C361

How to create a Data Guard Broker Configuration in Oracle 19c

 Here an article from my colleague Fernando:

https://rootfan.com/data-guard-broker-configuration-oracle-19c/#more-1677

And just another useful one, in a virtualized environment:

https://oracledbwr.com/step-by-step-guide-to-install-oracle-19c-rac-installation-using-virtualbox/

Oracle Clusterware 19C

Official documentation, all you need to know about all the architectures and possibilities of Oracle Clusterware 19C.

Here you will find Fleet Patching and Provisioning.

https://docs.oracle.com/en/database/oracle/oracle-database/19/cwadd/clusterware-administration-and-deployment-guide.pdf

DATAGUARD 19C

Official documentation, all you need to know about all the architectures and possibilities of Oracle Dataguard 19C.

https://docs.oracle.com/en/database/oracle/oracle-database/19/sbydb/data-guard-concepts-and-administration.pdf

Fleet Patching and Provisioning commands

Formerly “Rapid Home Provisioning (RHP)”, thats the reason for the command rhpctl.

This artefact consists of a server connected to an ACFS where clusterware and RDBMS binaries are stored for distribution and installation on client nodes.

The following is a summary of the main commands that will help us install and update the software

srvctl start rhpserver

srvctl add rhpserver -storage /// -diskgroup xxx

rhpctl import image… -imagetype -path

rhpctl add image -image abc -workingcopy abcwc

rhpctl add workingcopy -workingcopy WC1 -image iii -storagetype LOCAL -path /ora19

rhpctl upgrade gihome -sourcewc aa -destwc bb

rhpctl upgrade database

rhpctl zdtupgrade database…

rhpctl addpdb database…

rhpctl deletepdb database…

Bynaries:rhpctl add workingcopy -image db19 -path /u01…. -client xxx -oraclebase /u01.. - workingcopy w1

RDBMS:rhpctl add database -workingcopy w1 -dbname db - node a,b -dbtype RAC- datafiledestination xx_DG

rhpctl move database -sourcewc a -patchedwc a_psu

CHANGING THE COLUMNS WIDTH USING CSSCAN

After executing CSSCAN some files are generated (.err, .out) informing about the columns that will not fit in the target charset(usually ALxUTFz)and, furthermore, in the host schema CSMIG (@csminst.sql) the same information is stored, so we can use it to generate the modifications needed:

[CAT ]Després de passar el CSSCAN, com a subproducte, i a part dels fitxerets que es generen, queda la mateixa informació a l'esquema CSMIG (s'ha de crear abans de passar el CSSCAN [@csminst.sql]). Doncs aquesta info es pot utilitzar per generar les ordres d'ampliació de les columnes que no hi cabran al nou codi de caràcters.


cat prova.err|grep "Number of Exceptions"|grep -v ": 0"|wc –l

This select produces the instructions for the tables that can't fit in the actual column size:

set linesize 222
SELECT 'ALTER TABLE '||PROP||'.'||TAULA||' MODIFY '
||dba_tab_columns.COLUMN_NAME||' '||
dba_tab_columns.DATA_TYPE||'('||TAMANY||');' DDL
FROM
(select DISTINCT U.USERNAME PROP, O.OBJECT_NAME TAULA,
T.COLUMN_ID COL_ID, max(MAXSIZ) TAMANY
from CSM$COLUMNS C, dba_users U,
dba_objects O, dba_tab_columns T
where C.ERRCNT > 0 and
C.USR#=U.USER_ID and
C.OBJ#=O.OBJECT_ID and
C.COL#=T.COLUMN_ID
group by U.USERNAME, O.OBJECT_NAME, T.COLUMN_ID),
dba_tab_columns
WHERE PROP = dba_tab_columns.OWNER
AND TAULA = dba_tab_columns.TABLE_NAME
AND COL_ID = dba_tab_columns.COLUMN_ID
ORDER BY 1;

EXAMPLE OUTPUT:
ALTER TABLE DMBATCH.TAULA MODIFY NOM_PERSONA_TIT VARCHAR2(54);
....

Searching for a user (enhanced)

Now the same as before but using a Windows batch FOR loop.
The script iterates through the file "oracle.dat", that contains "username password SID" in every line. Then connects with sqlplus using this information and executes "busca.sql" that do and formats the search.


BUSCA.BAT

@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
@echo off
@echo >%1.out
FOR /F "tokens=1,2,3" %%G IN (oracle.dat) DO (sqlplus -S %%G/%%H@%%I @c:\busca.sql %1>>%1.out)

type %1.out|find "->"

REM ^ List only nontrivial results
type %1.out|find /c /i "%1"
REM ^ Count the results

ORACLE.DAT
(Information separated by ONE space. Can be configured with "option delims=xxx")

system password SID
....


BUSCA.SQL
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1');
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado||')');
end if;
exception
when no_data_found then null;
end;
/
exit

EXAMPLE

C:\>busca rman
Se genera el fichero "rman.out"
RESUMEN:
========
AE02 -> aecobde9 ---> RMAN (OPEN)
AE03 -> arepbde1 ---> RMAN (OPEN)
AE05 -> aremsae4 ---> RMAN (OPEN)
PROD -> sun06.sscc.intranet.local ---> RMAN (OPEN)
RMAN -> rman ---> RMAN (OPEN)
RMAN9 -> rman ---> RMAN (OPEN)
XE -> bcnaorarman ---> RMAN (OPEN)
AOLT -> sun03 ---> RMAN (OPEN)
8

Searching for a user

It's usual to have to locate where an user resides in. Here it's a way to do it from my Windows, that have all access permisions. Another way is to install a Linux or emulator and do it with a shell script (some day...).

Result:

C:\>busca perfstat
Se genera el fichero "perfstat.out"
RESUMEN:
========
AE02 -> aecobde9 ---> PERFSTAT (OPEN)
AE03 -> arepbde1 ---> PERFSTAT (OPEN)
AE05 -> aremsae4 ---> PERFSTAT (OPEN)
AE07 -> aecobde9 ---> PERFSTAT (OPEN)
AE08 -> arepbde1 ---> PERFSTAT (OPEN)
AE09 -> arepbde1 ---> PERFSTAT (OPEN)
FINP -> sunfin ---> PERFSTAT (OPEN)
PROD -> sun06.sscc.intranet.local ---> PERFSTAT (OPEN)
RMAN -> rman ---> PERFSTAT (OPEN)
SCENTER1 -> bcnascec1p01 ---> PERFSTAT (OPEN)
AOLT -> sun03 ---> PERFSTAT (OPEN)
APP02 -> aremsar1 ---> PERFSTAT (OPEN)
APP05 -> agenbdr1 ---> PERFSTAT (OPEN)
13

And now the implementation:

C:\>type busca.bat
@echo off
echo Se genera el fichero "%1.out"
echo RESUMEN:
echo ========
echo ***************************>%1.out
echo *** P R O D U C C I O N ***>>%1.out
echo ***************************>>%1.out

sqlplus -S system/password@AE01 @c:\busca.sql %1>>%1.out
sqlplus -S system/password@AE01-10 @c:\busca.sql %1>>%1.out
....
type %1.out|find "->"
type %1.out|find /c /i "%1"


C:\>type busca.sql
set pagesize 0
set feed off
set serveroutput on
declare
ins varchar2(10);
hostname varchar2(40);
OK varchar2(40):=NULL;
estado varchar2(40):=NULL;

begin
select UPPER(instance) into ins from v$thread where rownum=1;
select HOST_NAME into hostname from v$instance where rownum=1;
select username,ACCOUNT_STATUS into OK,estado from dba_users where upper(username) like upper('&1'
);
if ok is not null then
dbms_output.put_line(rpad(ins,9,' ')||'-> '||rpad(hostname,30,' ')||' ---> '||OK||' ('||estado
||')');
end if;
exception
when no_data_found then null;
end;
/
exit

Event trigger after logon

This trigger was necessary to execute 1-2 remote selects after login, because all the selects trough db links were working only the third time it was issued, a very strange behaviour. I had to create two similar triggers and they were very effective until we found the root cause.

CREATE OR REPLACE TRIGGER toni_bdn AFTER LOGON ON database
DECLARE
v_user VARCHAR2(30); x number;
a integer; b integer; c integer; a1 integer; a2 integer; a3 integer;
b1 integer; b2 integer; b3 integer;
BEGIN
if sys_context('userenv','session_user') in ('SYSTEM','SYS','BDNOWN01','BDNUSR01') then
a:=dbms_sql.open_cursor;
dbms_sql.parse(a, 'select count(1) from midual@BDN_DL_ROMEGA', dbms_sql.native);
execute immediate 'select count(1) into x from midual@BDN_DL_ROMEGA';
b:=dbms_sql.execute(a);
b1:=dbms_sql.execute(c);
b2:=dbms_sql.execute(a);
b3:=dbms_sql.execute(a);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end if;
END;
/

Creating objects without STORAGE clauses

In some teams it's usual not to indicate neither storage clauses nor tablespace clauses, so I've gotten some interest in studying the consecuences. Here are the results with a single table in Oracle 10GR2:

I create a single table with no clauses:

create table sense_clausules (col varchar(2000)) tablespace PL_DATOS;

col segment_name for a30
col INITIAL_EXTENT for 999,999
col next_extent, bytes for 999,999
select segment_name, INITIAL_EXTENT, next_extent, bytes
from dba_segments
where segment_name=upper('sense_clausules')
order by 1;


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 65536

Next I load a lot of rows:


set serveroutput on
declare i number;
j number;
begin
for j in 1..100000 loop
for i in 1..1000 loop
insert into sense_clausules values ('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') nologging;
end loop;
commit;
dbms_output.put_line(j*1000);
end loop;
end;
/


SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 75.497.472

select count(1) from sense_clausules;

COUNT(1)
------------
1.998.010

select sum(bytes)
from dba_extents
where segment_name=upper('sense_clausules')
order by 1;

SUM(BYTES)
------------
148.897.792

And now the quantity and size of the extensions after the load:

col segment_name format a30
select segment_name,bytes, count(*)
from dba_extents
where segment_name=upper('sense_clausules')
group by segment_name,bytes
order by 2,3;

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 6291456 1
SENSE_CLAUSULES 8388608 9

4 files seleccionades.

Now a more heavy load and observe:


COUNT(1)
------------
100.000.000

SEGMENT_NAME BYTES COUNT(*)
------------------------------ ------------ ------------
SENSE_CLAUSULES 65536 16
SENSE_CLAUSULES 1048576 63
SENSE_CLAUSULES 8388608 120
SENSE_CLAUSULES 35651584 1
SENSE_CLAUSULES 67108864 94

5 files seleccionades.

294 extensions created for 100 milions rows.

SEGMENT_NAME INITIAL_EXTENT NEXT_EXTENT BYTES
------------------------------ -------------- ------------ ------------
SENSE_CLAUSULES 65,536 148.897.792


drop table sense_clausules cascade constraints;

I, personally, prefer to indicate the INITIAL and NEXT clauses in the way the capacity plan indicates.

Checking the status of an instance

We need to verify if, as a consequence of a hot backup, the instance remains in a "suspend" status, then put it in open (resuming it), put all the tablespaces in "end backup", and sending a mail only when it do all this. The script follows up.

###############################################
##### resucita.sh ######
##### Authors: Toni Blanco ######
##### Fecha de creacion: 200800906 ######
##### Ultima modificacion: 20080906 ######
##### Revision: v1.0 ######
###############################################
# Si la instancia es troba en estat SUSPEND li fa un RESUME i envia un correu a explo.sistemas
# Si es troba en estat OPEN no fa res, només m'envia un correu a mi.
. /export/home/oracle/.profile
date
resultado=0
# I catch the result of the invocation to sqlplus to determine the status
# of the instance, using the dbms_output API.

resultado=`sqlplus -S "/as sysdba"<< EOF
set serveroutput on;
declare
estat varchar2(20):='nada';
a integer;
b integer;
c varchar2(100);
x integer:=0;
cursor c1 is select tablespace_name
from dba_tablespaces
where status<>'ONLINE'
order by 1;
fc1 c1%rowtype;

begin

select status into estat from "v\$instance" ; -- Status of the instance

if estat='SUSPEND' then
dbms_output.put_line(1); -- resultado:=1 --> send a mail
execute immediate 'alter system RESUME'; --> resume the instance
/* Loop to take to "end backup" all the tablespaces with another status */
open c1;
loop
exit when c1%notfound;
fetch c1 into fc1;
c:='alter tablespace '||fc1.tablespace_name||' online';
a:=dbms_sql.open_cursor;
dbms_sql.parse(a,c,dbms_sql.native);
b:=dbms_sql.execute(a);
dbms_sql.close_cursor(a);
end loop;
close c1;
else dbms_output.put_line(0);
end if;
end;
/
exit
EOF`

echo $resultado
if [ $resultado -gt 0 ]; then
# If the result i other than 0, send a mail to inform and suggest to check the actual status
echo "Instance in SUSPEND state. Check its actual status, please. "|mailx -s "Oracle AE05 recovered from a SUSPEND state" explo.sistemas@empresa.com
else
echo "Instance in OPEN state. "|mailx -s "Oracle AE05 OK" toni.blanco@empresa.com
fi

exit

Checking backups

Generation of a listing with all the filesystems that forms an instance. It's useful to send to the backup administrator, for instance.

We configure BACKUPS.BAT to connect to all the instances we administer. It uses backups.sql that groups and order the directories, and generates an ordered listing of instances with the directories that contains some datafiles or configurations files.

-- BACKUPS.BAT
@echo off
sqlplus -S system/****@APP05 @c:\backups.sql >>c:\backups.out
sqlplus -S system/****@APP01-10 @c:\backups.sql >>c:\backups.out
....

-- backups.sql

set pagesize 0

col instancia format a10

set serveroutput on feed off
declare
ins varchar2(20);
ver varchar2(120);
begin
select UPPER(instance) into ins from v$thread where rownum=1;
select banner into ver from gv$version where banner like '%Edition%' and rownum=1; dbms_output.put_line(rpad(ins,8,' ')' - 'ver);
end;
/

select HOST_NAME HOST from v$instance;
select distinct substr(file_name, 1, instr(file_name,'/',-1)) from dba_data_files
union
select distinct substr(name, 1, instr(name,'/',-1)) from v$controlfile
union
select distinct substr(member, 1, instr(member,'/',-1)) from v$logfile
union
select distinct substr(file_name, 1, instr(file_name,'/',-1)) from dba_temp_files union
select distinct substr(value, 1, instr(value,'/',-1)) from v$parameterwhere name like 'log_archive_dest%' and value is not null and value <>'enable'order by 1;
select rpad('-',80,'-') from dual;
exit



-- Backups.out

APP05 - Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
hostname1
/global/aecobde5/u01/oradata/ae06/
/global/aecobde5/u02/oradata/ae06/
/global/aecobde5/u03/oradata/ae06/
/global/aecobde5/u04/oradata/ae06/
/global/aecobde5/u05/oradata/ae06/arch/
/global/aecobde5/u06/oradata/ae06/
/global/aecobde5/u07/oradata/ae06/
/global/aecobde5/u08/oradata/ae06/
/global/aecobde5/u09/oradata/ae06/
/global/aecobde5/u11/oradata/ae06/
--------------------------------------------------------------------------------
APP011 - Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
hostname2
+DATOS/app01/controlfile/
+DATOS/app01/datafile/
+DATOS/app01/onlinelog/
+DATOS/app01/tempfile/
+FLASHDB/app01/controlfile/
+FLASHDB/app01/onlinelog/
--------------------------------------------------------------------------------

Comparing several schemas with dbms_space.object_space_usage

This is useful, for example, if we import or refresh a schema to another instance.
In this example we'll compare schemas DWH03 and PRUEBA03.

set serveroutput on

DECLARE
CURSOR C IS select OWNER, SEGMENT_NAME, SEGMENT_TYPE,100, PARTITION_NAME
from dba_segments
where (OWNER LIKE 'PRUEBA03' OR OWNER LIKE 'DWH03')
and segment_name not like 'BIN%'
AND PARTITION_NAME IS NULL
and SEGMENT_TYPE in ('TABLE', 'INDEX', 'CLUSTER')
group by owner, SEGMENT_NAME, SEGMENT_TYPE, PARTITION_NAME
order by owner, SEGMENT_TYPE, SEGMENT_NAME;

F C%rowtype;
SPACE_USED NUMBER;
SPACE_ALLOCATED NUMBER;
TOTAL1 NUMBER;TOTAL2 NUMBER;

BEGIN
DBMS_OUTPUT.ENABLE(999999999);
open c;
fetch c into f;
TOTAL1:=0; TOTAL2:=0;
LOOP
EXIT WHEN C%NOTFOUND;
dbms_space.object_space_usage(F.OWNER, F.SEGMENT_NAME,
F.SEGMENT_TYPE,100, F.PARTITION_NAME,SPACE_USED,
SPACE_ALLOCATED);
if F.OWNER='DWH03' THEN TOTAL1:=TOTAL1+SPACE_USED;
elsif F.OWNER='PRUEBA03' THEN TOTAL2:=TOTAL2+SPACE_USED;
end if;
dbms_output.put_line
(F.OWNER','F.SEGMENT_TYPE','F.SEGMENT_NAME','F.PARTITION_NAME','SPACE_USED','SPACE_ALLOCATED);
fetch c into f;
END LOOP;
close c;
dbms_output.put_line('SUMA DE DWH03 = 'TOTAL1);
dbms_output.put_line('SUMA DE PRUEBA03 = 'TOTAL2);
END;
/

MOVING OBJECTS TO NEW TABLESPACES WITH IMPDP

The scenario consists in having about 180 tablespaces and wish to move the objects to a set of 10 news tablespaces (based on the table size criteria for every schema). The “alter table…move…” technique generates thousands of individual orders and is very difficult to trace. For that reason I've used impdp and a few orders easy to follow for anyone.

1) Export the schemas to reorganize:
expdp system schemas=dwh01, dwh02 directory=exports dumpfile=dwh01-2_dp_1.dmp, ...
logfile=dwh01-2_dp.log content=all keep_master=N parallel=3 filesize=25G

2) Create new tablespaces.

3) Create new users (we can't rename it) or drop and recreate the existing ones

4) Import one schema at a time: impdp system PARFILE=dwh01_P.par

Example.par:
------------
directory=exports
dumpfile=dwh01-2_dp_1.dmp, dwh01-2_dp_2.dmp, ...
logfile=dwh01_import_P.log
content=all
keep_master=N
parallel=4
REMAP_SCHEMA=DWH01:PRUEBA01
EXCLUDE=PACKAGE
EXCLUDE=PACKAGE_BODY
EXCLUDE=FUNCTION
REMAP_TABLESPACE=AWCUBO:DWHTSDATP01,...
REMAP_TABLESPACE= ...,
TABLES=DWH01.LKBACAMPO01,...

5) Recreate invalid or unusable indexes

6) Recompile

7) Check the quantity of objects between source and destination

select owner, count(table_name)
from dba_tables
where (OWNER LIKE 'PRUEBA0_' OR OWNER LIKE 'DEMETRIO0_' OR OWNER LIKE 'D__0_')
group by owner
order by 1;

How to verify a restored backup

The scenario is this one: we need to clone an instance to another machine and different directories, and we need to verify that all the datafiles are copied anywhere in the destination machine, in order to avoid the invalidation when we recover de instance with missing datafiles.

The solution showed here is based in:

1) Having a listing with all the datafiles in the destination machine, showing the filename in a single line.
2) Create an external table in the source instance to the datafiles listing.
3) Issue a select command to find the datafiles not still copied.

1) Listing with all the datafiles

I do that simply this way:

dir G:\datafiles > DF.txt
dir H:\datafiles >> DF.txt
...and so on

Next I edit the txt and delete the left colums before the file name, and that's enough. We don't need to delete extra lines. We can avoid this action complicating the select, but I find it's quicker this way.

2) Create an external table

First I copy the datafile listing to some directory in the source machine, E:\BBDD\ in this example.

Next I declare an oracle directory in order to read the datafile listing from inside the source database, and declare the external table.

create directory toni as 'E:\BBDD\';

create table df_kk
(texto varchar2 (120 char) null)
organization external(
type oracle_loader
default directory toni
access parameters (records delimited by newline)
location (toni:'DF.txt')
)
reject limit 1000;


3) Verify that all the source files have been copied.

For every datafile in dba_data_files I look for it in the external table.

col bytes format 999,999,999,999,999
select distinct substr(file_name,instr(file_name,'\',-1)+1,length(file_name)) DF, substr(file_name, 1,3), bytes
from dba_data_files, df_kk
where substr(file_name,instr(file_name,'\',-1)+1,length(file_name)) not in (select texto from df_kk where texto like '%'||substr(file_name,instr(file_name,'\',-1)+1,length(file_name))||'%')
order by 1;


That's all.

How to include the timestamp in filenames (Windows)

It's little more difficult than in Unix.

We begin forming the name of the dumpfile:

Set fichero=MSMTD_%date:~-4,4%%date:~-7,2%%date:~4,2%%time:~0,2%%time:~3,2%

Example output: MSMTD_200803171559

Let's see the batch file:

export.bat

@echo off
set fecha=MSMTD_%date:~-4,4%%date:~-7,2%%date:~4,2%%time:~0,2%%time:~3,2%echo Se generaran los ficheros %fecha% {.dmp y .log}
expdp system/password directory=exports dumpfile=%fecha%.dmp logfile=%fecha%.log schemas=MSMTD content=all keep_master=N
@echo ****** FIN DE LA EXPORTACION *********

Test the connections to all the instances

I find very useful to connect to all my instances at the beginning of the day; in that way I can anticipate a lot of e-mails. This one is the most simplest version, using a windows batch file.

We need to enter a shell on our desktop PC (surely with all the network permissions to access the instances we administer), an Oracle Client installed (to use sqlplus) and a tnsnames.ora configured (at least an Instance Client).

Example of use:

U:\>connects
BDEXTPPR

BDINTPPR

DWHDEV

DWHREP10

DWHPRO
...
U:\>

Here we can see the "connects.bat" file and the "exit.sql" file.

U:\>type d:\dba\connects.bat
@echo off
sqlplus -S "sys/password@bdintppr as sysdba" @c:\exit.sql
sqlplus -S "system/password@dwhpro" @c:\exit.sql
....


U:\>type c:\exit.sql
set pagesize 0
col instancia format a10
select UPPER(instance) INSTANCIA from v$thread;
exit


It's not the solution I most like, but it works well. The other solution I'm working in on consists in a PL/SQL the establish connections to the locations stored in a VARRAY.

Exporting and importing statistics

Exporting and importing statistics from PROduction to PREproduction:
====================================================================

First we must create a table to save the statistics:

execute dbms_stats.create_stat_table('SYS','ESTADISTICAS','TOOLS');
^schema ^table ^TS

Second we export the schema statistics to this table:

execute dbms_stats.EXPORT_SCHEMA_STATS('DWH03','ESTADISTICAS','DWH03','SYS');

We can, alternativelly, export only one table:

PROCEDURE EXPORT_TABLE_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT

execute dbms_stats.EXPORT_TABLE_STATS('DWH03','BSMZCONINFHIS03',NULL,'ESTADISTICAS','a','SYS');

Next we export the statistics table and import it in the destination instance:

EXP sys@PRO file=estad_pro.dmp log=estad_pro.log tables="ESTADISTICAS"

Conectado a: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Exportaci¾n realizada en el juego de caracteres WE8MSWIN1252 y el juego de carac
teres NCHAR AL16UTF16

Exportando las tablas especificadas a travÚs de la Ruta de Acceso Convencional .
..
. exportando la tabla ESTADISTICAS 100058 filas exportadas
La exportaci¾n ha terminado correctamente y sin advertencias.

IMP system@PRE file=estad_pro.dmp log=estad_pre.log full=Y

importaci¾n realizada en el juego de caracteres WE8MSWIN1252 y el juego de carac
teres NCHAR AL16UTF16
. importando objetos de SYS en SYSTEM
. importando objetos de SYS en SYSTEM
. importando la tabla "ESTADISTICAS" 100058 filas importadas

La importaci¾n ha terminado correctamente y sin advertencias.


PROCEDURE IMPORT_SCHEMA_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT
FORCE BOOLEAN IN DEFAULT

execute dbms_stats.IMPORT_SCHEMA_STATS('DWH03','ESTADISTICAS','DWH03','SYSTEM');
Procedimiento PL/SQL terminado correctamente.

PROCEDURE IMPORT_TABLE_STATS
Nombre de Argumento Tipo E/S +Por Defecto?
------------------------------ ----------------------- ------ --------
OWNNAME VARCHAR2 IN
TABNAME VARCHAR2 IN
PARTNAME VARCHAR2 IN DEFAULT
STATTAB VARCHAR2 IN
STATID VARCHAR2 IN DEFAULT
CASCADE BOOLEAN IN DEFAULT
STATOWN VARCHAR2 IN DEFAULT
NO_INVALIDATE BOOLEAN IN DEFAULT

Or we can do the same procedure with the individual table:

SQL> execute dbms_stats.IMPORT_TABLE_STATS('DWH03','BSMZCONINFHIS03',NULL,'ESTAD
ISTICAS','DWH03',TRUE,'SYSTEM');

Procedimiento PL/SQL terminado correctamente.

And, finally, we can drop (or not) the statistics table in the source instance:

execute dbms_stats.drop_stat_table('SYS','ESTADISTICAS');

Example of resumable option

An usually forgotten feature. Here there is a basic example:

SQL> CREATE TABLESPACE chinorri
2 LOGGING
3 DATAFILE 'K:\ORANT\DWH\chinorri.DBF' SIZE 128k
4 autoextend off EXTENT MANAGEMENT LOCAL SEGMENT
5 SPACE MANAGEMENT AUTO ;

Tablespace creado.

SQL> create table kk(kk varchar2(2000)) tablespace chinorri;
Tabla creada.

-- WITHOUT RESUMABLE, THE USUAL ERROR FOLLOWS
-- SIN RESUMABLE, LA PETADA DE SIEMPRE

SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;


insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects
*
ERROR en lÝnea 1:

ORA-01653: no se ha podido ampliar la tabla SYSTEM.KK con 8 en el tablespace
CHINORRI

-- WITH RESUMABLE AND WAITING ONE MINUTE:
-- CON RESUMABLE DE UN MINUTILLO:
SQL> alter session enable resumable timeout 60 name 'resumable test';
Sesi¾n modificada.

SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;

-- OF COURSE, AT THE END OF THE PERIOD, THE TRANSACTIONS CRASHS
-- CLARO, AL CABO DEL MINUTILLO DA UN ERROR

insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
xxxxxxxxxxxxxxxxxxxxxx' from dba_objects
*
ERROR en lÝnea 1:

ORA-30032: la sentencia suspendida (de reanudaci¾n) ha sufrido un timeout
ORA-01653: no se ha podido ampliar la tabla SYSTEM.KK con 8 en el tablespace
CHINORRI


-- BUT IF WE CONFIGURE ENOUGH TIME AND SOLVE THE ISSUE IN THE MEANWHILE...
-- PERO SI LE DAMOS EL TIEMPO SUFICIENTE Y LO SOLUCIONAMOS ENTRE TANTO:


SQL> alter session enable resumable timeout 7200 name 'resumable test';
Sesi¾n modificada.


SQL> insert into kk select 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx' from dba_objects;

-- HERE THE SESSION IS SUSPENDED.....
…….. Y AQUÍ SE QUEDA ESPERANDO DURANTE UNA HORA (7200 SEGUNDOS).

-- BUT WHEN WE RESIZE THE TABLESPACE:
-- Se amplia el TS y ……

42842 filas creadas.

SQL> alter session disable resumable;
Sesi¾n modificada.


Similarly you can use this clause in the import utility (resumable=Y and RESUMABLE_TIMEOUT=time).