dba-tech: 2014

domingo, 28 de diciembre de 2014

Acciones sobre un Pluggable Database:

Que no se puede hacer:

1-
SYS@PDB1> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database
2-
SYS@PDB1> alter system set pga_aggregate_target=470m scope=spfile;
alter system set pga_aggregate_target=470m scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

Que operaciones que Si se pueden hacer

1-
SYS@PDB1> alter database datafile '/u09/app/oradata/ORCL12/datafile/PDB/pdb_index01.dbf' resize 2m;

Database altered.

Elapsed: 00:00:00.18

Siempre y cuando el tablespace en creado en PDB

2.SYS@PDB1> shutdown immediate
Pluggable Database closed.

3-SYS@PDB1> startup
Pluggable Database opened.

sábado, 13 de diciembre de 2014

Pruebas de Recovery

RECOVERY DATAFILE

1- mv pdb_data01.dbf pdb_data01.dbf.old
2- rman target sys/manager123@pdb1

RMAN> run

2> {

3> alter tablespace pdb_data offline immediate;

4> restore tablespace pdb_data;

recover tablespace pdb_data;

alter tablespace pdb_data online;

}5> 6> 7>

Statement processed

Starting restore at 13-DEC-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_DISK_1: restoring datafile 00036 to /u09/app/oradata/ORCL12/datafile/PDB/pdb_data01.dbf

channel ORA_DISK_1: reading from backup piece +FRA/ORCL12/0A0DF24AB9DE70B4E0539A00A8C011E3/BACKUPSET/2014_12_13/nnndn0_tag20141213t041241_0.332.866175161

channel ORA_DISK_1: piece handle=+FRA/ORCL12/0A0DF24AB9DE70B4E0539A00A8C011E3/BACKUPSET/2014_12_13/nnndn0_tag20141213t041241_0.332.866175161 tag=TAG20141213T041241

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished restore at 13-DEC-14

Starting recover at 13-DEC-14

using channel ORA_DISK_1

starting media recovery

media recovery complete, elapsed time: 00:00:00

Finished recover at 13-DEC-14

Statement processed

RMAN>

viernes, 12 de diciembre de 2014

-Scripts verification Backup Oracle 12c

oel-2:orcl12:/home/oracle12/rman/$ rman target / @backup_level_0.rmn

....
validation succeeded for archived log
archived log file name=+FRA/ORCL12/ARCHIVELOG/2014_12_12/thread_1_seq_166.409.866121149 RECID=466 STAMP=866121153
validation succeeded for archived log
archived log file name=+FRA/ORCL12/ARCHIVELOG/2014_12_12/thread_1_seq_168.410.866121149 RECID=467 STAMP=866121155
validation succeeded for archived log
archived log file name=+FRA/ORCL12/ARCHIVELOG/2014_12_12/thread_1_seq_169.408.866121469 RECID=468 STAMP=866121469
validation succeeded for archived log
archived log file name=+FRA/ORCL12/ARCHIVELOG/2014_12_12/thread_1_seq_170.400.866121691 RECID=469 STAMP=866121691
Crosschecked 4 objects

released channel: t1

Recovery Manager complete.

Veamos salida del scripts:

Scripts de Verificacion:

--*************************************************
-- Copyright © 2014 by SQL-TECH.
-- Free for non-commercial use!
-- To license, e-mail gerardo.leyton@gmail.com
-- ************************************************

select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') "start_time",
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') "end_time",
round((j.output_bytes/1024/1024),0) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 0, 'Sunday', 1, 'Monday',
2, 'Tuesday', 3, 'Wednesday',
4, 'Thursday', 5, 'Friday',
6, 'Saturday','Sunday') "dow",
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l
--.inst_id output_instance
,max( k.next_change#) "SCN"
,max( (k.sequence#+1)) "Sequence#"
,decode(x.con_id,0,'CDB',1,decode(c.name,'PDB$SEED','CDB',c.name),'CDB + PDBs') con_id
from v$containers c,V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,(count(distinct d.con_id) -2 ) con_id, max( d.con_id) max_id,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
from
V$BACKUP_SET_DETAILS d
join V$BACKUP_SET s on s.set_stamp = d.set_stamp and s.set_count = d.set_count
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp
) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp
from V$RMAN_status o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
join
v$backup_redolog k
on
to_char(next_time,'yyyy-mm-dd hh24:mi') between TO_CHAR(j.end_time - (1/12), 'yyyy-mm-dd hh24:mi') and TO_CHAR(j.end_time , 'yyyy-mm-dd hh24:mi')
and ( k.set_stamp,k.set_count) in (select set_stamp,set_count from V$BACKUP_SET_DETAILS kk where kk.session_recid=j.session_recid and kk.session_stamp=j.session_stamp)
where j.start_time > trunc(sysdate)-30
and decode(x.con_id,1,x.max_id,1)=c.con_id
--inst_id is not null
group by
j.session_recid,
j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss'),
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') ,
round((j.output_bytes/1024/1024),0),
j.status,
j.input_type,
decode(to_char(j.start_time, 'd'), 0, 'Sunday', 1, 'Monday',
2, 'Tuesday', 3, 'Wednesday',
4, 'Thursday', 5, 'Friday',
6, 'Saturday','Sunday'),
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l
, decode(x.con_id,0,'CDB',1,decode(c.name,'PDB$SEED','CDB',c.name),'CDB + PDBs')

saludos

miércoles, 18 de junio de 2014

Tiempos de Respuestas para Oracle

T. de respuesta para Discos:

NSTANCE_NUMBER BEGIN_TIME average(ms) maxval(ms)
--------------- ---------------- ----------- ----------
2 18-JUN-14 00:59 ,94 2,08
2 18-JUN-14 01:59 ,93 2,46
2 18-JUN-14 03:00 1,37 2,4
2 18-JUN-14 03:59 1,51 5,01
2 18-JUN-14 04:59 1,27 5,36
2 18-JUN-14 05:59 ,94 1,72
2 18-JUN-14 06:59 1,35 5,6
2 18-JUN-14 07:59 1,83 2,72
2 18-JUN-14 09:00 2,04 5,59
2 18-JUN-14 09:59 1,82 2,43

T, respuestas SQL:

INSTANCE_NUMBER BEGIN_TI HORA METRIC_NAME Tiempo Respuesta(s)

--------------- -------- -------- ------------------------- -------------------

2 18/06/14 00 SQL Response Time (secs) ,1

2 18/06/14 01 SQL Response Time (secs) ,05

2 18/06/14 02 SQL Response Time (secs) ,06

2 18/06/14 03 SQL Response Time (secs) 2,47

2 18/06/14 04 SQL Response Time (secs) 1,27

2 18/06/14 05 SQL Response Time (secs) 1,15

2 18/06/14 06 SQL Response Time (secs) 2,08

2 18/06/14 07 SQL Response Time (secs) ,07

2 18/06/14 08 SQL Response Time (secs) ,06

2 18/06/14 09 SQL Response Time (secs) ,06

2 18/06/14 10 SQL Response Time (secs) ,06

T. Respuesta TRansacciones:

NSTANCE_NUMBER BEGIN_TI HORA METRIC_NAME Tiempo Respuesta(s)

--------------- -------- ---- ------------------------------ -------------------

2 18/06/14 00 Response Time Per Txn (secs) 2,37

2 18/06/14 01 Response Time Per Txn (secs) 1,31

2 18/06/14 02 Response Time Per Txn (secs) 1,65

2 18/06/14 03 Response Time Per Txn (secs) 68,58

2 18/06/14 04 Response Time Per Txn (secs) 33,41

2 18/06/14 05 Response Time Per Txn (secs) 32,25

2 18/06/14 06 Response Time Per Txn (secs) 73,39

2 18/06/14 07 Response Time Per Txn (secs) 1,59

2 18/06/14 08 Response Time Per Txn (secs) 1,39

2 18/06/14 09 Response Time Per Txn (secs) 1,48

2 18/06/14 10 Response Time Per Txn (secs) 1,44

TPS:

INSTANCE_NUMBER SNAP_ID DATETIME Commits/s Rollbacks/s TX/s

--------------- ---------- --------------- ---------- ----------- ----------

2 16989 18-06-14 01 7,42 0 7,42

2 16990 18-06-14 02 5,06 0 5,06

2 16991 18-06-14 03 4,04 0 4,04

2 16992 18-06-14 04 3,54 0 3,55

2 16993 18-06-14 05 3,6 0 3,6

2 16994 18-06-14 06 4,4 0 4,4

2 16995 18-06-14 07 8,06 0 8,06

2 16996 18-06-14 08 18,95 0 18,95

2 16997 18-06-14 09 33,87 0 33,87

2 16998 18-06-14 10 37,73 0 37,73

OBS:

siempre debemos estar monitoreando estas variables en nuestro entorno Oracle

lunes, 16 de junio de 2014

Administrando PDB

Arquitectura:

domingo, 15 de junio de 2014

RMAN (recovery table)

1-
backup realizados:

SCOTT@orcl12> select * from emp
2 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

2- Drop table

SCOTT@orcl12> drop table emp;

Table dropped.

Elapsed: 00:00:00.04

3- Recovery table

oel-2:orcl12:/home/oracle12/rman/$ cat rec_table.rmn

RECOVER TABLE SCOTT.EMP

#until time "to_date('07/27/2013 05:00','mm-dd-yyyy hh24:mi')"

until sequence 843

AUXILIARY DESTINATION '/tmp/oracle/recover'

DATAPUMP DESTINATION '/tmp/recover/dumpfiles'

REMAP TABLE 'SCOTT'.'EMP':'EMP_RECOVERED';

oel-2:orcl12:/home/oracle12/rman/$ rman target / @rec_table.rmn

.....

Oracle instance shut down

Performing import of tables...

IMPDP> Master table "SYS"."TSPITR_IMP_qcwj_ddfx" successfully loaded/unloaded

IMPDP> Starting "SYS"."TSPITR_IMP_qcwj_ddfx":

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

IMPDP> . . imported "SCOTT"."EMP_RECOVERED" 8.757 KB 14 rows

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

IMPDP> Job "SYS"."TSPITR_IMP_qcwj_ddfx" successfully completed at Sun Jun 15 10:30:23 2014 elapsed 0 00:01:18

Import completed

Removing automatic instance

Automatic instance removed

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_temp_9svc2qoo_.tmp deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_9_9svc556f_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_8_9svc53pd_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_7_9svc50cg_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_6_9svc4w0k_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_5_9svc4s35_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/onlinelog/o1_mf_4_9svc4qo7_.log deleted

auxiliary instance file /tmp/oracle/recover/QCWJ_PITR_ORCL12/datafile/o1_mf_users_9svc4cpo_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_sysaux_9svbxqsq_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_undotbs1_9svbxqt2_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/datafile/o1_mf_system_9svbxqt0_.dbf deleted

auxiliary instance file /tmp/oracle/recover/ORCL12/controlfile/o1_mf_9svbxbpc_.ctl deleted

auxiliary instance file tspitr_qcwj_53892.dmp deleted

Finished recover at 15-JUN-14

Recovery Manager complete.

SCOTT@orcl12> select * from EMP_RECOVERED

2 ;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30

7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30

7566 JONES MANAGER 7839 02-APR-81 2975 20

7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30

7698 BLAKE MANAGER 7839 01-MAY-81 2850 30

7782 CLARK MANAGER 7839 09-JUN-81 2450 10

7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

---------- ---------- --------- ---------- --------- ---------- ---------- ----------

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20

7934 MILLER CLERK 7782 23-JAN-82 1300 10

14 rows selected.

FIN...

Obsevaciones:

- Se requiere al menos 30% de espacio del backup para generar la instancia auxiliar

- se requiere al menos el mismo SGA de la instancia actual.

viernes, 6 de junio de 2014

Arquitectura ORACLE 12

1-Arquitectura de Directorios UNIX

2- Arquitectura puggable Database

3- Arquitectura de Procesos