IT 研究室 ( 前IT DBA's 資訊站)

星期六, 3月 20, 2021

關於Oracle database做災難復原測試的規定

爲了測試備份的物理副本，Oracle 數據庫的許可證包括

有權在未經許可的計算機上運行數據庫最多四次，每次不超過 2 天，在任何給定日曆年進行測試。

For oracle database:

For the purpose of testing physical copies of backups, your license for the Oracle Database includes the

right to run the database on an unlicensed computer for up to four times, not exceeding 2 days per

testing, in any given calendar year

https://www.oracle.com/assets/data-recovery-licensing-070587.pdf

星期一, 5月 25, 2020

如何快速地下載並啟動一個Oracle 12.2 docker image

前言：
在以往安裝Oracle必須要從Oracle官方網站下載，而現在
有了docker社群裡面由原廠或是社群提供的image，就可以透過pull的方式直接下載，這樣測試與開發真的快上非常非常多。

趁著休假時間來玩一下怎麼從docket community pull oracle 12c image

首先請先在docker社群註冊你的帳號

測試步驟：

yum install -y docker

chkconfig docker on

# service docker start
Starting cgconfig service: [ OK ]
Starting docker: . [ OK ]

As ec2-user user :

$ docker login
Login with your Docker ID to push and pull images from Docker Hub. If you don't have a Docker ID, head over to https://hub.docker.com to create one.
Username: jaychu649
Password:
WARNING! Your password will be stored unencrypted in /home/ec2-user/.docker/config.json.
Configure a credential helper to remove this warning. See
https://docs.docker.com/engine/reference/commandline/login/#credentials-store

Login Succeeded

#ref 2
$sudo chmod 666 /var/run/docker.sock
$sudo docker pull store/oracle/database-enterprise:12.2.0.1
12.2.0.1: Pulling from store/oracle/database-enterprise
4ce27fe12c04: Pull complete
9d3556e8e792: Pull complete
fc60a1a28025: Pull complete
0c32e4ed872e: Pull complete
b465d9b6e399: Pull complete
Digest: sha256:40760ac70dba2c4c70d0c542e42e082e8b04d9040d91688d63f728af764a2f5d
Status: Downloaded newer image for store/oracle/database-enterprise:12.2.0.1
docker.io/store/oracle/database-enterprise:12.2.0.1

$ docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
store/oracle/database-enterprise 12.2.0.1 12a359cd0528 2 years ago 3.44GB

以名稱 JayDevDB 來建立容器
$ docker run -d -it --name JayDevDB -p 1521:1521 store/oracle/database-enterprise:12.2.0.1
786f903dca1a50deeff130fa2a9bf4b422a7ec4db3ad370d6b01ffe5a62ba865

$ docker start JayDevDB

$ sudo docker exec -it JayDevDB bash -c "source /home/oracle/.bashrc; sqlplus /nolog"

SQL> connect sys as sysdba;
Enter password: welcome1
Connected.
SQL>

alter session set "_ORACLE_SCRIPT"=true;
create user testuser identified by testuser;
GRANT CONNECT, RESOURCE, DBA TO testuser;

SQL>select value from v$parameter where name='service_names';
VALUE
--------------------------------------------------------------------------------
ORCLCDB.localdomain

SQL> exit

$ sudo docker exec -it JayDevDB bash -c "source /home/oracle/.bashrc; sqlplus testuser/testuser"

SQL> select instance_name, status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
ORCLCDB OPEN

SQL> archive log list;
ORA-01031: insufficient privileges

取得docket internal ip address:

$ sudo docker inspect 786f903dca1a50deeff130fa2a9bf4b422a7ec4db3ad370d6b01ffe5a62ba865 |grep -i ip
"IPAddress": "172.17.0.2",

or use:

$ sudo docker inspect -f '{{ .NetworkSettings.IPAddress }}' 786f903dca1a50deeff130fa2a9bf4b422a7ec4db3ad370d6b01ffe5a62ba865
172.17.0.2

$ netstat -nr
Kernel IP routing table
Destination Gateway Genmask Flags MSS Window irtt Iface
0.0.0.0 172.31.0.1 0.0.0.0 UG 0 0 0 eth0
169.254.169.254 0.0.0.0 255.255.255.255 UH 0 0 0 eth0
172.17.0.0 0.0.0.0 255.255.0.0 U 0 0 0 docker0
172.31.0.0 0.0.0.0 255.255.240.0 U 0 0 0 eth0

$ telnet 172.17.0.2 1521
Trying 172.17.0.2...
Connected to 172.17.0.2.
Escape character is '^]'.

#Connecting to another RDS Oracle instance using the Oracle client within doker container

$ sudo docker exec -it JayDevDB bash -c "source /home/oracle/.bashrc; sqlplus jaychu/welcome1@:1521/ORCL"

SQL> select status, instance_name from v$instance;

STATUS INSTANCE_NAME
------------ ----------------
OPEN ORCL

Ref:
https://yingclin.github.io/2018/create-oracle-docker-container.html
https://www.digitalocean.com/community/questions/how-to-fix-docker-got-permission-denied-while-trying-to-connect-to-the-docker-daemon-socket
https://linuxconfig.org/how-to-retrieve-docker-container-s-internal-ip-address

星期二, 10月 01, 2019

一年沒有更新

一年沒有更新了，這邊還有人在看嗎?XD

星期五, 7月 13, 2018

如何採集(AWR/ ASH) report

1.先得知ADDM snapshot 的時間區間
col BEGIN_INTERVAL_TIME for a25
col END_INTERVAL_TIME for a25
SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot
where
begin_interval_time between to_timestamp('2018-08-04 12:00:00', 'yyyy-mm-dd hh24:mi:ss') and to_timestamp('2018-08-04 18:00:00', 'yyyy-mm-dd hh24:mi:ss')
order by begin_interval_time ;

2.如何使用command line產出AWR報告了
登入sqlplus後執行 @?/rdbms/admin/ashrpt.sql

ref:
https://oraclespin.com/2009/09/27/how-to-generate-awr-report/

3.如何使用command line產出ASH(Active Session History)報告

[oracle@db01 ~]$ cd /home/oracle/

[oracle@db01 ~]$ . oraenv

ORACLE_SID = [oracle] ? orcl

The Oracle base has been set to /u01/app/oracle

[oracle@db01 ~]$ export ORACLE_SID=orcl1

[oracle@db01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Aug 12 15:52:53 2018

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productio

With the Partitioning, Real Application Clusters, Automatic Storage Managem

ent, OLAP,

Data Mining and Real Application Testing options

SQL>

SQL> @?/rdbms/admin/ashrpt

Current Instance

~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance

----------- ------------ -------- ------------

xxxxxxxxx ORCL 1 orcl1

Specify the Report Type

~~~~~~~~~~~~~~~~~~~~~~~

Enter 'html' for an HTML report, or 'text' for plain text

Defaults to 'html'

Enter value for report_type:

Type Specified: html

Instances in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host

------------ -------- ------------ ------------ ------------

xxxxxxxxx 2 ORCL orcl2 db02

* xxxxxxxxx 1 ORCL orcl1 db01

Defaults to current database

Using database id: xxxxxxxxxx

Enter instance numbers. Enter 'ALL' for all instances in a

RAC cluster or explicitly specify list of instances (e.g., 1,2,3).

Defaults to current instance.

Using instance number(s): 1

ASH Samples in this Workload Repository schema

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Oldest ASH sample available: 03-Aug-18 23:59:28 [ 12469 mins in the pas

Latest ASH sample available: 12-Aug-18 15:48:15 [ 0 mins in the pas

Specify the timeframe to generate the ASH report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Enter begin time for report:

-- Valid input formats:

-- To specify absolute begin time:

-- [MM/DD[/YY]] HH24:MI[:SS]

-- mples: 02/23/03 14:30:15

-- 02/23 14:30:15

-- 14:30:15

-- 14:30

-- To specify relative begin time: (start with '-' sign)

-- -[HH24:]MI

-- mples: -1:15 (SYSDATE - 1 Hr 15 Mins)

-- -25 (SYSDATE - 25 Mins)

Defaults to -15 mins

Enter value for begin_time: 07/05/18 14:00:00

Report begin time specified: 07/05/18 14:00:00

Enter duration in minutes starting from begin time:

Defaults to SYSDATE - begin_time

Press Enter to analyze till current time

Enter value for duration: 120 如果要收集連續兩小時區間的information, 這邊就指定為120 min

Report duration specified: 120

Using 05-Aug-18 14:00:00 as report begin time

Using 05-Aug-18 16:00:00 as report end time

Specify Slot Width (using ashrpti.sql) for 'Activity Over Time' section

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Explanation:

-- In the 'Activity Over Time' section of the ASH report,

-- the analysis period is divided into smaller slots

-- and top wait events are reported in each of those slots.

-- Default:

-- The analysis period will be automatically split upto 10 slots

-- complying to a minimum slot width of

-- 1 minute, if the source is V$ACTIVE_SESSION_HISTORY or

-- 5 minutes, if the source is DBA_HIST_ACTIVE_SESS_HISTORY.

Specify Slot Width in seconds to use in the 'Activity Over Time' section:

Defaults to a value as explained above:

Slot Width specified:

Specify Report Targets (using ashrpti.sql) to generate the ASH report

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

-- Explanation:

-- ASH Report can accept "Report Targets",

-- like a particular SQL statement, or a particular SESSION,

-- to generate the report on. If one or more report targets are

-- specified, then the data used to generate the report will only be

-- the ASH samples that pertain to ALL the specified report targets.

-- Default:

-- If none of the report targets are specified,

-- then the target defaults to all activity in the database instance.

Specify SESSION_ID (eg: from V$SESSION.SID) report target:

Defaults to NULL:

SESSION report target specified:

Specify SQL_ID (eg: from V$SQL.SQL_ID) report target: