13114ef41d
- ExecuteScriptOS.prc: runs PowerShell scripts via DBMS_SCHEDULER - UpdateSQLPLUS.prc: runs SQL*Plus scripts via DBMS_SCHEDULER - find_oracle_locations.sql: comprehensive script to discover all Oracle DB paths for backup/migration Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>
160 lines
4.9 KiB
SQL
160 lines
4.9 KiB
SQL
-- Script: find_oracle_locations.sql
|
|
-- Afișează toate locațiile bazei de date Oracle pentru backup/migrare
|
|
-- Utilizare: sqlplus / as sysdba @find_oracle_locations.sql
|
|
-- Rezultatul se salvează în: C:\oracle_locations.txt
|
|
|
|
SET LINESIZE 200
|
|
SET PAGESIZE 100
|
|
SET TRIMSPOOL ON
|
|
SET FEEDBACK OFF
|
|
SET VERIFY OFF
|
|
|
|
SPOOL D:\ROA\oracle_locations.txt
|
|
COLUMN name FORMAT A40
|
|
COLUMN value FORMAT A100
|
|
COLUMN file_name FORMAT A80
|
|
COLUMN member FORMAT A80
|
|
|
|
PROMPT ========================================
|
|
PROMPT 1. ORACLE_HOME si ORACLE_BASE
|
|
PROMPT ========================================
|
|
SELECT 'ORACLE_HOME' as name, SYS_CONTEXT('USERENV', 'ORACLE_HOME') as value FROM dual
|
|
UNION ALL
|
|
SELECT name, value FROM v$parameter WHERE name IN ('db_name', 'db_unique_name');
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 2. SID, SERVICE_NAME si LISTENER
|
|
PROMPT ========================================
|
|
COLUMN instance_name FORMAT A20
|
|
COLUMN service_name FORMAT A30
|
|
COLUMN network_name FORMAT A30
|
|
|
|
PROMPT Instance Name (SID):
|
|
SELECT instance_name FROM v$instance;
|
|
|
|
PROMPT
|
|
PROMPT Service Names:
|
|
SELECT name, value FROM v$parameter WHERE name = 'service_names';
|
|
|
|
PROMPT
|
|
PROMPT Servicii active:
|
|
SELECT name as service_name, network_name FROM v$services;
|
|
|
|
PROMPT
|
|
PROMPT Listener configuration:
|
|
SELECT name, value FROM v$parameter WHERE name = 'local_listener';
|
|
|
|
PROMPT
|
|
PROMPT Network config files (listener.ora, tnsnames.ora):
|
|
PROMPT Locatie: ORACLE_HOME\network\admin\
|
|
PROMPT - listener.ora
|
|
PROMPT - tnsnames.ora
|
|
PROMPT - sqlnet.ora
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 3. TIP BAZA DE DATE (CDB sau NON-CDB)
|
|
PROMPT ========================================
|
|
COLUMN cdb FORMAT A5
|
|
COLUMN con_id FORMAT 999
|
|
COLUMN con_name FORMAT A20
|
|
COLUMN open_mode FORMAT A15
|
|
|
|
SELECT cdb FROM v$database;
|
|
|
|
SELECT con_id, name as con_name, open_mode FROM v$containers ORDER BY con_id;
|
|
|
|
-- Daca e CDB, afiseaza si PDB-urile cu locatiile lor
|
|
PROMPT
|
|
PROMPT PDB Datafiles (daca exista):
|
|
SELECT p.name as pdb_name, d.file_name
|
|
FROM v$pdbs p, cdb_data_files d
|
|
WHERE p.con_id = d.con_id
|
|
ORDER BY p.name, d.file_id;
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 4. CONTROL FILES
|
|
PROMPT ========================================
|
|
SELECT name as file_name FROM v$controlfile;
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 5. DATAFILES (fisiere de date)
|
|
PROMPT ========================================
|
|
SELECT file_name FROM dba_data_files ORDER BY tablespace_name, file_id;
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 6. TEMPFILES
|
|
PROMPT ========================================
|
|
SELECT file_name FROM dba_temp_files ORDER BY tablespace_name;
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 7. REDO LOG FILES
|
|
PROMPT ========================================
|
|
SELECT member as file_name FROM v$logfile ORDER BY group#;
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 8. SPFILE / PFILE locatie
|
|
PROMPT ========================================
|
|
SELECT name, value FROM v$parameter WHERE name = 'spfile';
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 9. ARCHIVE LOG DESTINATION (daca e activat)
|
|
PROMPT ========================================
|
|
SELECT name, value FROM v$parameter
|
|
WHERE name LIKE 'log_archive_dest%' AND value IS NOT NULL;
|
|
|
|
SELECT name, value FROM v$parameter WHERE name = 'log_archive_format';
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 10. FAST RECOVERY AREA (FRA)
|
|
PROMPT ========================================
|
|
SELECT name, value FROM v$parameter
|
|
WHERE name IN ('db_recovery_file_dest', 'db_recovery_file_dest_size');
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 11. AUDIT si DIAGNOSTIC DEST
|
|
PROMPT ========================================
|
|
SELECT name, value FROM v$parameter
|
|
WHERE name IN ('audit_file_dest', 'diagnostic_dest', 'core_dump_dest');
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 12. PASSWORD FILE locatie
|
|
PROMPT ========================================
|
|
-- Password file este in ORACLE_HOME\database\PWDsid.ora
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT 13. WALLET LOCATION (daca exista)
|
|
PROMPT ========================================
|
|
SELECT * FROM v$encryption_wallet;
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT REZUMAT DIRECTOARE DE COPIAT:
|
|
PROMPT ========================================
|
|
PROMPT 1. ORACLE_HOME (intreaga instalare Oracle)
|
|
PROMPT 2. Toate directoarele unde sunt datafiles
|
|
PROMPT 3. Directorul FRA (Fast Recovery Area)
|
|
PROMPT 4. Directorul diagnostic_dest (alert logs)
|
|
PROMPT
|
|
PROMPT Inainte de copiere, opreste baza de date:
|
|
PROMPT SHUTDOWN IMMEDIATE;
|
|
PROMPT
|
|
|
|
SPOOL OFF
|
|
|
|
PROMPT
|
|
PROMPT ========================================
|
|
PROMPT Rezultatul a fost salvat in: C:\oracle_locations.txt
|
|
PROMPT ========================================
|