Wednesday, May 30, 2018

Oracle 12.2 docker install on RHEL 7

#yum install yum-utils \
device-mapper-persistent-data \
lvm2

# yum-config-manager \
--add-repo \
https://download.docker.com/linux/centos/docker-ce.repo

# yum install docker-ce

Error: Package: docker-ce-18.03.1.ce-1.el7.centos.x86_64 (docker-ce-stable)
           Requires: pigz

# yum install http://mirror.centos.org/centos/7/extras/x86_64/Packages/pigz-2.3.3-1.el7.centos.x86_64.rpm

# yum install docker-ce

# yum list docker-ce

# systemctl start docker

# docker run hello-world
Unable to find image 'hello-world:latest' locally
latest: Pulling from library/hello-world
9bb5a5d4561a: Pull complete 
Digest: sha256:f5233545e43561214ca4891fd1157e1c3c563316ed8e237750d59bde73361e77
Status: Downloaded newer image for hello-world:latest

Hello from Docker!
This message shows that your installation appears to be working correctly.

To generate this message, Docker took the following steps:
 1. The Docker client contacted the Docker daemon.
 2. The Docker daemon pulled the "hello-world" image from the Docker Hub.
    (amd64)
 3. The Docker daemon created a new container from that image which runs the
    executable that produces the output you are currently reading.
 4. The Docker daemon streamed that output to the Docker client, which sent it
    to your terminal.

To try something more ambitious, you can run an Ubuntu container with:
 $ docker run -it ubuntu bash

Share images, automate workflows, and more with a free Docker ID:
 https://hub.docker.com/

For more examples and ideas, visit:
 https://docs.docker.com/engine/userguide/


# docker ps
CONTAINER ID        IMAGE               COMMAND             CREATED             STATUS              PORTS               NAMES

# docker version
Client:
 Version:      18.03.1-ce
 API version:  1.37
 Go version:   go1.9.5
 Git commit:   9ee9f40
 Built:        Thu Apr 26 07:20:16 2018
 OS/Arch:      linux/amd64
 Experimental: false
 Orchestrator: swarm

Server:
 Engine:
  Version:      18.03.1-ce
  API version:  1.37 (minimum version 1.12)
  Go version:   go1.9.5
  Git commit:   9ee9f40
  Built:        Thu Apr 26 07:23:58 2018
  OS/Arch:      linux/amd64
  Experimental: false

 
# docker pull container-registry.oracle.com/database/enterprise:12.2.0.1
Error response from daemon: Get https://container-registry.oracle.com/v2/database/enterprise/manifests/12.2.0.1: unauthorized: authentication required

# docker login container-registry.oracle.com
Username: OTN_username
Password:

# docker pull container-registry.oracle.com/database/enterprise:12.2.0.1

# docker run -d -it --name OracleDB -p 32711:1521 container-registry.oracle.com/database/enterprise:12.2.0.1

# docker ps
CONTAINER ID        IMAGE                                                        COMMAND                  CREATED             STATUS                             PORTS                               NAMES
9a13cf004144        container-registry.oracle.com/database/enterprise:12.2.0.1   "/bin/sh -c '/bin/ba…"   19 seconds ago      Up 13 seconds (health: starting)   5500/tcp, 0.0.0.0:32711->1521/tcp   OracleDB

# docker port OracleDB
1521/tcp -> 0.0.0.0:32711

# docker exec -it OracleDB bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

SQL*Plus: Release 12.2.0.1.0 Production on Wed May 30 13:38:16 2018

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

SQL> conn / as sysdba
Connected.
 


Monday, January 15, 2018

Wednesday, November 15, 2017

SQL Server - Check how database recovery will take

DECLARE @DBName VARCHAR(64) = 'DCIS'

DECLARE @ErrorLog AS TABLE([LogDate] CHAR(24), [ProcessInfo] VARCHAR(64), [TEXT] VARCHAR(MAX))

INSERT INTO @ErrorLog
EXEC master..sp_readerrorlog 0, 1, 'Recovery of database', @DBName

SELECT TOP 5
[LogDate]
,SUBSTRING([TEXT], CHARINDEX(') is ', [TEXT]) + 4,CHARINDEX(' complete (', [TEXT]) - CHARINDEX(') is ', [TEXT]) - 4) AS PercentComplete
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0 AS MinutesRemaining
,CAST(SUBSTRING([TEXT], CHARINDEX('approximately', [TEXT]) + 13,CHARINDEX(' seconds remain', [TEXT]) - CHARINDEX('approximately', [TEXT]) - 13) AS FLOAT)/60.0/60.0 AS HoursRemaining
,[TEXT]

FROM @ErrorLog ORDER BY [LogDate] DESC

Thanks to http://timlaqua.com/2009/09/determining-how-long-a-database-will-be-in-recovery-sql-server-2008/

Thursday, October 26, 2017

Oracle - Create restore points in Data Guard environment

*************** On Standby


SQL> select db_unique_name, database_role, open_mode, flashback_on,dataguard_broker, guard_status, protection_mode from v$database;

SQL> alter database recover managed standby database cancel;

SQL> create restore point RP1 guarantee flashback database;

SQL> alter database recover managed standby database using current logfile disconnect;

SQL> select * from v$restore_point;


************ On Primary

SQL> select db_unique_name, database_role, open_mode, flashback_on,dataguard_broker, guard_status, protection_mode from v$database;

SQL> create restore point RP1 guarantee flashback database;

SQL> select * from v$restore_point;

















Tuesday, November 22, 2016

Oracle - Rotate/backup listener.log file

Stop log generation into listener logfile

[host11:oracle:/home/oracle]lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 22-NOV-2016 15:45:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

In different window, change listener log filename

[host111:oracle:/home/oracle]mv listener.log listener_1.log

Return to previous window and start log generation

LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL>

Archive old listener logfile

[host111:oracle:/home/oracle]tar -cvf - listener_1.log | gzip > listener_1.tar.gz

Oracle - Rotate/backup listener.log file

Stop log generation into listener logfile

[host11:oracle:/home/oracle]lsnrctl

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production on 22-NOV-2016 15:45:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Welcome to LSNRCTL, type "help" for information.

LSNRCTL> show log_status
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL> set log_status off
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to OFF
The command completed successfully

In different window, change listener log filename

[host111:oracle:/home/oracle]mv listener.log listener_1.log

Return to previous window and start log generation

LSNRCTL> set log_status on
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host11)(PORT=1521)))
LISTENER parameter "log_status" set to ON
The command completed successfully
LSNRCTL>

Archive old listener logfile

[host111:oracle:/home/oracle]tar -cvf - listener_1.log | gzip > listener_1.tar.gz

Thursday, November 17, 2016

SQL Server - Execute dynamic SQL with parameters

Example of procedure running an dynamic SQL based on procedure's parameter:

CREATE PROCEDURE [dbo].[SP_DYNAMIC_SQL_TEST] 
(@V_PARAMETER INT) 
AS
BEGIN

SET NOCOUNT ON 

DECLARE @delete_sql nvarchar(1000)
DECLARE @ParmDefinitionDel NVARCHAR(500)

SET @delete_sql = 'DELETE TB_DYNAMIC_SQL'

IF (@V_PARAMETER IS NOT NULL) 
SET @delete_sql = @delete_sql + ' WHERE COLUMN_NAME = @V_PARAMETER_DYN'

       SET @ParmDefinitionDel = N'@V_PARAMETER_DYN INT'

       PRINT @delete_sql

       EXEC sp_executesql @delete_sql, @ParmDefinitionDel, @V_PARAMETER_DYN = @V_PARAMETER

END