Friday, October 6, 2023

Install Oracle 23c free using docker in Amazon Linux 2023

 

Install docker package and enable it

[ec2-user@ip-10-1-1-152 ~]$ cat /etc/amazon-linux-release
Amazon Linux release 2023 (Amazon Linux)

sudo yum install docker -y
sudo systemctl enable docker
sudo systemctl start docker

Pull Oracle 23c free version docker image

sudo docker pull container-registry.oracle.com/database/free:latest

[ec2-user@ip-10-1-1-152 ~]$ sudo docker pull container-registry.oracle.com/database/free:latest
latest: Pulling from database/free
089fdfcd47b7: Pull complete
43c899d88edc: Pull complete
47aa6f1886a1: Pull complete
f8d07bb55995: Pull complete
c31c8c658c1e: Pull complete
b7d28faa08b4: Pull complete
1d0d5c628f6f: Pull complete
db82a695dad3: Pull complete
25a185515793: Pull complete
Digest: sha256:5ac0efa9896962f6e0e91c54e23c03ae8f140cf6ed43ca09ef4354268a942882
Status: Downloaded newer image for container-registry.oracle.com/database/free:latest
container-registry.oracle.com/database/free:latest

Check the downloaded docker images

sudo docker images

[ec2-user@ip-10-1-1-152 ~]$ sudo docker images
REPOSITORY                                    TAG       IMAGE ID       CREATED       SIZE
container-registry.oracle.com/database/free   latest    39cabc8e6db0   4 weeks ago   9.16GB

Run the docker images

sudo docker run -d -it --name 23cfree -p 1521:1521 -p 5500:5500 -p 8080:8080 -p 8443:8443 -e ORACLE_PWD=Welcome123 container-registry.oracle.com/database/free:latest

[ec2-user@ip-10-1-1-152 ~]$ sudo docker run -d -it --name 23cfree -p 1521:1521 -p 5500:5500 -p 8080:8080 -p 8443:8443 -e ORACLE_PWD=Welcome123 container-registry.oracle.com/database/free:latest
ff618602d4402b6dbf92fd2c69612027c885937a176b9b160a8a9e93018bd519

[ec2-user@ip-10-1-1-152 ~]$ sudo docker logs 23cfree
Starting Oracle Net Listener.
Oracle Net Listener started.
Starting Oracle Database instance FREE.
Oracle Database instance FREE started.

The Oracle base remains unchanged with value /opt/oracle

SQL*Plus: Release 23.0.0.0.0 - Production on Fri Oct 6 12:31:14 2023
Version 23.3.0.23.09

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>
User altered.

SQL>
User altered.

SQL>
Session altered.

SQL>
User altered.

SQL> Disconnected from Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09
The Oracle base remains unchanged with value /opt/oracle
#########################
DATABASE IS READY TO USE!
#########################
The following output is now a tail of the alert.log:
Dumping current patch information
===========================================================
No patches have been applied
===========================================================
2023-10-06T12:31:12.630086+00:00
FREEPDB1(3):Opening pdb with Resource Manager plan: DEFAULT_PLAN
Completed: Pluggable database FREEPDB1 opened read write
Completed: ALTER DATABASE OPEN
2023-10-06T12:31:15.404495+00:00
FREEPDB1(3):TABLE AUDSYS.AUD$UNIFIED: ADDED INTERVAL PARTITION SYS_P342 (3385) VALUES LESS THAN (TIMESTAMP' 2023-10-07 00:00:00')
[ec2-user@ip-10-1-1-152 ~]$

Login to running docker

sudo docker  exec -it 23cfree /bin/bash

[ec2-user@ip-10-1-1-152 ~]$ sudo docker  exec -it 23cfree /bin/bash
bash-4.4$ adrci

ADRCI: Release 23.0.0.0.0 - Production on Fri Oct 6 12:45:05 2023

Copyright (c) 1982, 2023, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/opt/oracle"
adrci> set home FREE
adrci> show alert -tail -f

Login to the database remotely

  • Password is the one specified during during "docker run" command.
  • Server name:
    • FREE: CDB Name (Container)
    • FREEPDB1: PDB Name
  • Users created:
    • sys (login as sysdba)
    • system
    • pdbadmin
sql system/Welcome123@3.216.132.117:1521/FREEPDB1

[oracle@ol ~]$ sql system/Welcome123@3.216.132.117:1521/FREEPDB1


SQLcl: Release 22.4 Production on Fri Oct 06 20:41:31 2023

Copyright (c) 1982, 2023, Oracle.  All rights reserved.


	New version: 23.2.0 available to download

Last Successful login time: Fri Oct 06 2023 20:43:09 +08:00

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09


SQL> select host_name from v$instance;

HOST_NAME
_______________
ff618602d440

SQL> select banner from v$version;

BANNER
_________________________________________________________________________________
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Subsequent stop/start Docker

sudo docker stop 23cfree
sudo docker start 23cfree

Currently docker requires sudo to run it, to run as default ec2-user, you can add it to docker group.

sudo usermod -a -G docker ec2-user

ec2-user@ip-10-1-1-152 ~]$ sudo usermod -a -G docker ec2-user

[ec2-user@ip-10-1-1-152 ~]$ id -a ec2-user
uid=1000(ec2-user) gid=1000(ec2-user) groups=1000(ec2-user),4(adm),10(wheel),190(systemd-journal),992(docker)

[ec2-user@ip-10-1-1-152 ~]$ exit
logout

[root@ip-10-1-1-152 ~]# su - ec2-user
Last login: Fri Oct  6 12:24:26 UTC 2023 on pts/0

[ec2-user@ip-10-1-1-152 ~]$ docker ps
CONTAINER ID   IMAGE                                                COMMAND                  CREATED          STATUS                PORTS                                                                      NAMES
ff618602d440   container-registry.oracle.com/database/free:latest   "/bin/bash -c $ORACL…"   32 minutes ago   Up 2 minutes (healthy)   0.0.0.0:1521->1521/tcp, :::1521->1521/tcp, 0.0.0.0:5500->5500/tcp, :::5500->5500/tcp, 0.0.0.0:8080->8080/tcp, :::8080->8080/tcp, 0.0.0.0:8443->8443/tcp, :::8443->8443/tcp   23cfree
Posted by Luo Donghua at 9:06 PM 0 comments
Labels:

Friday, September 1, 2023

Impacts on MS-Replication or MS-CDC to enable CDC solution

 

ConfigurationDML DurationDatafile (Allocated MB)Datafile (Free MB)Tlog (Allocated MB)Tlog Size (Free MB)
No Replication00:07:1984.4392146.4
MS-Replication00:07:2283.2392214
MS-CDC00:07:1113617.845624.3
MS-Replication + MS-CDC00:09:2713622.7456190

Instance Configuration

/*
To avoid error: Update mask evaluation will be disabled in net_changes_function because the CLR configuration option is disabled.
*/
EXEC sp_configure 'clr enabled';  
EXEC sp_configure 'clr enabled' , '1';  
RECONFIGURE;

Database Creation

CREATE DATABASE [db_msrepll]
 ON  PRIMARY ( NAME = N'db_msrepl', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll.mdf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB )
 LOG ON ( NAME = N'db_msrepll_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\db_msrepll_log.ldf' , 
SIZE = 8192KB , FILEGROWTH = 65536KB );
-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Table Creation

create table [db_msrepll].dbo.T (i int identity primary key, c text);
create table [db_mscdc].dbo.T (i int identity primary key, c text);
create table [db_msrepl_cdc].dbo.T (i int identity primary key, c text);
create table [db_norep].dbo.T (i int identity primary key, c text);

Enable CDC requirement with MS-Replication

For replication tool, it only requires additional information in the TLog, thus there is a filter 1=2, so no extra data writing to DistributionDB.

use [db_msrepl]
exec sp_replicationdboption @dbname = N'db_msrepl', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [db_msrepl]
exec sp_addpublication @publication = N'msrep', @description = N'Transactional publication of database ''db_msrepl'' from Publisher ''EC2AMAZ-PPQ3EA9''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO

exec sp_addpublication_snapshot @publication = N'msrep', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1

use [db_msrepl]
exec sp_addarticle @publication = N'msrep', @article = N'T', @source_owner = N'dbo', @source_object = N'T', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'T', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboT', @del_cmd = N'CALL sp_MSdel_dboT', @upd_cmd = N'SCALL sp_MSupd_dboT', @filter_clause = N'1=2'

-- Adding the article filter
exec sp_articlefilter @publication = N'msrep', @article = N'T', @filter_name = N'FLTR_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1

-- Adding the article synchronization object
exec sp_articleview @publication = N'msrep', @article = N'T', @view_name = N'SYNC_T_1__51', @filter_clause = N'1=2', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO

-- repeat this for [db_msrep_cdc]

Enable CDC requirement with MS-CDC

In this case, MS-CDC automatically populates CDC related table (such as dbo_T_CT) along the DML changes, that explains why data file size increases.

USE [db_mscdc]  
GO  
EXEC sys.sp_cdc_enable_db  
GO  

EXEC sys.sp_cdc_enable_table  
@source_schema = N'dbo',  
@source_name   = N'T',  
@role_name     = NULL,  
@supports_net_changes = 1  
GO  
-- repeat this for [db_msrep_cdc]

Simulate DML workloads

use [db_msrepl];

DECLARE
	@Counter int= 1
WHILE @Counter< = 100000
BEGIN
	insert into T (c) values (replicate('x',100));
	update T set c=replicate('y',100);
	delete from T;
	SET @Counter= @Counter + 1
END;

-- repeat this for database db_mscdc, db_msrepl_cdc and db_norep

Check the data file and Tlog size

SELECT DB_NAME() AS DbName, 
    name AS FileName, 
    type_desc,
    size/128 AS CurrentSizeMB,  
    size/128 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0 AS FreeSpaceMB
FROM sys.database_files
WHERE type IN (0,1);
Posted by Luo Donghua at 4:33 PM 0 comments
Labels:

Saturday, August 12, 2023

PostgreSQL JDBC prepareThreshold Behaviour with Database setting plan_cache_mode

Source code: https://raw.githubusercontent.com/luodonghua/PostgreSQL/main/Development/Java/JdbcPreparedPlanCacheModeDemo.java


Summary

Observations:

  1. During testing, the first time execution with id=1000 is much more expensive comparing to subsequent execution (483 ms vs 0.7 ms), although same plan used.
  2. In the situation with "prepared statements", the value for prepareThreshold=n and plan_cache_mode=auto, after n+5-1 executions, generic plan could be used if " its cost is not so much higher than the average custom-plan cost". (Ref: https://www.postgresql.org/docs/current/sql-prepare.html)
  3. plan_cache_mode only affects prepared statements, has no effect on literal queries.
  4. To ensure optimizer always pick the best plan, plan_cache_mode=force_custom_plan produces consistent plans.

Prepare Statements:

PreparedStatement ps = conn.prepareStatement("SELECT * FROM t WHERE id = ?");

ps.setInt(1, 1);
ps.executeQuery().close();
ps.setInt(1, 2);
ps.executeQuery().close();
ps.setInt(1, 3);
ps.executeQuery().close();
ps.setInt(1, 4);
ps.executeQuery().close();
ps.setInt(1, 5);
ps.executeQuery().close();
ps.setInt(1, 6);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();
ps.setInt(1, 7);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();

Result

Config123456100071000
1.PrepareThreshold5CacheModeAutoBISISISISISISSEQISSEQ
2.PrepareThreshold5CacheModeForceCustomISISISISISISSEQISSEQ
3.PrepareThreshold5CacheModeForceGenericISISISISISISISISIS
4.PrepareThreshold10CacheModeAutoISISISISISISSEQISSEQ
5.PrepareThreshold10CacheModeForceCustomISISISISISISSEQISSEQ
6.PrepareThreshold10CacheModeForceGenericISISISISISISISISIS
7.PrepareThreshold1CacheModeAutoISISISISISISISISIS
8.PrepareThreshold1CacheModeForceCustomISISISISISISSEQISSEQ
9.PrepareThreshold1CacheModeForceGenericISISISISISISISISIS
  • BIS: Bitmap Index Scan
  • IS: Index Scan
  • SEQ: Seq Scan

Prepare Statements Starts with 1000

PreparedStatement ps = conn.prepareStatement("SELECT * FROM t WHERE id = ?");

ps.setInt(1, 1000);
ps.executeQuery().close();
ps.setInt(1, 2);
ps.executeQuery().close();
ps.setInt(1, 3);
ps.executeQuery().close();
ps.setInt(1, 4);
ps.executeQuery().close();
ps.setInt(1, 5);
ps.executeQuery().close();
ps.setInt(1, 6);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();
ps.setInt(1, 7);
ps.executeQuery().close();
ps.setInt(1, 1000);
ps.executeQuery().close();

Result

Config100023456100071000
10.PrepareThreshold5CacheModeAutoSEQISISISISISSEQISSEQ
11.PrepareThreshold5CacheModeForceCustomSEQISISISISISSEQISSEQ
12.PrepareThreshold5CacheModeForceGenericISISISISISISISISIS
13.PrepareThreshold10CacheModeAutoSEQISISISISISSEQISSEQ
14.PrepareThreshold10CacheModeForceCustomSEQISISISISISSEQISSEQ
15.PrepareThreshold10CacheModeForceGenericISISISISISISISISIS
16.PrepareThreshold1CacheModeAutoSEQISISISISISISISIS
17.PrepareThreshold1CacheModeForceCustomSEQISISISISISSEQISSEQ
18.PrepareThreshold1CacheModeForceGenericISISISISISISISISIS
  • BIS: Bitmap Index Scan
  • IS: Index Scan
  • SEQ: Seq Scan

Statements:

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM t WHERE id = 1");  
stmt.execute("SELECT * FROM t WHERE id = 2");  
stmt.execute("SELECT * FROM t WHERE id = 3");
stmt.execute("SELECT * FROM t WHERE id = 4");
stmt.execute("SELECT * FROM t WHERE id = 5");
stmt.execute("SELECT * FROM t WHERE id = 6");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.execute("SELECT * FROM t WHERE id = 7");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.close();
Config123456100071000
19.PrepareThreshold5CacheModeAutoISISISISISISSEQISSEQ
20.PrepareThreshold5CacheModeForceCustomISISISISISISSEQISSEQ
21.PrepareThreshold5CacheModeForceGenericISISISISISISSEQISSEQ
22.PrepareThreshold10CacheModeAutoISISISISISISSEQISSEQ
23.PrepareThreshold10CacheModeForceCustomISISISISISISSEQISSEQ
24.PrepareThreshold10CacheModeForceGenericISISISISISISSEQISSEQ
25.PrepareThreshold1CacheModeAutoISISISISISISSEQISSEQ
26.PrepareThreshold1CacheModeForceCustomISISISISISISSEQISSEQ
27.PrepareThreshold1CacheModeForceGenericISISISISISISSEQISSEQ
  • BIS: Bitmap Index Scan
  • IS: Index Scan
  • SEQ: Seq Scan

Statements:

Statement stmt = conn.createStatement();
stmt.execute("SELECT * FROM t WHERE id = 1000");  
stmt.execute("SELECT * FROM t WHERE id = 2");  
stmt.execute("SELECT * FROM t WHERE id = 3");
stmt.execute("SELECT * FROM t WHERE id = 4");
stmt.execute("SELECT * FROM t WHERE id = 5");
stmt.execute("SELECT * FROM t WHERE id = 6");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.execute("SELECT * FROM t WHERE id = 7");
stmt.execute("SELECT * FROM t WHERE id = 1000");
stmt.close();
Config100023456100071000
28.PrepareThreshold5CacheModeAutoSEQISISISISISSEQISSEQ
29.PrepareThreshold5CacheModeForceCustomSEQISISISISISSEQISSEQ
30.PrepareThreshold5CacheModeForceGenericSEQISISISISISSEQISSEQ
31.PrepareThreshold10CacheModeAutoSEQISISISISISSEQISSEQ
32.PrepareThreshold10CacheModeForceCustomSEQISISISISISSEQISSEQ
33.PrepareThreshold10CacheModeForceGenericSEQISISISISISSEQISSEQ
34.PrepareThreshold1CacheModeAutoSEQISISISISISSEQISSEQ
35.PrepareThreshold1CacheModeForceCustomSEQISISISISISSEQISSEQ
36.PrepareThreshold1CacheModeForceGenericSEQISISISISISSEQISSEQ
  • BIS: Bitmap Index Scan
  • IS: Index Scan
  • SEQ: Seq Scan




Posted by Luo Donghua at 3:34 PM 0 comments
Labels:
Subscribe to: Posts (Atom)