DBA Corner

Data is stored somewhere

Author: Bambino

  • Historical Oracle ANSI Join Limit: 1,050 Columns

    —

    by

    Bambino
    in

    This note refers to an old Oracle 10g issue in which very wide ANSI join queries could fail after internal query transformation. The reported threshold was roughly 1,050 projected columns, and some failures surfaced as ORA-01445. It should not be treated as a universal limit in current Oracle releases. Historical context The issue was associated…

  • Oracle: CPU Used by Each Database Session

    —

    by

    Bambino
    in

    Oracle exposes cumulative database CPU time for each session through V$SESSTAT. Join it to V$STATNAME by name—never hard-code a statistic number, because statistic numbers can change between releases. Current CPU by session The statistic is cumulative for the life of the session and is traditionally recorded in hundredths of a second. It is Oracle database…

  • Oracle IPC Resources: Map Shared Memory to an Instance

    —

    by

    Bambino
    in

    On UNIX and Linux hosts with several Oracle instances, ipcs can show many shared-memory and semaphore resources without identifying the owning database clearly. Oracle’s sysresv utility maps System V IPC resources to an Oracle SID. Report resources for an instance To check one or more explicitly named instances: Run the command as the Oracle software…

  • RMAN-10035 / ORA-19502 / ORA-27030 Media Manager Errors

    —

    by

    Bambino
    in

    When RMAN reports RMAN-10035, ORA-19502, and ORA-27030 during an SBT backup, the useful diagnosis is usually in the final media-manager message. The Oracle stack says the backup write failed; the vendor text says why. Read the error stack from the bottom ORA-19511 means the linked media-management software returned a failure. Treat the vendor message, backup-server…

  • Trigger RMAN Archivelog Backups Based on Filesystem Usage

    —

    by

    Bambino
    in

    A rapidly growing archive destination can fill during bulk loads or data migrations. A usage-triggered RMAN job is a useful emergency backstop, but it should complement—not replace—scheduled archivelog backups, monitoring, and capacity planning. If you use the fast recovery area Measure the Oracle-managed recovery area from the database rather than parsing a filesystem command: V$RECOVERY_AREA_USAGE…

  • SQL Server: Safely Attach Multiple Databases

    —

    by

    Bambino
    in

    The old sp_attach_db procedure is deprecated. Modern SQL Server uses CREATE DATABASE … FOR ATTACH, and every data and log file must be accounted for. Prefer backup and restore For planned migrations, a tested full backup and restore is usually safer and easier to audit. Attach is appropriate when you have a cleanly detached, trusted…

  • Oracle DBNEWID (NID): Change a DBID or Database Name

    —

    by

    Bambino
    in

    Oracle’s DBNEWID utility, invoked as nid, can change a database identifier (DBID), a database name (DBNAME), or both. This is useful after cloning a database that must be distinguished from its source in an RMAN repository. Understand the consequences Changing the DBID makes backups and archived logs created under the old DBID unusable for the…

  • Changing an Oracle DBID: Oracle 8i and 9i Considerations

    —

    by

    Bambino
    in

    A DBID is Oracle’s internal identifier for a database. RMAN uses it to distinguish databases, so a physical clone that retains the source DBID can conflict with the source in the same recovery catalog. Oracle 9i Release 2 and later Oracle 9i Release 2 introduced the supported DBNEWID utility. Use nid rather than manually editing…

  • Script SQL Server Agent Jobs with PowerShell and SMO

    —

    by

    Bambino
    in

    SQL-DMO and ActiveX Script job steps belong to an older SQL Server era. For current systems, use SQL Server Management Objects (SMO) from PowerShell and the maintained SqlServer module. Script every SQL Server Agent job Import-Module SqlServer $serverName = “SERVER\INSTANCE” $outputDir = “C:\DBA\SqlAgentJobs” New-Item -ItemType Directory -Force -Path $outputDir | Out-Null $server = New-Object Microsoft.SqlServer.Management.Smo.Server…

  • Monitor Oracle Processes and Threads on Windows

    —

    by

    Bambino
    in

    On Windows, Oracle database activity is easiest to diagnose by correlating Oracle sessions with operating-system process and thread identifiers. The old Quick Slice utility is no longer needed; use supported Windows tools such as Task Manager, Performance Monitor, or Process Explorer. Map sessions to Windows activity SELECT s.sid, s.serial#, s.username, s.status, s.program, p.spid, p.stid, p.pname,…