miércoles, 31 de marzo de 2021
viernes, 19 de marzo de 2021
PRINCIPIO PRIVILEGIO MINIMO
DBA SQL> create user dbo identified by oracle123;
domingo, 7 de febrero de 2021
SOFTWARE FREE
Bizagi Modeler
Wireless Network Watcher
Map Toolkit Inventario Maquinas Windows
Gestion de Proyectos.
Jira | Software de seguimiento de proyectos e incidencias | Atlassian
VULNERABILIDADES SEGURIDAD
Nessus
Download Microsoft Assessment and Planning Toolkit from Official Microsoft Download Center
Pentaho (ETL Hitachi)
Apache superset (Visualizacion de Datos) Software free
Apache JMeter - Apache JMeter™
The Grinder, a Java Load Testing Framework (sourceforge.net)
DIAGRAMAS
CONTRASEÑAS
https://bitwarden.com/
Zabbix: una herramienta de monitoreo de red de código abierto que permite el monitoreo en tiempo real de redes, servidores, aplicaciones y servicios.
GLPI: una herramienta de gestión de activos de TI de código abierto que permite la gestión de inventarios de hardware y software, la gestión de tickets de soporte, la gestión de contratos y la gestión de recursos.
Inicio - GLPI Project (glpi-project.org)
OCS Inventory NG: una herramienta de inventario de hardware y software de código abierto que permite la gestión remota de activos de TI, la recopilación de información del hardware y software, y la generación de informes detallados.
Nagios: una herramienta de monitoreo de red de código abierto que permite la supervisión y alerta de servicios de red, servidores y aplicaciones.
iTop: una herramienta de gestión de servicios de TI de código abierto que permite la gestión de servicios de TI, la gestión de incidentes, la gestión de cambios, la gestión de problemas y la gestión de configuraciones.
SuiteCRM: una herramienta de gestión de relaciones con clientes (CRM) de código abierto que permite la gestión de ventas, marketing y soporte al cliente.
OpenNMS: una herramienta de gestión de red de código abierto que permite el monitoreo y gestión de redes complejas y heterogéneas.
Live optics: Collector Software
OpenVAS: una herramienta de escaneo de vulnerabilidades de código abierto que permite la detección de vulnerabilidades en sistemas y redes.
Nmap: una herramienta de escaneo de puertos de código abierto que permite la detección de puertos abiertos en sistemas y redes.
Nikto: una herramienta de escaneo de vulnerabilidades web de código abierto que permite la detección de vulnerabilidades en aplicaciones web.
Snort: una herramienta de detección de intrusiones de código abierto que permite la detección de ataques en tiempo real en redes.
OSSEC: una herramienta de detección de intrusiones de código abierto que permite la detección de ataques en tiempo real en sistemas.
Wireshark: una herramienta de análisis de tráfico de red de código abierto que permite la captura y análisis de paquetes de red para detectar posibles vulnerabilidades y ataques.
Metasploit: una herramienta de pruebas de penetración de código abierto que permite la simulación de ataques para detectar vulnerabilidades en sistemas y redes.
Cacti: Es una herramienta de gráficos y monitoreo basada en RRDTool. Permite visualizar datos de rendimiento en forma de gráficos y ayudar a identificar patrones y tendencias.
Git: Es un sistema de control de versiones distribuido. Permite gestionar el código fuente y colaborar en proyectos de desarrollo de software de manera eficiente.
Sweet Home 3D: Es una herramienta de diseño de interiores gratuita y de código abierto que permite a los usuarios crear modelos en 3D de sus hogares. La herramienta cuenta con una gran biblioteca de muebles y objetos decorativos que pueden ser utilizados para crear diseños personalizados.
Blender: Aunque es una herramienta de modelado 3D más general, Blender se puede utilizar para el diseño de interiores también. Es una herramienta muy potente que permite a los usuarios crear modelos en 3D, animaciones y gráficos. Es un poco más complicado de usar que Sweet Home 3D, pero también ofrece una amplia variedad de herramientas de diseño y opciones de personalización.
SketchUp: Es una herramienta de modelado 3D que es especialmente popular entre los arquitectos y diseñadores de interiores. SketchUp es fácil de usar y cuenta con una amplia variedad de herramientas para crear diseños precisos y detallados.
RoomSketcher: Es una herramienta de diseño de interiores en línea que permite a los usuarios crear planos de planta, diseños de interiores y renderizaciones en 3D. RoomSketcher es fácil de usar y cuenta con una gran variedad de herramientas para crear diseños personalizados.
viernes, 5 de febrero de 2021
GRANULO MEMORIA
SQL> select (bytes/1024/1024) from v$sgainfo where name='Granule Size';
(BYTES/1024/1024)
-----------------
4
SQL> select (bytes) from v$sgainfo where name='Granule Size';
BYTES
----------
4194304
SQL>
miércoles, 3 de febrero de 2021
RESULT_CACHE EJEMPLO IMPLEMENTACION
select /*+ RESULT_CACHE */ department_id, avg(salary) from hr.employees group by department_id
select /*+ NO_RESULT_CACHE */ department_id, avg(salary) from hr.employees group by department_id
Consultas SQL útiles para obtener información sobre Oracle Database
Consultas SQL útiles para obtener información sobre Oracle Database
- Vista que muestra el estado de la base de datos:
select * from v$instance
- Consulta que muestra si la base de datos está abierta:
select status from v$instance
- Vista que muestra los parámetros generales de Oracle:
select * from v$system_parameter
- Versión de Oracle:
select value from v$system_parameter where name = 'compatible'
- Ubicación y nombre del fichero spfile:
select value from v$system_parameter where name = 'spfile'
- Ubicación y número de ficheros de control:
select value from v$system_parameter where name = 'control_files'
- Nombre de la base de datos
select value from v$system_parameter where name = 'db_name'
- Vista que muestra las conexiones actuales a Oracle:
select osuser, username, machine, program from v$session order by osuser
- Vista que muestra el número de conexiones actuales a Oracle agrupado por aplicación que realiza la conexión
select program Aplicacion, count(program) Numero_Sesiones from v$session group by program order by Numero_Sesiones desc
- Vista que muestra los usuarios de Oracle conectados y el número de sesiones por usuario
select username Usuario_Oracle, count(username) Numero_Sesiones from v$session group by username order by Numero_Sesiones desc
- Propietarios de objetos y número de objetos por propietario
select owner, count(owner) Numero from dba_objects group by owner order by Numero desc
- Diccionario de datos (incluye todas las vistas y tablas de la Base de Datos):
select * from dictionary select table_name from dictionary
- Muestra los datos de una tabla especificada (en este caso todas las tablas que lleven la cadena "EMPLO"):
select * from ALL_ALL_TABLES where upper(table_name) like '%EMPLO%'
- Muestra los disparadores (triggers) de la base de datos Oracle Database:
select * from ALL_TRIGGERS
- Tablas propiedad del usuario actual:
select * from user_tables
- Todos los objetos propiedad del usuario conectado a Oracle:
select * from user_catalog
- Consulta SQL para el DBA de Oracle que muestra los tablespaces, el espacio utilizado, el espacio libre y los ficheros de datos de los mismos:
Select t.tablespace_name "Tablespace", t.status "Estado",
ROUND(MAX(d.bytes)/1024/1024,2) "MB Tamaño",
ROUND((MAX(d.bytes)/1024/1024) -
(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024),2) "MB Usados",
ROUND(SUM(decode(f.bytes, NULL,0, f.bytes))/1024/1024,2) "MB Libres",
t.pct_increase "% incremento",
SUBSTR(d.file_name,1,80) "Fichero de datos"
FROM DBA_FREE_SPACE f, DBA_DATA_FILES d, DBA_TABLESPACES t
WHERE t.tablespace_name = d.tablespace_name AND
f.tablespace_name(+) = d.tablespace_name
AND f.file_id(+) = d.file_id GROUP BY t.tablespace_name,
d.file_name, t.pct_increase, t.status ORDER BY 1,3 DESC
- Productos Oracle instalados y la versión:
select * from product_component_version
- Roles y privilegios por roles:
select * from role_sys_privs
- Reglas de integridad y columna a la que afectan:
select constraint_name, column_name from sys.all_cons_columns
- Tablas de las que es propietario un usuario, en este caso "HR":
SELECT table_owner, table_name from sys.all_synonyms where table_owner like 'HR'
- Otra forma más efectiva (tablas de las que es propietario un usuario):
SELECT DISTINCT TABLE_NAME FROM ALL_ALL_TABLES WHERE OWNER LIKE 'HR'
- Parámetros de Oracle, valor actual y su descripción:
SELECT v.name, v.value value, decode(ISSYS_MODIFIABLE, 'DEFERRED',
'TRUE', 'FALSE') ISSYS_MODIFIABLE, decode(v.isDefault, 'TRUE', 'YES',
'FALSE', 'NO') "DEFAULT", DECODE(ISSES_MODIFIABLE, 'IMMEDIATE',
'YES','FALSE', 'NO', 'DEFERRED', 'NO', 'YES') SES_MODIFIABLE,
DECODE(ISSYS_MODIFIABLE, 'IMMEDIATE', 'YES', 'FALSE', 'NO',
'DEFERRED', 'YES','YES') SYS_MODIFIABLE , v.description
FROM V$PARAMETER v
WHERE name not like 'nls%' ORDER BY 1
- Usuarios de Oracle y todos sus datos (fecha de creación, estado, id, nombre, tablespace temporal,...):
Select * FROM dba_users
- Tablespaces y propietarios de los mismos:
select owner, decode(partition_name, null, segment_name, segment_name || ':' || partition_name) name, segment_type, tablespace_name,bytes,initial_extent, next_extent, PCT_INCREASE, extents, max_extents from dba_segments Where 1=1 And extents > 1 order by 9 desc, 3
- Últimas consultas SQL ejecutadas en Oracle y usuario que las ejecutó:
select distinct vs.sql_text, vs.sharable_mem, vs.persistent_mem, vs.runtime_mem, vs.sorts, vs.executions, vs.parse_calls, vs.module, vs.buffer_gets, vs.disk_reads, vs.version_count, vs.users_opening, vs.loads, to_char(to_date(vs.first_load_time, 'YYYY-MM-DD/HH24:MI:SS'),'MM/DD HH24:MI:SS') first_load_time, rawtohex(vs.address) address, vs.hash_value hash_value , rows_processed , vs.command_type, vs.parsing_user_id , OPTIMIZER_MODE , au.USERNAME parseuser from v$sqlarea vs , all_users au where (parsing_user_id != 0) AND (au.user_id(+)=vs.parsing_user_id) and (executions >= 1) order by buffer_gets/executions desc
- Todos los ficheros de datos y su ubicación:
select * from V$DATAFILE
- Ficheros temporales:
select * from V$TEMPFILE
- Tablespaces:
select * from V$TABLESPACE
- Otras vistas muy interesantes:
select * from V$BACKUP select * from V$ARCHIVE select * from V$LOG select * from V$LOGFILE select * from V$LOGHIST select * from V$ARCHIVED_LOG select * from V$DATABASE
- Memoria Share_Pool libre y usada:
select name,to_number(value) bytes from v$parameter where name ='shared_pool_size' union all select name,bytes from v$sgastat where pool = 'shared pool' and name = 'free memory'
select * from v$sgastat where name='free memory';
- Cursores abiertos por usuario:
select b.sid, a.username, b.value Cursores_Abiertos
from v$session a,
v$sesstat b,
v$statname c
where c.name in ('opened cursors current')
and b.statistic# = c.statistic#
and a.sid = b.sid
and a.username is not null
and b.value >0
order by 3
- Aciertos de la caché (no debe superar el 1 por ciento):
select sum(pins) Ejecuciones, sum(reloads) Fallos_cache,
trunc(sum(reloads)/sum(pins)*100,2) Porcentaje_aciertos
from v$librarycache
where namespace in ('TABLE/PROCEDURE','SQL AREA','BODY','TRIGGER');
- Sentencias SQL completas ejecutadas con un texto determinado en el SQL:
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text FROM v$session c, v$sqltext d WHERE c.sql_hash_value = d.hash_value and upper(d.sql_text) like '%WHERE CAMPO LIKE%' ORDER BY c.sid, d.piece
- Una sentencia SQL concreta (filtrado por sid):
SELECT c.sid, d.piece, c.serial#, c.username, d.sql_text FROM v$session c, v$sqltext d WHERE c.sql_hash_value = d.hash_value and sid = 105 ORDER BY c.sid, d.piece
- Tamaño ocupado por la base de datos
select sum(BYTES)/1024/1024 MB from DBA_EXTENTS
- Tamaño de los ficheros de datos de la base de datos:
select sum(bytes)/1024/1024 MB from dba_data_files
- Tamaño ocupado por una tabla concreta sin incluir los índices de la misma
select sum(bytes)/1024/1024 MB from user_segments where segment_type='TABLE' and segment_name='NOMBRETABLA'
- Tamaño ocupado por una tabla concreta incluyendo los índices de la misma
select sum(bytes)/1024/1024 Table_Allocation_MB
from user_segments
where segment_type in ('TABLE','INDEX') and
(segment_name='NOMBRETABLA' or segment_name in
(select index_name
from user_indexes
where table_name='NOMBRETABLA'))
- Tamaño ocupado por una columna de una tabla:
select sum(vsize('NOMBRECOLUMNA'))/1024/1024 MB
from NOMBRETABLA
- Espacio ocupado por usuario:
SELECT owner, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB GROUP BY owner
- Espacio ocupado por los diferentes segmentos (tablas, índices, undo, rollback, cluster, ...):
SELECT SEGMENT_TYPE, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB GROUP BY SEGMENT_TYPE
- Espacio ocupado por todos los objetos de la base de datos, muestra los objetos que más ocupan primero:
SELECT SEGMENT_NAME, SUM(BYTES)/1024/1024 FROM DBA_EXTENTS MB GROUP BY SEGMENT_NAME ORDER BY 2 DESC
- Obtener todas las funciones de Oracle: NVL, ABS, LTRIM, ...:
SELECT distinct object_name FROM all_arguments WHERE package_name = 'STANDARD' order by object_name
- Obtener los roles existentes en Oracle Database:
select * from DBA_ROLES
- Obtener los privilegios otorgados a un rol de Oracle:
select privilege from dba_sys_privs where grantee = 'NOMBRE_ROL'
- Obtener la IP del servidor de la base de datos Oracle Database:
select utl_inaddr.get_host_address IP from dual
Mostrar datos de auditoría de la base de datos Oracle (inicio y desconexión de sesiones):
select username, action_name, priv_used, returncode from dba_audit_trail
Comprobar si la auditoría de la base de datos Oracle está activada:
select name, value from v$parameter where name like 'audit_trail'
lunes, 1 de febrero de 2021
BITCOIN HALVING
HALVING
| # HALVING | # BLOQUE | RECOMPENSA ACTIVA | # BTC INTRODUCIDO | AÑO |
| 0 | 0 | 50 | 10500000,00000000 | 2008 |
| 1 | 210000 | 25 | 5250000,00000000 | 2012 |
| 2 | 420000 | 12,5 | 2625000,00000000 | 2016 |
| 3 | 630000 | 6,25 | 1312500,00000000 | 2020 |
| 4 | 840000 | 3,125 | 656250,00000000 | 2024 |
| 5 | 1050000 | 1,5625 | 328125,00000000 | 2028 |
| 10 | 2100000 | 0,04882813 | 10253,90625000 | 2048 |
| 15 | 3150000 | 0,00152588 | 320,43457031 | 2068 |
| 20 | 4200000 | 0,00004768 | 10,01358032 | 2088 |
| 25 | 5250000 | 0,00000149 | 0,31292439 | 2108 |
| 30 | 6300000 | 0,00000005 | 0,00977889 | 2128 |
| 34 | 7140000 | 0,00000000 | 0,00061118 | 2144 |
| TOTAL DE BITCOINS | 20.999.999,99938880 | |||
DELETE INCIDENTES
SQL> begin
2 delete from mgmt_current_severity;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
begin
delete from mgmt_current_severity;
end;
The Driving Table
The table of interest in the SYSMAN schema is named MGMT_SEVERITY.
USER is "SYSTEM" SQL> conn sysman/oracle Connected. USER is "SYSMAN" SQL> desc mgmt_severity Name Null? Type ----------------------------------------- -------- -------------- TARGET_GUID NOT NULL RAW(16) METRIC_GUID NOT NULL RAW(16) KEY_VALUE NOT NULL VARCHAR2(256) COLLECTION_TIMESTAMP NOT NULL DATE LOAD_TIMESTAMP DATE SEVERITY_CODE NOT NULL NUMBER SEVERITY_TYPE NUMBER SEVERITY_DURATION NUMBER SEVERITY_GUID RAW(16) ANNOTATED_FLAG NUMBER NOTIFICATION_STATUS NUMBER MESSAGE VARCHAR2(4000) MESSAGE_NLSID VARCHAR2(64) MESSAGE_PARAMS VARCHAR2(4000) ACTION_MESSAGE VARCHAR2(4000) ACTION_NLSID VARCHAR2(64) ACTION_MESSAGE_PARAMS VARCHAR2(4000) ADVISORY_ID VARCHAR2(64) USER_NAME VARCHAR2(64)
Aside from the RAW datatypes, working with data in this table should be fairly straightforward, that is, once you know what to look for. The table’s primary key is a composite key, and uses TARGET_GUID, METRIC_GUID, KEY_VALUE, COLLECTION_TIMESTAMP, and SEVERITY_CODE.
You won’t find the RAW values anywhere on the page, even if you look at the page’s source. If you try that approach, mainly as an exercise to see what the underlying HTML code looks like, all you’re going to see (at the bottom of the text editor page) are a few lines of code, with one of them stretching out well over 30,000 characters.
The two best telltales in the table are KEY_VALUE and COLLECTION_TIMESTAMP. The KEY_VALUE values have a pattern to them. If the message is about a tablespace having crossed a space threshold, then the KEY_VALUE is simply the name of the tablespace. If the message is about SYS having logged on, the KEY_VALUE will be a mixture of “SYS” plus “_
For the most part, the date and time shown under Alert Triggered will appear as the value for COLLECTION_TIMESTAMP. Between that time and the “key values” in the KEY_VALUE column, you should be sufficiently armed to invoke a procedure named DELETE_CURRENT_SEVERITY in the EM_SEVERITY package.
PROCEDURE DELETE_CURRENT_SEVERITY Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- P_TARGET_GUID RAW IN P_METRIC_GUID RAW IN P_KEY_VALUE VARCHAR2 IN
This procedure deletes data from a table named MGMT_CURRENT_SEVERITY.
BEGIN
DELETE FROM MGMT_CURRENT_SEVERITY
WHERE target_guid = p_target_guid
AND metric_guid = p_metric_guid
AND key_value = p_key_value;
END delete_current_severity;
On the face of it, this approach seems like a safe way to go about clearing stale messages, but there is a better way, and that way is to delete directly from the MGMT_SEVERITY table and let the SEVERITY_DELETE trigger do all the work for you.
lunes, 25 de enero de 2021
PRIVILEGIOS PLANES DE EJECUCION PLAN TABLE
Rol SELECT_CATALOG_ROLE:
SQL> grant select_catalog_role to user1;
Grant succeeded.
Privilegio SELECT ANY DICTIONARY:
SQL> grant select any dictionary to user1;
Grant succeeded.
trcsess and tkprof.
trcsess output=orcl_trace.txt service=orcl orcl_ora_4143.trc orcl_ora_4186.trc orcl_ora_4196.trc
$ tkprof orcl_trace.trc resultado.txt sys=no
$ tkprof orcl_trace.trc resultado.txt sys=no sort=execpu
tkprof orcl_trace.trc resultado.txt sys=no sort=exeela
tkprof orcl_ora_4196.trc resultado.txt sys=no
ACTIVAR TRACE
EXEC DBMS_MONITOR.session_trace_enable(waits=>TRUE, binds=>TRUE);EXEC DBMS_MONITOR.session_trace_disable;VISTA VER TRACES DBA_EANBLED_TRACES
sábado, 23 de enero de 2021
RMAN LIST BACKUP ARCHIVELOG
RMAN>
RMAN> list backup of archivelog logseq=120316; RMAN> list backup of archivelog logseq 120316; RMAN> list backup of archivelog sequence 120316;
To view backups of archivelog between two sequences:
RMAN> list backup of archivelog sequence between 120316 and 120317; RMAN> list backup of archivelog from logseq 412593 until logseq 412656;
Use the SUMMARY directive to view only the backupsets affected:
RMAN> list backup of archivelog from logseq 412593 until logseq 412656 summary; RMAN> list backup of archivelog sequence between 120316 and 120317 summary;
To view backups completed before a specific date:
RMAN> list backup completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')"; RMAN> list backup of archivelog all summary completed before "to_date( '18.12.2009 18:00:00', 'DD.MM.YYYY HH24:MI:SS')";
To list archivelogs recognized by the controlfile:
list archivelog sequence between 110880 and 110881;
lunes, 18 de enero de 2021
VISTAS METRICAS ALERTAS ALERT_HISTORY
VISTA
select * from dba_alert_history
select * from dba_outstanding_alerts
select * from dba_thresholds
select * from v$alert_types
REGISTROS TABLA FILTRADO X TABLESPACE
SQL> exec dbms_stats.gather_schema_stats('HR');
where owner='HR'
and TABLESPACE_NAME='EXAMPLE'
order by num_rows desc
viernes, 15 de enero de 2021
AWR CONFIGURACION RETENTION
Snapshots and AWR Snapshot Retention
By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.
BEGIN
DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
retention => 43200, -- Minutes (= 30 Days). Current value retained if NULL.
interval => 60); -- Minutes. Current value retained if NULL.
END;
/CONSULTAR VISTA DBA_HIST_SNAPSHOTEXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot; BEGIN DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range ( low_snap_id => 22, high_snap_id => 32); END; /
execute dbms_workload_repository.DROP_SNAPSHOT_RANGE(1959,1966);
CREAR SNAPSHOT AWR
CREAR UN SNAPSHOT TOMAR FOTO
SQL> exec dbms_workload_repository.create_snapshot();
select SNAP_ID, BEGIN_INTERVAL_TIME, END_INTERVAL_TIMEfrom DBA_HIST_SNAPSHOTorder by 1;SQL>execute dbms_workload_repository.DROP_SNAPSHOT_RANGE(1959,1966);
lunes, 14 de diciembre de 2020
RMAN RESTORE TABLE TPITR
# SCN
RECOVER TABLE TEST.T1 UNTIL SCN 1853267 AUXILIARY DESTINATION '/u01/backup' REMAP TABLE 'TEST'.'T1':'T1_PREV';
RECOVER TABLE TEST.T1 UNTIL SEQUENCE 200 AUXILIARY DESTINATION '/u01/backup' REMAP TABLE 'TEST'.'T1':'T1_PREV';
# TIME
RECOVER TABLE TEST.T1
UNTIL TIME "TO_DATE('2020-12-14 19:00', 'YYYY-MM-DD:HH24:MI:SS')"
AUXILIARY DESTINATION '/u01/backup'
REMAP TABLE 'TEST'.'T1':'T1_PREV';TECNOLOGIA TPITR
LABORATORIO RESTORE TABLE UNTIL TIME
SQL> !date
Mon Dec 14 19:13:47 -05 2020
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
6003465
SQL> connect hr/hr;
Connected.
SQL>
SQL> update hr.lolito set salary=0, email='NELSONJAVIER63@GMAIL.COM';
107 rows updated.
SQL> commit;
Commit complete.
RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';
RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';
Starting recover at 2020-12-14:19:23:07
current log archived
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/14/2020 19:23:09
RMAN-05063: Cannot recover specified tables
RMAN-05112: table "HR"."LOLITO" already exists
RMAN> RECOVER TABLE HR.LOLITO UNTIL TIME "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')" AUXILIARY DESTINATION '/u01/backup/';
Starting recover at 2020-12-14:19:23:49
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=72 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='pfuh'
initialization parameters used for automatic instance:
db_name=ACME
db_unique_name=pfuh_pitr_ACME
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/backup/
log_archive_dest_1='location=/u01/backup/'
#No auxiliary parameter file used
starting up automatic instance ACME
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 281019648 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2020-12-14:19:24:01
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711
channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/AUTOBACKUP/2020_12_14/s_1059158709.366.1059158711 tag=TAG20201214T184509
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:06
output file name=/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl
Finished restore at 2020-12-14:19:24:07
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/backup/ACME/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2020-12-14:19:24:13
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ACME/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ACME/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ACME/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619
channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:45
Finished restore at 2020-12-14:19:24:59
datafile 1 switched to datafile copy
input datafile copy RECID=11 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=13 STAMP=1059161099 file name=/u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
Starting recover at 2020-12-14:19:24:59
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705
archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987
archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14
archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15
media recovery complete, elapsed time: 00:00:09
Finished recover at 2020-12-14:19:25:11
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter system set control_files = ''/u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2020-12-14:19:25:46
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=27 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619
channel ORA_AUX_DISK_1: piece handle=+FRA/ACME/BACKUPSET/2020_12_14/nnndf0_tag20201214t184339_0.362.1059158619 tag=TAG20201214T184339
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2020-12-14:19:25:49
datafile 6 switched to datafile copy
input datafile copy RECID=15 STAMP=1059161149 file name=/u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "TO_DATE('2020-12-14 19:13:47', 'YYYY-MM-DD:HH24:MI:SS')";
# online the datafiles restored or switched
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 6 online
Starting recover at 2020-12-14:19:25:49
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 14 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705
archived log for thread 1 with sequence 15 is already on disk as file +FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987
archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_14.367.1059158705 thread=1 sequence=14
archived log file name=+FRA/ACME/ARCHIVELOG/2020_12_14/thread_1_seq_15.266.1059160987 thread=1 sequence=15
media recovery complete, elapsed time: 00:00:00
Finished recover at 2020-12-14:19:25:51
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup/''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_pfuh_nDAC":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '+DATA/ACME/DATAFILE/example.259.1058124367'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
EXPDP> . . exported "HR"."LOLITO" 17.07 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_pfuh_nDAC" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_pfuh_nDAC is:
EXPDP> /u01/backup/tspitr_pfuh_82509.dmp
EXPDP> Job "SYS"."TSPITR_EXP_pfuh_nDAC" completed with 2 error(s) at Mon Dec 14 19:27:12 2020 elapsed 0 00:00:52
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_pfuh_pkrj" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_pfuh_pkrj":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "HR"."LOLITO" 17.07 KB 107 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_pfuh_pkrj" successfully completed at Mon Dec 14 19:28:00 2020 elapsed 0 00:00:30
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/backup/ACME/datafile/o1_mf_temp_hxj0m83n_.tmp deleted
auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_3_hxj0nj8c_.log deleted
auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_2_hxj0nhnq_.log deleted
auxiliary instance file /u01/backup/PFUH_PITR_ACME/onlinelog/o1_mf_1_hxj0nh58_.log deleted
auxiliary instance file /u01/backup/PFUH_PITR_ACME/datafile/o1_mf_users_hxj0nd73_.dbf deleted
auxiliary instance file /u01/backup/ACME/datafile/o1_mf_sysaux_hxj0kg5h_.dbf deleted
auxiliary instance file /u01/backup/ACME/datafile/o1_mf_undotbs1_hxj0kg5t_.dbf deleted
auxiliary instance file /u01/backup/ACME/datafile/o1_mf_system_hxj0kg5n_.dbf deleted
auxiliary instance file /u01/backup/ACME/controlfile/o1_mf_hxj0k64p_.ctl deleted
auxiliary instance file tspitr_pfuh_82509.dmp deleted
Finished recover at 2020-12-14:19:28:06
RMAN>
TECNOLOGIA TPITR
LABORATORIO RESTORE TABLE SCN
SQL> create table hr.lolito as (select * from hr.employees);
RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';
Starting recover at 14-DEC-20
using target database control file instead of recovery catalog
current log archived
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/14/2020 18:58:42
RMAN-05063: Cannot recover specified tables
RMAN-05112: table "HR"."LOLITO" already exists
RMAN> RECOVER TABLE HR.LOLITO UNTIL SCN 6223707 AUXILIARY DESTINATION '/u01/backup/';
Starting recover at 14-DEC-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
RMAN-05026: WARNING: presuming following set of tablespaces applies to specified Point-in-Time
List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace UNDOTBS1
Creating automatic instance, with SID='haer'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=haer_pitr_ORCL
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/backup/
log_archive_dest_1='location=/u01/backup/'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 281019648 bytes
Database Buffers 780140544 bytes
Redo Buffers 5480448 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 6223707;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log
sql 'alter system archive log current';
}
executing Memory Script
executing command: SET until clause
Starting restore at 14-DEC-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=7 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/autobackup/2020_12_14/o1_mf_s_1059158574_hxhy3zj0_.bkp tag=TAG20201214T184254
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl
Finished restore at 14-DEC-20
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn 6223707;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 5, 4, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /u01/backup/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 14-DEC-20
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/backup/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/backup/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/backup/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 14-DEC-20
datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=8 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=10 STAMP=1059159666 file name=/u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 6223707;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 3 online
Starting recover at 14-DEC-20
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-DEC-20
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter system set control_files = ''/u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 285213952 bytes
Database Buffers 775946240 bytes
Redo Buffers 5480448 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 6223707;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 6 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 14-DEC-20
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00006 to /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2020_12_14/o1_mf_nnnd0_TAG20201214T184159_hxhy27q3_.bkp tag=TAG20201214T184159
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 14-DEC-20
datafile 6 switched to datafile copy
input datafile copy RECID=12 STAMP=1059159701 file name=/u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 6223707;
# online the datafiles restored or switched
sql clone "alter database datafile 6 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 6 online
Starting recover at 14-DEC-20
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 12 is already on disk as file /u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc
archived log file name=/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2020_12_14/o1_mf_1_12_hxhz1klk_.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:00
Finished recover at 14-DEC-20
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/backup/''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/backup/''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_haer_peEB":
EXPDP> Estimate in progress using BLOCKS method...
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 64 KB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_ORDERS_QUEUETABLE_S','IX',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
ORA-39127: unexpected error from call to export_string :=SYS.DBMS_TRANSFORM_EXIMP.INSTANCE_INFO_EXP('AQ$_STREAMS_QUEUE_TABLE_S','IX',1,1,'12.01.00.00.00',newblock)
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_hwjdb5y7_.dbf'
ORA-06512: at "SYS.DBMS_TRANSFORM_EXIMP", line 197
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_METADATA", line 9901
EXPDP> . . exported "HR"."LOLITO" 17.07 KB 107 rows
EXPDP> Master table "SYS"."TSPITR_EXP_haer_peEB" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_haer_peEB is:
EXPDP> /u01/backup/tspitr_haer_59331.dmp
EXPDP> Job "SYS"."TSPITR_EXP_haer_peEB" completed with 2 error(s) at Mon Dec 14 19:03:12 2020 elapsed 0 00:00:50
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_haer_pAbb" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_haer_pAbb":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "HR"."LOLITO" 17.07 KB 107 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_haer_pAbb" successfully completed at Mon Dec 14 19:03:56 2020 elapsed 0 00:00:30
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_temp_hxhz666l_.tmp deleted
auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_3_hxhz774g_.log deleted
auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_2_hxhz76pt_.log deleted
auxiliary instance file /u01/backup/HAER_PITR_ORCL/onlinelog/o1_mf_1_hxhz76cr_.log deleted
auxiliary instance file /u01/backup/HAER_PITR_ORCL/datafile/o1_mf_users_hxhz743t_.dbf deleted
auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_sysaux_hxhz41l5_.dbf deleted
auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_undotbs1_hxhz41lo_.dbf deleted
auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lw_.dbf deleted
auxiliary instance file /u01/backup/ORCL/datafile/o1_mf_system_hxhz41lk_.dbf deleted
auxiliary instance file /u01/backup/ORCL/controlfile/o1_mf_hxhz3two_.ctl deleted
auxiliary instance file tspitr_haer_59331.dmp deleted
Finished recover at 14-DEC-20
RMAN>
