ROMFASTSQL/proxmox/lxc108-oracle/scripts/export-roa2.ps1

# Export ROA2 schemas și copiere pe Windows
# Rulează din PowerShell: .\export-roa2.ps1

$BackupDir = "E:\backups\oracle"
$Proxmox = "root@10.0.20.201"
$DmpDir21 = "/opt/oracle/oradata/dmpdir"    # Oracle 21c
$DmpDir18 = "/opt/oracle18/oradata/dmpdir"  # Oracle 18c

# Creează directorul backup dacă nu există
if (!(Test-Path $BackupDir)) {
    New-Item -ItemType Directory -Path $BackupDir | Out-Null
}

function Get-RemoteArchives {
    param([string]$DmpDir = "")

    # Dacă nu e specificat, caută în ambele directoare
    if ([string]::IsNullOrEmpty($DmpDir)) {
        $dirs = "$DmpDir18 $DmpDir21"
    } else {
        $dirs = $DmpDir
    }

    $result = ssh $Proxmox "pct exec 108 -- bash -c 'for d in $dirs; do ls -lt `$d/*.tar.gz 2>/dev/null; done'" 2>$null
    if ($result) {
        $archives = @()
        foreach ($line in $result) {
            if ($line -match '(\S+\.tar\.gz)$') {
                $fullPath = $matches[1]
                $name = Split-Path $fullPath -Leaf
                $size = ($line -split '\s+')[4]
                $date = ($line -split '\s+')[5..7] -join ' '
                $archives += [PSCustomObject]@{
                    Name = $name
                    Size = $size
                    Date = $date
                    Path = $fullPath
                }
            }
        }
        return $archives
    }
    return @()
}

function Copy-Archive($archivePath) {
    $name = Split-Path $archivePath -Leaf
    Write-Host "  Copying: $name..." -NoNewline
    ssh $Proxmox "pct pull 108 $archivePath /tmp/$name"
    scp "${Proxmox}:/tmp/$name" "$BackupDir\" 2>$null
    ssh $Proxmox "rm -f /tmp/$name" 2>$null
    Write-Host " Done" -ForegroundColor Green
}

function Start-OracleContainer {
    param([string]$Container)

    Write-Host "Pornesc containerul $Container..." -ForegroundColor Yellow
    ssh $Proxmox "pct exec 108 -- docker start $Container"
    Write-Host "Astept 60s pentru startup Oracle..." -ForegroundColor Yellow
    Start-Sleep -Seconds 60

    # Verifică dacă e pornit
    $status = ssh $Proxmox "pct exec 108 -- docker ps --format '{{.Names}}' | grep $Container"
    if ($status) {
        Write-Host "Container $Container pornit cu succes!" -ForegroundColor Green
        return $true
    } else {
        Write-Host "EROARE: Container $Container nu a pornit!" -ForegroundColor Red
        return $false
    }
}

function Run-Export {
    Write-Host "`nSelecteaza sursa Oracle:" -ForegroundColor Cyan
    Write-Host "  1. Oracle 18c (port 1522) - Compatibil 11g/18c/19c (Recomandat)"
    Write-Host "  2. Oracle 21c (port 1521) - ROA2"

    $srcChoice = Read-Host "Selecteaza sursa [1]"
    $oracleVer = switch ($srcChoice) {
        '2' { '21'; $containerName = 'oracle-xe' }
        default { '18'; $containerName = 'oracle18-xe' }
    }

    if ($oracleVer -eq '18') {
        $containerName = 'oracle18-xe'
        $currentDmpDir = $DmpDir18
    } else {
        $containerName = 'oracle-xe'
        $currentDmpDir = $DmpDir21
    }

    # Verifică dacă LXC 108 rulează
    Write-Host "`nVerific LXC 108..." -NoNewline
    $lxcStatus = ssh $Proxmox "pct status 108" 2>$null
    if ($lxcStatus -notmatch "running") {
        Write-Host " OPRIT" -ForegroundColor Red
        $start = Read-Host "Pornesc LXC 108? (D/n)"
        if ($start -ne 'n') {
            Write-Host "Pornesc LXC 108..." -ForegroundColor Yellow
            ssh $Proxmox "pct start 108"
            Start-Sleep -Seconds 10
        } else {
            Write-Host "Anulat." -ForegroundColor Gray
            return
        }
    } else {
        Write-Host " OK" -ForegroundColor Green
    }

    # Verifică dacă containerul Oracle rulează
    Write-Host "Verific container $containerName..." -NoNewline
    $containerStatus = ssh $Proxmox "pct exec 108 -- docker ps --format '{{.Names}}' | grep $containerName" 2>$null
    if (-not $containerStatus) {
        Write-Host " OPRIT" -ForegroundColor Red
        $start = Read-Host "Pornesc $containerName? (D/n)"
        if ($start -ne 'n') {
            if (-not (Start-OracleContainer -Container $containerName)) {
                return
            }
        } else {
            Write-Host "Anulat." -ForegroundColor Gray
            return
        }
    } else {
        Write-Host " OK" -ForegroundColor Green
    }

    if ($oracleVer -eq '18') {
        Write-Host "`nExport din Oracle 18c (TSTZ 31 - compatibil 11g/18c/19c)..." -ForegroundColor Green
    } else {
        Write-Host "`nExport din Oracle 21c ROA2 (TSTZ 35 - doar 21c+)..." -ForegroundColor Yellow
    }

    ssh $Proxmox "pct exec 108 -- bash /opt/oracle/oradata/export-roa2.sh $oracleVer"

    # Copiază cele mai noi 2 arhive din directorul corect
    Write-Host "`nCopying new archives..." -ForegroundColor Yellow
    $archives = Get-RemoteArchives -DmpDir $currentDmpDir | Select-Object -First 2
    foreach ($archive in $archives) {
        Copy-Archive $archive.Path
    }
}

function Select-Archives {
    $archives = Get-RemoteArchives
    if ($archives.Count -eq 0) {
        Write-Host "No archives found on LXC 108!" -ForegroundColor Red
        return
    }

    Write-Host "`nAvailable archives:" -ForegroundColor Cyan
    for ($i = 0; $i -lt $archives.Count; $i++) {
        Write-Host "  $($i + 1). $($archives[$i].Name) [$($archives[$i].Size)] - $($archives[$i].Date)"
    }
    Write-Host "  A. All archives"
    Write-Host "  0. Cancel"

    $choice = Read-Host "`nSelect archives (e.g., 1,3 or A for all)"

    if ($choice -eq '0' -or [string]::IsNullOrWhiteSpace($choice)) {
        return
    }

    Write-Host "`nCopying to $BackupDir..." -ForegroundColor Yellow

    if ($choice -eq 'A' -or $choice -eq 'a') {
        foreach ($archive in $archives) {
            Copy-Archive $archive.Path
        }
    } else {
        $indices = $choice -split ',' | ForEach-Object { [int]$_.Trim() - 1 }
        foreach ($i in $indices) {
            if ($i -ge 0 -and $i -lt $archives.Count) {
                Copy-Archive $archives[$i].Path
            }
        }
    }
}

function Clean-RemoteArchives {
    $archives = Get-RemoteArchives
    if ($archives.Count -eq 0) {
        Write-Host "No archives to clean!" -ForegroundColor Yellow
        return
    }

    Write-Host "`nArchives on LXC 108:" -ForegroundColor Cyan
    for ($i = 0; $i -lt $archives.Count; $i++) {
        Write-Host "  $($i + 1). $($archives[$i].Name) [$($archives[$i].Size)] - $($archives[$i].Date)"
    }

    Write-Host "`n  A. Delete ALL archives"
    Write-Host "  0. Cancel"

    $choice = Read-Host "`nSelect archives to delete (e.g., 1,3 or A for all)"

    if ($choice -eq '0' -or [string]::IsNullOrWhiteSpace($choice)) {
        return
    }

    $toDelete = @()
    if ($choice -eq 'A' -or $choice -eq 'a') {
        $toDelete = $archives
    } else {
        $indices = $choice -split ',' | ForEach-Object { [int]$_.Trim() - 1 }
        foreach ($i in $indices) {
            if ($i -ge 0 -and $i -lt $archives.Count) {
                $toDelete += $archives[$i]
            }
        }
    }

    if ($toDelete.Count -gt 0) {
        Write-Host "`nDeleting $($toDelete.Count) archive(s)..." -ForegroundColor Yellow
        foreach ($archive in $toDelete) {
            ssh $Proxmox "pct exec 108 -- rm -f $($archive.Path)"
            Write-Host "  Deleted: $($archive.Name)" -ForegroundColor Red
        }
    }
}

# Main Menu
Clear-Host
Write-Host "=== Export ROA2 ===" -ForegroundColor Cyan
Write-Host ""
Write-Host "  1. Export nou si copiere arhive"
Write-Host "  2. Copiere arhive existente"
Write-Host "  3. Curatare arhive pe LXC 108"
Write-Host "  0. Iesire"
Write-Host ""

$choice = Read-Host "Selecteaza optiune"

switch ($choice) {
    '1' {
        Run-Export
    }
    '2' {
        Select-Archives
    }
    '3' {
        Clean-RemoteArchives
    }
    '0' {
        Write-Host "Bye!" -ForegroundColor Gray
        exit
    }
    default {
        Write-Host "Optiune invalida!" -ForegroundColor Red
        exit
    }
}

Write-Host "`n=== Done! ===" -ForegroundColor Cyan
Get-ChildItem $BackupDir\*.tar.gz -ErrorAction SilentlyContinue |
    Sort-Object LastWriteTime -Descending |
    Select-Object -First 6 |
    Format-Table Name, @{N='Size(MB)';E={[math]::Round($_.Length/1MB,1)}}, LastWriteTime