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');