Con esta consulta en la base de datos, se puede identificar el porcentaje que lleva de la tarea de backup o restauración con RMAN:

–Porcentaje completado por pieza rman
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) «%_COMPLETE»
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE ‘RMAN%’
AND OPNAME NOT LIKE ‘%aggregate%’
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
;

— Rman cual pieza de backup está leyendo

select device_type «Device», type, filename, to_char(open_time, ‘mm/dd/yyyy hh24:mi:ss’) open,
to_char(close_time,’mm/dd/yyyy hh24:mi:ss’) close,elapsed_time ET, effective_bytes_per_second EPS
from v$backup_async_io
where close_time is null

I did this powershell to restore a database from SQL Server 2022 to a lower version, I had to drop the users, because it got error:

Error SQL74502: The element [ user01] cannot be deployed. This element contains state that cannot be recreated in the target database.

$PathVariables=$env:Path
$PathVariables

IF (-not $PathVariables.Contains( "C:\Program Files\Microsoft SQL Server\160\DAC\bin"))
{
write-host "SQLPackage.exe path is not found, Update the environment variable"
$env:Path = $env:Path + ";C:\Program Files\Microsoft SQL Server\160\DAC\bin;"
}

$BackupDirectory="C:\SQLServer\backup\"
$DatabaseName="DB_pruebas"

##Source SQL Server name

$SourceServerName="ServerDB"

##Nombre del archivo backup

$dirName = [io.path]::GetDirectoryName($BackupDirectory)
$filename = "DB_pruebas"

$ext= "bacpac"

##target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds

$TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext"

##Ejecutar Export

SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$DatabaseName /tf:$TargetFilePath /sec:"Optional" /p:VerifyExtraction=False;IgnoreUserSettingsObjects=true;CompressionOption=Fast;ExcludeObjectTypes="Users,ServerRoleMembership,ServerRoles,DatabaseRoles"

Este script lo utilizo para sincronizar dos directorios, en dos servidores y que solo me copie los archivos faltantes en el destino, la programo para que se ejecute desde el servidor destino

Start-Transcript -OutputDirectory D:\Log

$destino = 'G:\archivos'
$origen= '\\x.x.x.x\g$\archivos'

$ARCHFalta=Get-ChildItem (Join-Path $origen "*") -Exclude (Get-ChildItem (Join-Path $destino "*") -File | %{$_.Name})

write-output $ARCHFalta.FullName

$ARCHFalta | foreach {
Copy-Item $ARCHFalta.FullName $destino
}
Stop-Transcript

Despues de importar un esquema, si no se borraron las secuencias antes, el valor de las secuencias no se actualiza:

Con este script se genera la sentencia para cambiar el valor, para que quede igual al esquema de origen

select ‘alter sequence .’||sequence_owner||sequence_name||’ restart start with ‘|| last_number||’;’ from dba_sequences where sequence_owner=’USER01′;

alter sequence USER01.FACTURA_SEQ restart start with 1289;

select a.inst_id, a.username,a.osuser, a.sid, a.serial#, a.status, round(a.last_call_et/60) Min, a.WAIT_CLASS, 
a.event, a.machine,a.sql_id , c.sql_text, 'alter system kill session ' || '''' || sid || ',' || serial#|| '''' || ' IMMEDIATE;' cmd
from gv$session a, v$sqlarea c
where status = 'ACTIVE' 
and a.sql_hash_value = c.hash_value
and username is not null
and username <> 'SYS'  
--and username
;

— consultar archivos del tablespace actual

select tablespace_name,file_name from dba_temp_files;

— qué sesión esta usando el tablespace temp, para matarla antes de borrarlo/

select 'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' ||s.serial# || ''' immediate;',
tu.username,s.sid,s.serial#, s.status  from v$tempseg_usage tu, v$session s
where tu.session_addr=s.saddr;

— Crear un tablespace temporal para poder borrar el actual y ponerlo por defecto

CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '+DATA' SIZE 20M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

— Borrar el tablespace actual

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

— Recrear el tablespace

CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '+data' SIZE 30G;
alter tablespace temp add TEMPFILE '+data' SIZE 10G;

— volver a poner el tablespace por defecto y borrar el que se habia creado

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

SELECT a.tablespace_name,SIZEMB,
USAGEMB, (SIZEMB – USAGEMB) FREEMB
FROM ( SELECT SUM (bytes) / 1024 / 1024 SIZEMB, b.tablespace_name
FROM dba_data_files a, dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name AND b.contents like ‘UNDO’
GROUP BY b.tablespace_name) a,
( SELECT c.tablespace_name, SUM (bytes) / 1024 / 1024 USAGEMB
FROM DBA_UNDO_EXTENTS c
WHERE status <> ‘EXPIRED’
GROUP BY c.tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;