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).

Grants, three ways to obtain it

1) If you are in Oracle <= 9i you can use the Unix commands strings and grep to filter the metadata existing in the export file header (interesting if you have a huge one):

strings export.dmp | grep -i ' TO '
or
strings export.dmp|grep –i GRANT |sort > grants.sql


2) Classical dictionary views querys:

-- USUERS ASSIGNEDS TO EVERY ROLE --
SELECT GRANTED_ROLE as rol, GRANTEE as User
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
order by granted_role;

-- ROLES OF EVERY USER --
SELECT GRANTEE as Usuario, GRANTED_ROLE as rol
FROM DBA_ROLE_PRIVS
WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
order by grantee;

-- PRIVILEGES OF EVERY USER --
select * from dba_sys_privs
where grantee in (select username from dba_users)
order by grantee;

-- OBJECT PRIVILEGES OF EVERY USER --
select substr(grantee, 1,15) usuario,
substr(table_name,1,35) tabla,
substr(privilege,1,20) privilegio
from dba_tab_privs
where GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
ORDER BY table_name;

-- DBA USERS --
select grantee from dba_role_privs
where granted_role ='DBA';


3) The most powerful an flexible: query the dictionary.

select 'grant '||m$.name||' on '||uo$.name||'.'||o$.name||' to '||ue$.name
from
sys.objauth$ t$,
sys.obj$ o$, sys.user$ ur$,
sys.table_privilege_map m$,
sys.user$ ue$,
sys.user$ uo$
where
o$.obj# = t$.obj#
and t$.privilege# = m$.privilege
and t$.col# is null
and t$.grantor# = ur$.user#
and t$.grantee# = ue$.user#
and o$.owner# = uo$.user#
and t$.grantor# != 0
-- (to filter by role name:) and ue$.name='ROLE';

(10g) A query to find the most active users connected from another machine

This query is useful to find middle-tier (applications servers or so) users having a heavy activity in the instance, thanks to the column CLIENT_IDENTIFIER, that is maintained throught all the layers. You can, also, filter by active users.

set linesize 1000
col program format a30
col machine format a10
col PERSONA format a10
col program format a7
col username format a7
alter session set nls_date_format='dd/mm/yy hh24:mi';

select s.CLIENT_IDENTIFIER PERSONA, substr(s.program, 1,7) program, s.username, l.TIME_REMAINING REMAINING, l.ELAPSED_SECONDS ELAPSED,l.START_TIME, s.sid, s.serial#, s.process,
l.OPNAME,l.TARGET,l.TARGET_DESC,l.SOFAR,l.TOTALWORK,l.UNITS,
l.LAST_UPDATE_TIME,l.TIMESTAMP,l.CONTEXT,l.MESSAGE
from v$session s, v$session_longops l
where s.TYPE='USER'
and program like 'dis51ws@%'
and s.username='SCOTT'
and s.SID=l.sid
and s.SERIAL#=l.SERIAL#
--and status='ACTIVE'
order by elapsed_seconds desc;