DBA-TECH.CL
miércoles, 21 de enero de 2015
viernes, 16 de enero de 2015
Maneras de Subir un Pluggable Database
1- desde CDB
SYS@CDB$ROOT> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDBLEYTON MOUNTED
SYS@CDB$ROOT> alter pluggable database pdb1 open;
Pluggable database altered.
Elapsed: 00:00:08.33
2- Desde PDB (a)
SYS@PDB1> alter pluggable database open;
Pluggable database altered.
Elapsed: 00:00:06.60
3 Desde PDB (b)
SYS@PDB1> alter database open;
Database altered.
Elapsed: 00:00:11.22
1- desde CDB
SYS@CDB$ROOT> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDBLEYTON MOUNTED
SYS@CDB$ROOT> alter pluggable database pdb1 open;
Pluggable database altered.
Elapsed: 00:00:08.33
2- Desde PDB (a)
SYS@PDB1> alter pluggable database open;
Pluggable database altered.
Elapsed: 00:00:06.60
3 Desde PDB (b)
SYS@PDB1> alter database open;
Database altered.
Elapsed: 00:00:11.22
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.
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
RECOVERY DATAFILE
1- mv pdb_data01.dbf pdb_data01.dbf.old
2- rman target sys/manager123@pdb1
3-
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')
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
gl
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. 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
Suscribirse a:
Entradas (Atom)