# ROA Oracle Database Setup for Windows

## Overview

Scripts and documentation for setting up ROA Oracle Database on Windows servers:

| Scenario | Description |
|----------|-------------|
| **New Server** | Oracle 21c SE (non-CDB) or XE (CDB/PDB) + CONTAFIN_ORACLE.dmp |
| **Migration** | Import existing DMP files (CONTAFIN_ORACLE + 30-50 companies) |
| **Add Company** | Add new company schema to existing server |

---

## Prerequisites

- Windows Server 2016+ or Windows 10/11
- Oracle 21c Standard Edition or Express Edition installed
- Administrator privileges
- PowerShell 5.1+
- Minimum 8GB RAM (16GB recommended for SE)
- 50GB free disk space

---

## Quick Start

### Oracle 21c Express Edition (XE) - Gratuit
1. Download și instalează Oracle 21c XE (see `docs/00-INSTALL-ORACLE-XE.md`)
2. Copiază fișierele DMP în `C:\DMPDIR\`
3. Rulează instalarea:
   ```cmd
   RunAll.cmd
   ```

### Oracle 21c Standard Edition (SE) - Licență
1. Download și instalează Oracle 21c SE (see `docs/00-INSTALL-ORACLE-SE.md`)
   - În wizard: "Create and configure a single instance database"
   - Global database name: `ROA`
   - **Debifează** "Create as Container database"
2. Copiază fișierele DMP în `C:\DMPDIR\`
3. Rulează instalarea:
   ```cmd
   RunAll.cmd
   ```

### Rulare script individual (dacă e necesar)

```cmd
Run.cmd 01-setup-database.ps1
Run.cmd 07-verify-installation.ps1
```

---

## Configuration

Copy `config.example.ps1` to `config.ps1` and edit:

| Parameter | XE Value | SE Value |
|-----------|----------|----------|
| `ORACLE_HOME` | `C:\app\oracle\product\21c\dbhomeXE` | `C:\app\oracle\product\21c\dbhome_1` |
| `SERVICE_NAME` | `XEPDB1` | `ROA` |
| `DATAFILE_DIR` | `C:\app\oracle\oradata\XE\XEPDB1` | `C:\app\oracle\oradata\ROA` |

---

## Directory Structure

```
roa-windows-setup/
├── README.md                    # This file
├── config.example.ps1           # Configuration template
├── Run.cmd                      # Script runner (handles ExecutionPolicy)
├── RunAll.cmd                   # Complete installation runner
│
├── docs/                        # Installation guides
│   ├── 00-INSTALL-ORACLE-XE.md  # Oracle 21c XE installation
│   └── 00-INSTALL-ORACLE-SE.md  # Oracle 21c SE installation
│
├── scripts/                     # PowerShell setup scripts
│   ├── 01-setup-database.ps1    # Tablespace, profile, CONTAFIN_ORACLE user
│   ├── 02-create-sys-objects.ps1# SYS objects (AUTH_PACK, NEWSCHEMA, etc.)
│   ├── 03-import-contafin.ps1   # Import CONTAFIN_ORACLE schema
│   ├── 04-create-synonyms-grants.ps1  # Public synonyms and grants
│   ├── 05-import-companies.ps1  # Batch import company schemas
│   ├── 06-add-company.ps1       # Add new company to existing server
│   ├── 07-verify-installation.ps1# Verify installation completeness
│   ├── 08-post-install-config.ps1# ROAUPDATE folders, SERVER_INFO, jobs
│   └── 99-uninstall-roa.ps1     # Uninstall/cleanup script
│
├── config/                      # Configuration templates
│   └── sqlnet.ora               # Compatibility for Instant Client 10/11
│
├── par/                         # Data Pump parameter files
│   ├── import-contafin.par      # CONTAFIN_ORACLE import parameters
│   └── import-company.par       # Company schema import template
│
├── sql/                         # SQL scripts
│   ├── create-tablespace.sql    # ROA tablespace creation
│   ├── create-user-contafin.sql # CONTAFIN_ORACLE user creation
│   ├── create-user-company.sql  # Company user template
│   ├── configure-profile.sql    # Profile settings
│   ├── sys-objects.sql          # SYS custom objects (AUTH_PACK, etc.)
│   ├── synonyms-public.sql      # Public synonyms for CONTAFIN_ORACLE
│   ├── grants-public.sql        # Public grants
│   ├── directories-roaupdate.sql # 54 DIRECTORY objects for PACK_UPDATE
│   ├── server-info-init.sql     # SERVER_INFO configuration
│   ├── scheduler-jobs.sql       # UPDATEROA_ZILNIC, UPDATERTVAI_ZILNIC
│   ├── auth-detalii-init.sql    # Customer ID for licensing
│   ├── verify-objects.sql       # Verification queries
│   └── uninstall-roa.sql        # Cleanup script (removes all ROA objects)
│
└── test/                        # Testing scripts
    └── clone-vm300.sh           # Proxmox VM cloning for testing
```

---

## Scripts Overview

| Script | Purpose | Required |
|--------|---------|----------|
| `01-setup-database.ps1` | Create tablespace ROA, configure profile, create CONTAFIN_ORACLE user | Yes |
| `02-create-sys-objects.ps1` | Install SYS objects: AUTH_PACK, NEWSCHEMA, EXECUTESCRIPTOS, UTL_MAIL | Yes |
| `03-import-contafin.ps1` | Import CONTAFIN_ORACLE schema from DMP | Yes |
| `04-create-synonyms-grants.ps1` | Create public synonyms and grants for CONTAFIN_ORACLE | Yes |
| `05-import-companies.ps1` | Batch import company schemas from DMP files | Yes |
| `06-add-company.ps1` | Add new company to existing server | Optional |
| `07-verify-installation.ps1` | Verify installation completeness (objects, grants, synonyms) | Yes |
| `08-post-install-config.ps1` | ROAUPDATE directories, SERVER_INFO, scheduler jobs, licensing | Yes |
| `99-uninstall-roa.ps1` | Remove all ROA objects (cleanup for re-testing) | Optional |

---

## Architecture Differences

### Oracle 21c Standard Edition (non-CDB) - RECOMANDAT pentru producție

Traditional single-database architecture (legacy compatibility).

| Feature | Value |
|---------|-------|
| Architecture | Single database (non-CDB) |
| Database Name | ROA |
| Service Name | ROA |
| SID | ROA |
| Connection | `system/pass@ROA` |
| Datafiles | `C:\app\oracle\oradata\ROA\` |

**La instalare:**
- Selectezi "Create and configure a single instance database"
- **Debifezi** "Create as Container database"
- Global database name: `ROA`
- Wizard-ul creează baza de date automat (~30-45 min)

**Avantaje:**
- Fără limitări de resurse (RAM, CPU, spațiu)
- Administrare simplă
- Compatibil cu scripturi de migrare Oracle 10g
- Fără overhead PDB

### Oracle 21c Express Edition (CDB/PDB) - Gratuit, pentru testare

Modern multitenant architecture with Container Database.

| Feature | Value |
|---------|-------|
| Architecture | Container + Pluggable Database |
| CDB Name | XE |
| PDB Name | XEPDB1 |
| Service Name | XEPDB1 (for applications) |
| Connection | `system/pass@XEPDB1` |
| Datafiles | `C:\app\oracle\oradata\XE\XEPDB1\` |

**La instalare:**
- Rulezi installer-ul XE
- Baza de date se creează automat (CDB: XE + PDB: XEPDB1)
- Nu ai opțiunea non-CDB (XE suportă doar CDB)

**Avantaje:**
- Licență gratuită
- Instalare rapidă
- Ideal pentru dezvoltare/testare

**Limitări XE:**
- Max 2 CPU threads
- Max 2 GB RAM
- Max 12 GB date utilizator

**Connection Warning:**
> Conectează-te întotdeauna la **XEPDB1** (PDB), NU la **XE** (CDB root) pentru operații ROA!

---

## Standard Passwords

| User | Password | Notes |
|------|----------|-------|
| SYS | romfastsoft | SYSDBA |
| SYSTEM | romfastsoft | DBA |
| CONTAFIN_ORACLE | ROMFASTSOFT | Common schema |
| Company schemas | From NOM_FIRME.parola | See company-specific config |

> **Security Note:** Change passwords in production environments!

---

## DMP Files Required

### For New Installation

| File | Description | Size |
|------|-------------|------|
| `contafin_oracle.dmp` | Common schema (rights, updates, etc.) | ~50MB |
| `[company].dmp` | Company schema (one per company) | ~100-500MB each |

### Obtaining DMP Files

Export from existing Oracle 18c server (for compatibility):

```bash
# From LXC 108 (Oracle 18c - port 1522)
docker exec oracle18-xe expdp system/romfastsoft@localhost:1521/XEPDB1 \
  SCHEMAS=CONTAFIN_ORACLE DIRECTORY=DMPDIR DUMPFILE=contafin_oracle.dmp \
  LOGFILE=export_contafin.log VERSION=11.2
```

Copy to Windows:
```powershell
# Using SCP from WSL/Linux
scp root@10.0.20.201:/opt/oracle18/oradata/dmpdir/contafin_oracle.dmp C:\DMPDIR\
```

---

## Workflow Examples

### New Server Installation

```powershell
# 1. Install Oracle 21c XE (see docs/00-INSTALL-ORACLE-XE.md)

# 2. Configure
Copy-Item config.example.ps1 config.ps1
notepad config.ps1  # Edit values

# 3. Copy DMP files
mkdir C:\DMPDIR
Copy-Item \\server\dmp\*.dmp C:\DMPDIR\

# 4. Run setup scripts
.\01-setup-database.ps1
.\02-create-sys-objects.ps1
.\03-import-contafin.ps1
.\04-create-synonyms-grants.ps1
.\05-import-companies.ps1
.\08-post-install-config.ps1
.\07-verify-installation.ps1
```

### Post-Installation Configuration (08-post-install-config.ps1)

This script completes the ROA setup by configuring:

1. **ROAUPDATE Directories**: Creates 54 physical folders and Oracle DIRECTORY objects
2. **SERVER_INFO**: Encoded passwords, paths, email settings for PACK_UPDATE
3. **Customer ID**: Sets AUTH_DETALII for license verification
4. **Scheduler Jobs**: UPDATEROA_ZILNIC and UPDATERTVAI_ZILNIC (disabled by default)

```powershell
# Basic usage (defaults: D:\ROAUPDATE, no Customer ID)
.\08-post-install-config.ps1

# With custom ROAUPDATE path and Customer ID
.\08-post-install-config.ps1 -RoaUpdatePath "E:\ROAUPDATE" -CustomerId "138"

# With email configuration
.\08-post-install-config.ps1 -EmailTo "admin@company.ro" -EmailSmtp "mail.company.ro"

# Skip specific parts
.\08-post-install-config.ps1 -SkipSchedulerJobs -SkipServerInfo
```

**Enable automatic updates after verification:**
```sql
-- Enable daily update job
EXEC DBMS_SCHEDULER.ENABLE('CONTAFIN_ORACLE.UPDATEROA_ZILNIC');

-- Run update manually for testing
EXEC DBMS_SCHEDULER.RUN_JOB('CONTAFIN_ORACLE.UPDATEROA_ZILNIC');
```

### Add New Company

```powershell
# Copy company DMP
Copy-Item \\server\dmp\NEWCOMPANY.dmp C:\DMPDIR\

# Run add company script
.\06-add-company.ps1 -CompanyName "NEWCOMPANY" -Password "ROMFASTSOFT"
```

### Migration from Oracle 10g

```powershell
# 1. Export from Oracle 10g (on source Windows server)
exp system/password@ORCL file=C:\backup\COMPANY.dmp owner=COMPANY

# 2. Convert with Oracle 21c imp (if needed)
# Or use impdp with VERSION parameter

# 3. Import using scripts
.\05-import-companies.ps1 -DmpFile "COMPANY.dmp"
```

---

## Uninstall / Cleanup

To remove all ROA objects and start fresh (useful for testing):

```powershell
# Interactive (with confirmation)
.\scripts\99-uninstall-roa.ps1 -SystemPassword "romfastsoft"

# Force (no confirmation)
.\scripts\99-uninstall-roa.ps1 -SystemPassword "romfastsoft" -Force
```

Or run the SQL script directly:

```sql
sqlplus sys/romfastsoft@localhost:1521/XEPDB1 as sysdba @sql/uninstall-roa.sql
```

**This removes:**
- All company user schemas (using ROA tablespace)
- CONTAFIN_ORACLE user and all objects
- Public synonyms pointing to CONTAFIN_ORACLE
- SYS custom objects (AUTH_PACK, AUTH_SERII, INFO, etc.)
- Application context SESIUNE
- Tablespace ROA (including datafile)

> **Warning:** This permanently deletes all ROA data! Use only for testing or before reinstallation.

---

## Troubleshooting

### ORA-28040: No matching authentication protocol

**Problem:** Old clients (Instant Client 10/11) cannot connect to Oracle 21c.

**Solution:** Configure `sqlnet.ora` for backward compatibility:

```powershell
# Copy sqlnet.ora template
Copy-Item config\sqlnet.ora $env:ORACLE_HOME\network\admin\sqlnet.ora

# Restart listener
lsnrctl reload
```

### ORA-01017: invalid username/password

**After configuring sqlnet.ora for old clients, reset passwords:**

```sql
ALTER USER CONTAFIN_ORACLE IDENTIFIED BY ROMFASTSOFT;
ALTER USER [COMPANY] IDENTIFIED BY [PASSWORD];
```

### ORA-12514: TNS:listener does not currently know of service

**Check listener status:**

```powershell
lsnrctl status
lsnrctl services
```

**For XE, ensure PDB is open:**

```sql
ALTER SESSION SET CONTAINER = XEPDB1;
ALTER PLUGGABLE DATABASE XEPDB1 OPEN;
ALTER PLUGGABLE DATABASE XEPDB1 SAVE STATE;
```

### Import Errors

**ORA-00959: tablespace 'ROA' does not exist:**
```powershell
# Use REMAP_TABLESPACE in impdp
impdp ... REMAP_TABLESPACE=ROA:USERS
```

**ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version newer:**

Use DMP files exported from Oracle 18c (TSTZ 31) instead of Oracle 21c (TSTZ 35).

### Service Not Starting

Check Windows Services:
- `OracleServiceXE` (for XE)
- `OracleServiceROA` (for SE)
- `OracleOraDB21Home1TNSListener`

```powershell
# Restart services
net stop OracleServiceXE
net start OracleServiceXE
net stop OracleOraDB21Home1TNSListener
net start OracleOraDB21Home1TNSListener
```

---

## Connection Strings

### SQL*Plus

```bash
# Oracle XE (connect to PDB)
sqlplus system/romfastsoft@localhost:1521/XEPDB1
sqlplus CONTAFIN_ORACLE/ROMFASTSOFT@localhost:1521/XEPDB1

# Oracle SE (connect to database)
sqlplus system/romfastsoft@localhost:1521/ROA
sqlplus CONTAFIN_ORACLE/ROMFASTSOFT@localhost:1521/ROA
```

### JDBC

```
# Oracle XE
jdbc:oracle:thin:@localhost:1521/XEPDB1

# Oracle SE
jdbc:oracle:thin:@localhost:1521/ROA
```

### ODBC (Instant Client)

| Parameter | XE Value | SE Value |
|-----------|----------|----------|
| Host | localhost | localhost |
| Port | 1521 | 1521 |
| Service Name | XEPDB1 | ROA |
| User | CONTAFIN_ORACLE | CONTAFIN_ORACLE |
| Password | ROMFASTSOFT | ROMFASTSOFT |

### TNS Entry

```
# For Oracle XE
ROA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = XEPDB1))
  )

# For Oracle SE
ROA =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA = (SERVICE_NAME = ROA))
  )
```

---

## Testing with Proxmox VM Clone

For testing installations, use the VM cloning script:

```bash
# On Proxmox host (pvemini)
./test/clone-vm300.sh 301   # Creates VM 301 from template 300
```

See `test/clone-vm300.sh` for details.

---

## Related Documentation

- **Oracle LXC 108 (Docker):** `../README.md`
- **Migration Oracle 10g -> 21c:** `../migration/README.md`
- **SQL Migration Guidelines:** `/system_instructions/system_prompt.md`
- **Proxmox Infrastructure:** `../../README.md`

---

**Last Updated:** 2026-01-28
**Author:** Marius Mutu
**Project:** ROMFASTSQL - ROA Oracle Database Windows Setup