[원문 ] : http://bloodguy.tistory.com/entry/PHP-%EB%82%A0%EC%A7%9C-%EC%9D%BC%EC%88%98-%EC%B0%A8%EC%9D%B4-%EA%B3%84%EC%82%B0-datediff-DateTimeDateInterval

정확한 날짜 일수를 계산하기 위한 간편한 방법.
여러가지 방법들이 산더미 같이 있겠지만 다 귀찮고 DateTime, DateInterval 객체를 이용하는 가장 간단한 방법.

<?PHP  /*5.3 이상에서*/

$시작일 = new DateTime('2012-01-01'); // 20120101 같은 포맷도 잘됨
$종료일 = new DateTime('2012-10-11');
// $차이 는 DateInterval 객체. var_dump() 찍어보면 대충 감이 옴.
$차이    = date_diff($시작일, $종료일);
echo $차이->days; // 284
?>

/*php 5.2 버전 에서*/
<?php
$start = new DateTime('2010-10-12');
$end = new DateTime('2010-10-15');
$days = round(($end->format('U') - $start->format('U')) / (60*60*24));
echo $days;
?>

[참조]
http://www.php.net/manual/en/datetime.diff.php
http://www.php.net/manual/en/class.dateinterval.php
http://www.php.net/manual/en/class.datetime.php

[원문]
Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)
http://www.oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1.php

Row Limiting Clause for Top-N Queries in Oracle Database 12c Release 1 (12.1)

• Introduction
• Setup
• Top-N Queries
• Paging Through Data
• Extra Information

Related articles.

• Top-N Queries

Introduction

A Top-N query is used to retrieve the top or bottom N rows from an ordered set. Combining two Top-N queries gives you the ability to page through an ordered set. This concept is not a new one. In fact, Oracle already provides multiple ways to perform Top-N queries, as discussed here. These methods work fine, but they look rather complicated compared to the methods provided by other database engines. For example, MySQL uses a LIMIT clause to page through an ordered result set.

SELECT * 
FROM   my_table 
ORDER BY column_1
LIMIT 0 , 40

Oracle 12c has introduced the row limiting clause to simplify Top-N queries and paging through ordered result sets.

Setup

To be consistent, we will use the same example table used in the Top-N Queries article.

Create and populate a test table.

DROP TABLE rownum_order_test;

CREATE TABLE rownum_order_test (
  val  NUMBER
);

INSERT ALL
  INTO rownum_order_test
  INTO rownum_order_test
SELECT level
FROM   dual
CONNECT BY level <= 10;

COMMIT;

The following query shows we have 20 rows with 10 distinct values.

SELECT val
FROM   rownum_order_test
ORDER BY val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6

       VAL
----------
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

SQL>

Top-N Queries

The syntax for the row limiting clause looks a little complicated at first glance.

[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
    { ROW | ROWS } { ONLY | WITH TIES } ]

Actually, for the classic Top-N query it is very simple. The example below returns the 5 largest values from an ordered set. Using the ONLY clause limits the number of rows returned to the exact number requested.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS ONLY;

       VAL
----------
        10
        10
         9
         9
         8

5 rows selected.

SQL>

Using the WITH TIES clause may result in more rows being returned if multiple rows match the value of the Nth row. In this case the 5th row has the value "8", but there are two rows that tie for 5th place, so both are returned.

SELECT val
FROM   rownum_order_test
ORDER BY val DESC
FETCH FIRST 5 ROWS WITH TIES;

       VAL
----------
        10
        10
         9
         9
         8
         8

6 rows selected.

SQL>

In addition to limiting by row count, the row limiting clause also allows us to limit by percentage of rows. The following query returns the bottom 20% of rows.

SELECT val
FROM   rownum_order_test
ORDER BY val
FETCH FIRST 20 PERCENT ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

4 rows selected.

SQL>

Paging Through Data

Paging through an ordered resultset was a little annoying using the classic Top-N query approach, as it required two Top-N queries, one nested inside the other. For example, if we wanted the second block of 4 rows we might do the following.

SELECT val
FROM   (SELECT val, rownum AS rnum
        FROM   (SELECT val
                FROM   rownum_order_test
                ORDER BY val)
        WHERE rownum <= 8)
WHERE  rnum >= 5;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

With the row limiting clause we can achieve the same result using the following query.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

The starting point for the FETCH is OFFSET+1.

The OFFSET is always based on a number of rows, but this can be combined with a FETCH using a PERCENT.

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

4 rows selected.

SQL>

Not surprisingly, the offset, rowcount and percent can, and probably should, be bind variables.

VARIABLE v_offset NUMBER;
VARIABLE v_next NUMBER;

BEGIN
  :v_offset := 4;
  :v_next   := 4;
END;
/

SELECT val
FROM   rownum_order_test
ORDER BY val
OFFSET :v_offset ROWS FETCH NEXT :v_next ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

SQL>

Extra Information

• The keywords ROW and ROWS can be used interchangeably, as can the FIRST and NEXT keywords. Pick the ones that scan best when reading the SQL like a sentence.
• If the offset is not specified it is assumed to be 0.
• Negative values for the offset, rowcount or percent are treated as 0.
• Null values for offset, rowcount or percent result in no rows being returned.
• Fractional portions of offset, rowcount or percent are truncated.
• If the offset is greater than or equal to the total number of rows in the set, no rows are returned.
• If the rowcount or percent are greater than the total number of rows after the offset, all rows are returned.
• The row limiting clause can not be used with the FOR UPDATE clause, CURRVAL and NEXTVAL sequence pseudocolumns or in an fast refresh materialized view.

For more information see:

• Top-N Queries
• SELECT : row_limiting_clause
• Oracle Scratchpad: 12c First N

작업환경

• CentOS

System 설정

아래의 명령을 실행하여 sysctl.conf 파일을 편집합니다.

아래와 같이 net.ipv4.ip_forward 값을 1 로 변경합니다. 재부팅시에 적용이 됩니다.

아래의 명령을 이용하여 직접 Kernel 변수를 수정할 수 있습니다.

iptables 설정

MASQUERADE 설정

Network Interface (eth0) 을 통한 Port Forwarding

특정 IP 를 통한 Port Forwarding

Local Port Forwarding

[원문] : http://netmaid.tistory.com/archive/20130910

Oracle Linux 는 기본적으로 Red Hat Enterprise Linux 와 동일합니다.

따라서 CentOS 하고도 동일합니다. 단지 내부적으로 최적화를 위해서 몇가지 configuration 이 변경된 것으로 알고 있습니다. 자세한 사항은 http://en.wikipedia.org/wiki/Oracle_Linux 를 참고합니다.

이하 최신 Oracle Linux 6.4 를 기준으로 설치하고, Oracle DB 12c Release 1 을 설치해봅니다.

1. Oracle Linux 설치

다운로드 이후에, iso 이미지를 이용하여 설치를 진행합니다.

중간에 서버의 용도를 선택하는데 여기서 Database Server 로 선택합니다.

2. 네트워크 설정

CentOS 와 동일합니다. 참고: http://netmaid.tistory.com/91

DNS 도 설정합니다. 설정하지 않으면, 오라클 도메인을 찾지 못하여 업데이트가 안될 수도 있습니다.

3. 최신으로 업데이트

yum update 로 진행합니다.

4. Oracle DB 12c Release 1 다운로드

오라클 홈페이지에서 다운로드합니다. Linux x86-64 버전의 linuxamd64_12c_database_1of2.zip, linuxamd64_12c_database_2of2.zip 파일을 다운로드합니다.

scp 를 이용하여 설치하려는 서버로 업로드합니다.

참고: http://netmaid.tistory.com/23

5. 압축해제

$ unzip linuxamd64_12c_database_1of2.zip
$ unzip linuxamd64_12c_database_2of2.zip

6. 오라클 계정 생성

Oracle DB 는 보안상 root 계정으로 설치할 수 없게 되어 있습니다. 별도의 계정을 만듭니다.

$ adduser oracle

5번에서 압축해제한 파일이 root 권한으로 되어 있으므로, oracle 계정 공간으로 옮기고 권한도 바꿉니다.

$ mv database /home/oracle
$ chown -R oracle:oracle /home/oracle/database

7. Xfce 설치

Oracle DB 는 GUI 환경에서 설치를 진행합니다. 원격의 GUI 환경에서 접속하여 Oracle DB 를 설치합니다.

원격의 GUI 환경으로는 Xubuntu 를 선택했습니다. Xubuntu 는 Xfce 를 기본 xwindow 로 탑재하고 있습니다.

따라서 Oracle Linux 서버에도 Xfce 를 설치합니다.

Xfce 를 설치하려면 EPEL 저장소를 추가해야 합니다.

$ yum install wget
$ wget http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
$ rpm -ivh epel-release-6.8.noarch.rpm

Xfce 를 설치합니다.

$ yum groupinstall Xfce

추가로 폰트, 인증 모듈, 유틸 모듈도 설치합니다.

$ yum install xorg-x11-fonts-Type1 xorg-x11-fonts-misc
$ yum install xorg-x11-xauth xorg-x11-utils

참고:

• http://blog.inticol.com/2012/10/installing-xfce-on-centos-6.html
• http://www.rackspace.com/knowledge_center/article/installing-rhel-epel-repo-on-centos-5x-or-6x
• http://adminoracle10g.blogspot.kr/2013/04/checking-monitor-must-be-configured-to_23.html

8. ssh 를 통해서 xwindow 접속이 가능하도록 설정

sshd_config 를 수정하여 다음의 3개를 활성화시킵니다.

$ vi /etc/ssh/sshd_config
.....
X11Forwarding yes
X11DisplayOffset 10 
X11UseLocalhost yes

9. Xubuntu 를 설치한 서버에서 Oracle Linux 로 접속

터미널 창을 띄우고 ssh 로 -X 옵션을 지정하여 접속합니다. (원격으로 xwindow 를 활성화하여 접속하는 것입니다)

$ ssh -X oracle@oracle_linux_ip

10. hostname 점검

원격으로 접속을 성공했으면, hostname 과 ip 를 확인합니다.

$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME = test.domain.com

$ vi /etc/hosts
127.0.0.1 test test.domain.com localhost localhost.localdomain localhost4 localhost4.localdomain4
::1       test test.domain.com localhost localhost.localdomain localhost4 localhost4.localdomain4

제대로 설정되지 않으면 DB 설치 진행 중에 다음과 같은 오류 메시지가 뜰 것입니다.

PRVF-0002 : Could not retrieve local nodename

참고:

• http://vitkalovav.blogspot.kr/2013/07/prvf-0002-could-not-retrieve-local.html

11. DB data 디렉토리 생성

root 권한으로 디렉토리를 생성합니다. 생성한 디렉토리는 oracle 계정 권한으로 만듭니다.

$ su root
$ mkdir /oradata
$ chown oracle:oracle /oradata

12. limits.conf 변경

root 권한으로 보안 설정을 바꿉니다. 이는 성능을 높이기 위해 시스템 자원을 많이 사용할 수 있도록 설정하는 것입니다. 이 설정을 바꾸지 않으면, 나중에 DB 설치 진행 중에 경고가 뜹니다.

$ vi /etc/security/limits.conf
.....
oracle	soft	nproc	2047
oracle	hard	nproc	16384
oracle	soft	nofile	1024
oracle	hard	nofile	65536

참고:

• https://forums.oracle.com/message/9812015?tstart=0

13. sysctl.conf 변경

root 권한으로 시스템 설정을 바꿉니다. 이 설정을 바꾸지 않으면, 나중에 DB 설치 진행 중에 경고가 뜹니다.

$ vi /etc/sysctl.conf
.....
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.aio-max-nr = 1048576
fs.file-max = 6815744

net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

참고:

• http://www.jameskoopmann.com/docs/Install_Oracle11gCentOS5.htm
• http://www.oracle-base.com/articles/11g/oracle-db-11gr2-installation-on-oracle-linux-5.php

14. Oracle DB 를 위한 종속적인 패키지 설치

root 권한으로 Oracle DB 설치에서 필요한 패키지를 추가로 설치해둡니다.

$ yum install compat-libcap1 gcc gcc-c++ ksh compat-libstdc++-33 libaio-devel

15. 시스템을 리부팅

12번과 13번에서 시스템 설정을 변경했기 때문에 root 권한으로 리부팅을 합니다.

$ sudo shutdown -r now

16. Oracle DB 설치

리부팅이 완료되면, 9번처럼 원격의 Xubuntu 에서 oracle 계정 권한으로 서버에 접속합니다. Oracle DB 를 설치합니다.

$ cd database
$ ./runInstaller

GUI 에 따라서 진행합니다. 오라클 계정이 있다면, 최신 업데이트를 받을 수 있습니다. 그리고, 중간에 새로운 database 를 생성하는 옵션으로 설정합니다. (대부분은 이것을 선택할 것입니다.)

설치를 진행하다가 다음 화면에서 멈춥니다.

17. root 로 스크립트 설치

다른 터미널을 통해서 Oracle Linux 서버에 root 권한으로 접속합니다.

GUI 에서 명시한 대로 스크립트를 실행합니다.

$ /home/oracle/app/oraInventory/orainstRoot.sh
$ /home/oracle/app/oracle/product/12.1.0/dbhome_1/root.sh

18. Oracle DB 설치 계속

스크립트를 실행한 뒤에 GUI 를 계속 진행합니다. 그러면 설치가 마무리 됩니다.

19. 방화벽 설정

외부 서버에서 접속이 가능하도록 방화벽의 포트를 개방합니다.

$ vi /etc/sysconfig/iptables
.....
.....

참고:

• http://docs.oracle.com/cd/E16655_01/install.121/e17720/app_port.htm#CHDEHAIF

20. 접속 테스트

Oracle DB 설치 참고 문서:

• http://www.oracle.com/pls/db121/portal.portal_db?selected=11

[원문] : http://vitkalovav.blogspot.kr/2013/07/prvf-0002-could-not-retrieve-local.html


PRVF-0002 : Could not retrieve local nodename

$ ORACLE_HOSTNAME=test.localdomain
$ export ORACLE_HOSTNAME

If you want to change it, you need to do this on the client, as Remus already mentioned. The client is creating the connection pool.

You can specify the connection pooling properties in your connection string that you use to connect to SQL Server. The most important properties are:

• Pooling : which can be true or false - use pooling or not
• MinPoolSize : minimum size of connection pool; default is 10
• MaxPoolSize : maximum size of connection pool; default is 100

So if you want to enable pooling and have min. 20, max. 250 connections, you could use this connection string:

server=MyServer;database=MyDatabase;Pooling=True;Min Pool Size=25;Max Pool Size=250

For more details, see the MSDN docs or check out the Connection Strings web site.

Marc

netstat -an | find "80" | find /c "ESTABLISHED"

80 is port number

if i set port number , i get total of port number !

In Part 1 of the HOWTO we built the virtual machine and configured the Oracle Linux 6 operating systems. Here we continue with the Database 12c installation.

In order to install Database 12c your VM has to meet some prerequisites as outlined in the Database Installation Guide for Linux. Most of the pre-installation tasks can be completed by installing the 12cR1 Pre-install RPM. Let’s open a terminal console and install this package.

[root@ol6 ~]# yum install oracle-rdbms-server-12cR1-preinstall.x86_64
Loaded plugins: refresh-packagekit, security
Setting up Install Process
Resolving Dependencies
--> Running transaction check
---> Package oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-8.el6 will be installed
...
--> Finished Dependency Resolution

Dependencies Resolved

===============================================================================================================================================================================================================
 Package                                                             Arch                                  Version                                             Repository                                 Size
===============================================================================================================================================================================================================
Installing:
 oracle-rdbms-server-12cR1-preinstall                                x86_64                                1.0-8.el6                                           ol6_latest                                 15 k
Installing for dependencies:
 compat-libcap1                                                      x86_64                                1.10-1                                              ol6_latest                                 17 k
 compat-libstdc++-33                                                 x86_64                                3.2.3-69.el6                                        ol6_latest                                183 k
 gcc-c++                                                             x86_64                                4.4.7-3.el6                                         ol6_latest                                4.7 M
 ksh                                                                 x86_64                                20100621-19.el6_4.4                                 ol6_latest                                686 k
 libaio-devel                                                        x86_64                                0.3.107-10.el6                                      ol6_latest                                 13 k
 libstdc++-devel                                                     x86_64                                4.4.7-3.el6                                         ol6_latest                                1.6 M

Transaction Summary
===============================================================================================================================================================================================================
Install       7 Package(s)

Total download size: 7.2 M
Installed size: 23 M
Is this ok [y/N]: y
Downloading Packages:
(1/7): compat-libcap1-1.10-1.x86_64.rpm                                                                                                                                                 |  17 kB     00:00
...
Installed:
  oracle-rdbms-server-12cR1-preinstall.x86_64 0:1.0-8.el6

Dependency Installed:
  compat-libcap1.x86_64 0:1.10-1            compat-libstdc++-33.x86_64 0:3.2.3-69.el6      gcc-c++.x86_64 0:4.4.7-3.el6      ksh.x86_64 0:20100621-19.el6_4.4      libaio-devel.x86_64 0:0.3.107-10.el6
  libstdc++-devel.x86_64 0:4.4.7-3.el6

Complete!
[root@ol6 ~]#

The pre-install RPM automatically creates the Oracle software owner account (named oracle). Let’s set a password for the account.

[root@ol6 ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: it is based on a dictionary word
Retype new password:
passwd: all authentication tokens updated successfully.
[root@ol6 ~]#

Next you need to create the Oracle base and the Oracle inventory directories:

[root@ol6 ~]# mkdir -p /u01/app/oracle
[root@ol6 ~]# chown -R oracle:oinstall /u01/app/oracle/
[root@ol6 ~]# chmod -R 775 /u01/app/oracle/
[root@ol6 ~]#
[root@ol6 ~]# mkdir -p /u01/app/oraInventory
[root@ol6 ~]# chown -R oracle:oinstall /u01/app/oraInventory/
[root@ol6 ~]# chmod -R 775 /u01/app/oraInventory/

Time to switch to the oracle user. Logout from your current session and login back as oracle.

You will need the Database 12c Release 1 installation for Linux. Start the internet browser inside your virtual machine and go to the Database download section of OTN.

Download the two zip files to a convenient location (/home/oracle will do nicely). After both files are in place use unzip to extract their contents.

[root@ol6 ~]$ unzip /home/oracle/*.zip

Run the Oracle Universal Installer by executing:

[root@ol6 ~]$ /home/oracle/database/runInstaller.sh

As this is a test installation you do not have to register it with Oracle Support. Uncheck the “I wish to receive security updates via Oracle Support” box and click Next.

Select “Skip software updates” and click Next.

Select “Create and configure a database” and click Next.

Select “Server class” and click Next.

Make sure the installation type is set to “Single instance” and click Next.

Select “Advanced install” and click Next.

Select English for your product language and click Next.

Set the edition to Enterprise and click Next.

Set the Oracle base to /u01/app/oracle and the Oracle home to /u01/app/oracle/product/12.1.0/dbhome_1.

Set the inventory directory to /u01/app/oraInventory and the inventory owner to oinstall.

Set the database type to General Purpose and click Next.

Set the database name and SID to orcl and uncheck the “Create as Container database” option.

Leave the memory settings by default and go to the Character sets tab.

Set the database character set to Unicode (AL32UTF8).

At the Sample schemas tab select the “Create database with sample schemas”. This will install some sample data that you can experiment with.

Select File system as the database storage option and set the path to /u01/app/oracle/oradata.

You have no Cloud Control installation in this test setup, so skip the registration and click Next.

Do not enable the archivelog mode. Click Next.

Select “Use the same password for all accounts” and set the system password for your database.

Set all the groups to dba and click Next.

The installer presents you with a summary of the upcoming installation. Click Install.

Give the installer some time to copy the oracle binaries to the specified location.

At some point you will be asked to execute a pair of configuration scripts. Open a terminal console, become root and run the scripts in the specified order.

[oracle@ol6 ~]$ su -
Password: 
[root@ol6 ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory.
Adding read,write permissions for group.
Removing read,write,execute permissions for world.

Changing groupname of /u01/app/oraInventory to oinstall.
The execution of the script is complete.
[root@ol6 ~]# /u01/app/oracle/product/12.1.0/dbhome_1/root.sh
Performing root user operation for Oracle 12c

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /u01/app/oracle/product/12.1.0/dbhome_1

Enter the full pathname of the local bin directory: [/usr/local/bin]:
   Copying dbhome to /usr/local/bin ...
   Copying oraenv to /usr/local/bin ...
   Copying coraenv to /usr/local/bin ...


Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
[root@ol6 ~]# 

Close the terminal and click OK in the Execute configuration scripts dialog.

After the database binaries are in place the installer will launch the Database Configuration Assistant (DBCA). The assistant will create the orcl database and will insert the sample data into it.

When DBCA completes it will display a dialog with the address of your Enterprise Manager console. Take a note of the URL and click OK.

The installation is now completed. Click Close to exit the installer.

Open a browser and type in the Enterprise Manager URL.

Login as user system (use the system password you set during the database installation).

You can see your Database 12c up and running.