This document provides step-by-step instructions for configuring Oracle Database Free 23ai for Snowflake OpenFlow connector using XStream technology.
The snowflake_oracle_setup.sql script configures Oracle database with:
- GoldenGate replication enabled for XStream support
- Supplemental logging for change data capture
- XStream administrator user (c##xstreamadmin) with proper privileges
- XStream connect user (c##connectuser) for Snowflake connector
- XStream outbound server (XOUT1) configured for HR and CO schemas
- Proper container-based user management for CDB/PDB architecture
- Oracle Database Free 23ai running in Docker container
- SYSTEM user access with SYSDBA privileges
- HR and CO schemas populated with sample data
- Database in ARCHIVELOG mode (required for XStream)
# SSH to your EC2 instance
ssh -i <your-key.pem> ec2-user@<InstancePublicIp>
# docker bash
docker exec -it sharvan-kumar-afe-oracle-free bash
sqlplus -L / as sysdba-- Check if database is in ARCHIVELOG mode
SELECT log_mode FROM v$database;
-- If not in ARCHIVELOG mode, enable it (requires database restart)
-- SHUTDOWN IMMEDIATE;
-- STARTUP MOUNT;
-- ALTER DATABASE ARCHIVELOG;
-- ALTER DATABASE OPEN;
-- run now to check the log_mode.
SELECT log_mode FROM v$database;
-- Run the complete setup script
@/path/to/snowflake_oracle_setup.sql
-- Or copy and paste the script content directly-- Check XStream outbound server
SELECT server_name, status, connect_user, capture_user
FROM dba_xstream_outbound;
-- Check XStream capture process
SELECT capture_name, status, queue_name, source_database
FROM dba_capture;
-- Check supplemental logging
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM v$database;
-- Check user accounts
SELECT username, account_status, created, default_tablespace
FROM cdb_users
WHERE username IN ('C##XSTREAMADMIN','C##CONNECTUSER')
ORDER BY username;
- c##xstreamadmin: XStream administrator with full privileges (common user)
- c##connectuser: XStream server connect user (common user)
- XOUT1: XStream outbound server for data streaming
- Source Container: FREEPDB1 (Pluggable Database)
- Schemas: HR and CO schemas configured for capture
- GoldenGate Replication: Enabled for XStream support
- Supplemental Logging: Enabled for all columns
- Container Management: Proper CDB/PDB user management
- Privileges: All necessary privileges granted for XStream operations
- Host:
<InstancePublicIp>or<InstancePrivateIp> - Port:
1521 - Service:
FREEPDB1 - Username:
c##connectuser - Password:
ConnectUser123!
- XStream Server:
XOUT1 - Source Container:
FREEPDB1 - Schemas:
HR,CO - Connect User:
c##connectuser - Capture User:
c##xstreamadmin
-- Check outbound server status
SELECT server_name, status, connect_user, capture_user
FROM dba_xstream_outbound;
-- Check GoldenGate replication setting
SELECT name, value FROM v$parameter WHERE name = 'enable_goldengate_replication';
-- Check supplemental logging
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM v$database;
-- Check user accounts
SELECT username, account_status, created, default_tablespace
FROM cdb_users
WHERE username IN ('C##XSTREAMADMIN','C##CONNECTUSER')
ORDER BY username;-- Check queue statistics
SELECT * FROM dba_queue_tables;-- Check server status
SELECT server_name, status FROM dba_xstream_outbound_servers;
-- Restart server if needed
BEGIN
DBMS_XSTREAM_ADM.ALTER_OUTBOUND_SERVER(
server_name => 'XSTREAM_OUTBOUND_SERVER',
status => 'ENABLED'
);
END;
/-- Check capture status
SELECT capture_name, status FROM dba_capture;
-- Start capture if needed
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE('XSTREAM_CAPTURE');
END;
/-- Verify supplemental logging
SELECT supplemental_log_data_min, supplemental_log_data_pk, supplemental_log_data_all
FROM v$database;
-- Re-enable if needed
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (ALL) COLUMNS;
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;- Change default passwords in production
- Use strong passwords (12+ characters)
- Store passwords securely
- Restrict database access to authorized IPs
- Use SSL/TLS for connections
- Monitor database access logs
- Review granted privileges regularly
- Use least-privilege principle
- Monitor user activity
- Minimal impact on database performance
- Captures only committed transactions
- Efficient change data capture
-- Check XStream performance
SELECT server_name, total_messages, total_bytes,
messages_per_second, bytes_per_second
FROM dba_xstream_outbound_servers;
-- Check capture performance
SELECT capture_name, total_messages_captured,
total_messages_queued, total_messages_dequeued
FROM dba_capture;-- Stop and drop capture process
BEGIN
DBMS_CAPTURE_ADM.STOP_CAPTURE('XSTREAM_CAPTURE');
DBMS_CAPTURE_ADM.DROP_CAPTURE('XSTREAM_CAPTURE');
END;
/
-- Drop outbound server
BEGIN
DBMS_XSTREAM_ADM.DROP_OUTBOUND_SERVER('XSTREAM_OUTBOUND_SERVER');
END;
/
-- Drop users
DROP USER XSTRMADMIN CASCADE;
DROP USER XSTRMUSER CASCADE;
DROP USER SNOWFLAKE_CONNECTOR CASCADE;SQL> @snowflake_oracle_setup.sql
Database altered.
Database altered.
Database altered.
User created.
Grant succeeded.
Grant succeeded.
...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Snowflake Oracle Connector setup completed successfully!
XStream users created: XSTRMADMIN, XSTRMUSER, SNOWFLAKE_CONNECTOR
XStream outbound server: XSTREAM_OUTBOUND_SERVER
XStream capture process: XSTREAM_CAPTURE
Supplemental logging enabled for all columns and primary keys
HR and CO schemas configured for XStream capture
- Configure Snowflake Connector: Use the connection parameters in Snowflake
- Test Data Flow: Verify data is being captured and streamed
- Monitor Performance: Set up monitoring for XStream components
- Scale as Needed: Add more tables or schemas to capture process
The Oracle database is now ready for Snowflake OpenFlow connector integration! 🎉