星期六, 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

星期五, 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

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Productio
n
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
t]
Latest ASH sample available:  12-Aug-18 15:48:15   [      0 mins in the pas
t]


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:
Defaults to NULL: (% and _ wildcards allowed)
SQL report target specified:


Specify WAIT_CLASS name (eg: from V$EVENT_NAME.WAIT_CLASS) report target:
[Enter 'CPU' to investigate CPU usage]
Defaults to NULL: (% and _ wildcards allowed)
WAIT_CLASS report target specified:


Specify SERVICE_HASH (eg: from V$ACTIVE_SERVICES.NAME_HASH) report target:
Defaults to NULL:
SERVICE report target specified:


Specify MODULE name (eg: from V$SESSION.MODULE) report target:
Defaults to NULL: (% and _ wildcards allowed)
MODULE report target specified:


Specify ACTION name (eg: from V$SESSION.ACTION) report target:
Defaults to NULL: (% and _ wildcards allowed)
ACTION report target specified:


Specify CLIENT_ID (eg: from V$SESSION.CLIENT_IDENTIFIER) report target:
Defaults to NULL: (% and _ wildcards allowed)
CLIENT_ID report target specified:


Specify PLSQL_ENTRY name (eg: "SYS.DBMS_LOB.*") report target:
Defaults to NULL: (% and _ wildcards allowed)
PLSQL_ENTRY report target specified:

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is ashrpt_1_0705_1600.html.  To use this name,
press <return> to continue, otherwise enter an alternative.
Enter value for report_name: ashrpt_orcl1_0705_1600.html <--輸入想要產出的檔案名稱

Using the report name ashrpt_orcl1_0705_1600.html

Summary of All User Input
-------------------------
Format         : HTML
DB Id          : xxxxxxxxx 
Inst num       : 1
Begin time     : 05-Aug-18 14:00:00
End time       : 05-Aug-18 16:00:00
Slot width     : Default
Report targets : 0
Report name    : ashrpt_orcl1_0705_1600.html



星期一, 7月 02, 2018

[FWD] Oracle DataGuard Broker re-initiate primary步驟

參考link link

由於Oracle DataGard Broker已經建置, 可在Standby failover開啟之後, 以下列流程re-initiate primary
----------------------------------------------------------------------
DGMGRL> connect sys/*********@STANDBY
DGMGRL> failover to 'standby'

Check the configuration again:
DGMGRL> show configuration
Configuration - broker1
  Protection Mode: MaxPerformance
  Members:
  standby - Primary database
    PRIMARY - Physical standby database (disabled)
      ORA-16661: the standby database needs to be reinstated
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS   (status updated 53 seconds ago)

--Set Old Primary DB to mount status
SQL> startup mount
SQL> select db_unique_name,database_role,flashback_on from v$database;
DB_UNIQUE_NAME DATABASE_ROLE    FLASHBACK_ON
------------------------------ ---------------- ------------------
PRIMARY PRIMARY          YES
With the database in mount try a reinstate again:

PRIMARY>dgmgrl
DGMGRL> connect sys/*********@STANDBY
DGMGRL> reinstate database 'PRIMARY'

DGMGRL> validate database 'PRIMARY'
  Database Role:     Physical standby database
  Primary Database:  standby
  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups Standby Redo Log Groups Status
    (standby) (PRIMARY)
1 4 3 Insufficient SRLs
  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups Standby Redo Log Groups Status
(PRIMARY) (standby)
1 4 3 Insufficient SRLs

--如果validate database看到 Insufficient SRLs訊息代表沒有建立足夠的standby logfile
alter database add standby logfile thread 1 group 4 size 536870912;
alter database add standby logfile thread 1 group 5 size 536870912;
alter database add standby logfile thread 1 group 6 size 536870912;
alter database add standby logfile thread 1 group 7 size 536870912;
----------------------------------------------------------------------

延伸閱讀
Oracle 11g DG Broker and Observer Health Conditions 設定步驟整理
https://jaychu649.blogspot.com/2013/03/oracle-dg-broker-and-observer.html

Oracle DataGuard停止同步步驟(10g+)
https://jaychu649.blogspot.com/2018/05/oracle-dataguard10g.html

星期五, 6月 29, 2018

Oracle 11g+ RAC CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time.

0.確認CRSS daemon是處於Observer mode or Active mode (如果config ntpd 就會變成observer mode)
[grid@db1 ~]$  crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.

[grid@db2 ~]$  crsctl check ctss
CRS-4700: The Cluster Time Synchronization Service is in Observer mode.


1. db1 crs alert log 有看到以下訊息, 兩台主機的時間應該還是不太一致, 光是有時間差就會造成RAC的單台DB被重啟

2018-06-xx xx:xx:xx.xx [OCTSSD(9974)]CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time. Details in /u01/app/grid/diag/crs/db1/crs/trace/octssd.trc.
2018-06-xx xx:xx:xx.xx [OCTSSD(9974)]CRS-2412: The Cluster Time Synchronization Service detects that the local time is significantly different from the mean cluster time. Details in /u01/app/grid/diag/crs/db1/crs/trace/octssd.trc.

2.建議確認DB1, DB2主機上的時間, 確定是否有時間差
#At Both DB Servers
date

3.如有時間差, 先disable chronyd服務, 再ntpd 服務帶起

3.1 Disable chronyd service
#chrony 是 RHEL/CentOS 7 預設的校時 dameon, 如果您的作業系統是RHEL6 請忽略相關步驟
#In order to use ntpd the default user space daemon, chronyd, must be stopped and disabled
systemctl stop chronyd
systemctl disable chronyd
systemctl status chronyd

3.2 NTP Client Configuration
yum install ntp
-------------------------------------------------------------------------
## cat /etc/ntp.conf
restrict default kod nomodify notrap nopeer noquery
restrict -6 default kod nomodify notrap nopeer noquery
restrict 127.0.0.1
restrict -6 ::1
server 0.pool.ntp.org
server 1.pool.ntp.org
server 2.pool.ntp.org
server 3.pool.ntp.org
restrict #ntp_ip_address mask 255.255.255.0 nomodify notrap
driftfile /var/lib/ntp/drift
keys /etc/ntp/keys
-------------------------------------------------------------------------
#systemctl list-unit-files --state=enabled
systemctl enable ntpd.service
systemctl start ntpd.service

3.3 Configure firewall to enable NTP port 123
# firewall-cmd --zone=public --add-port=123/udp --permanent
success

# firewall-cmd --reload
success


不可使用ntpdate 的指令放到cron table去更新時間
#手動更新較快
ntpdate -d xxx.xxx.xxx.xxx #這裡為實際ntp serverip address

3.4 Verify ntpd service
#At Both DB Servers, verify /etc/ntp.conf寫法是否正確
service ntpd status
service ntpd start

#Verify
ntpq -p

3.5 開啟"微調模式"
[root@ ~]# vi /etc/sysconfig/ntpd
#The following item added by Robinson
#Set to 'yes' to sycn hw clock after successful ntpdate
SYNC_HWCLOCK=yes #開啟同步更新硬體時間
OPTIONS="-x -u ntp:ntp -p /var/run/ntpd.pid -g" #加-x 參數,開啟微調模式

4.手動把時間從系統回寫到bios
#update date to bios
hwclock --systohc

5.確認是否關閉numa, transparent_hugepages  (Disable Transparent_Hugepages以避免node eviction問題)
[root@oraracx1 ~]# cat /etc/default/grub
GRUB_TIMEOUT=5
GRUB_DISTRIBUTOR="$(sed 's, release .*$,,g' /etc/system-release)"
GRUB_DEFAULT=saved
GRUB_DISABLE_SUBMENU=true
GRUB_TERMINAL_OUTPUT="console"
GRUB_CMDLINE_LINUX="crashkernel=auto rd.lvm.lv=ol/root rd.lvm.lv=ol/swap rhgb quiet numa=off transparent_hugepage=never intel_idle.max_cstate=0 processor.max_cstate=0"
GRUB_DISABLE_RECOVERY="true"

Ref:
ALERT: Disable Transparent HugePages on SLES11, RHEL6, RHEL7, OL6, OL7, and UEK2 and above (Doc ID 1557478.1)

ref:
http://www.voidcn.com/article/p-rwdsexfj-bcp.html
https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/7/html/system_administrators_guide/sect-configuring_the_date_and_time-hwclock

FWD: Disable and Remove Unwanted Services on RHEL/CentOS 7 Minimal Installation
https://jaychu649.blogspot.com/2018/05/fwd-disable-and-remove-unwanted.html

星期二, 6月 26, 2018

前一陣子在專案上的心得

前一陣子在專案上的心得(督促自己也督促工程師) 1.專案經驗是別人竊取不走的,心態轉換很重要! (Self-motivation) 2.花一樣的時間在專案上,就要盡全力想辦法把不足的領域學習好。 3.Project collaboration is as important as Project Managent , which is crucial to success. 4.心中無須劃分一道牆,間接告訴大家我什麼不做,我只做什麼(客戶與專案成功至上) 5.自我心理建設與學習態度很重要,相信自己能做到,勇於嘗試永不放棄。 #projectmanagement #lessons_learned

LinkWithin-相關文件

Related Posts Plugin for WordPress, Blogger...