Dropping an Empty Partition Without “Update Indexes” Necessary

As we know  the ‘update indexes’ clause is used  to drop the local indexes and update the global index  when we drop a partition. So the indexes will still be VALID”

The below example shows when we drop an empty partition,  the ” Update Indexes” clause is not required.

1) Create partition table :

SQL>CREATE TABLE JAMESTESTCREATE TABLE JAMESTEST( 
  id number, 
  name varchar2(80))
PARTITION BY RANGE ( id )
  ( PARTITION p1 VALUES LESS THAN (1000), 
    PARTITION p2 VALUES LESS THAN (2000));

Table created.

2) Insert records into partition:

SQL>begin 
  for i in 1..1800 loop 
    insert into JAMESTEST values (i, 'JAMES'||to_char(i) ); 
  end loop; 
  commit;
end;

PL/SQL procedure successfully completed.

3)Create global index:

SQL> create index id_idx on JAMESTEST (id) global;
Index created.

SQL>  select index_name, index_type, status from all_indexes where index_name='ID_IDX';
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
ID_IDX                         NORMAL                      VALID

4) Empty partition p2 by deleting all records in p2 partition:

SQL> select count(*) from JAMESTEST ;
  COUNT(*)
----------      
1800

SQL>  select count(*) from JAMESTEST partition ( p1);
  COUNT(*)
----------       
999
SQL>  select count(*) from JAMESTEST partition ( p2);
  COUNT(*)
----------       
801

SQL>  delete from JAMESTEST where ID >= 1000;
801 rows deleted.

SQL>commit;
Commit complete

5) Drop the empty partition p2, and confirm the global index is still valid:

SQL>alter table JAMESTEST drop partition p2;
Table altered.

SQL> select index_name, index_type, status from all_indexes where index_name='ID_IDX';
INDEX_NAME                     INDEX_TYPE                  STATUS
------------------------------ --------------------------- --------
ID_IDX                         NORMAL                      VALID
Posted in Partitioning | Tagged , , , | Leave a comment

The Master Node of RAC Cluster

1) Check from ocssd.log:

$ cat $GRID_HOME/log/racnode1/cssd/ocssd.l* |grep 'master node' |tail -1
2017-07-18 10:09:21.431: [ CSSD][1102125408]clssgmCMReconfig: reconfiguration successful, incarnation 217002855 with 2 nodes, local node number 2, master node number 2

2) Check from crsd.log

$ cat $GRID_HOME/log/racnode1/crsd/crsd.l* |grep 'OCR MASTER' | tail -1
2017-07-18 10:09:10.414: [ OCRMAS][1101056352]th_master:13: I AM THE NEW OCR MASTER at incar 18. Node Number 2

3) Check who ( MASTER ) does OCR backup ;

grid@racnode1:$ ocrconfig -showbackup

racnode2 2017/07/17 13:51:21 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/backup00.ocr
racnode2 2017/07/17 09:51:18 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/backup01.ocr
racnode2 2017/07/17 05:51:16 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/backup02.ocr
racnode2 2017/07/16 05:51:00 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/day.ocr
racnode2 2017/07/06 01:48:12 /u01/app/11.2.0.4/grid/cdata/RACNODE-CLUSTER/week.ocr
PROT-25: Manual backups for the Oracle Cluster Registry are not available
Posted in 12c new features, GI and RAC | Tagged , | Leave a comment

WARNING: Console code page (850) differs from Windows code page (1252) 8-bit characters might not work correctly. See psql reference page “Notes for Windows users” for details.

When trying to connect to my first PostgreSQL database, got the below warning :

C:>psql -h 192.168.78.51 -U postgres -d postgres
Password for user postgres:
psql (9.6.3)
WARNING: Console code page (850) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page "Notes for Windows users" for details.
Type "help" for help.

Below are extracted from psql reference page “Notice for Windows users”:

Notes for Windows Users

psql is built as a "console application". Since the Windows console windows use a different encoding than the rest of the system, you must take special care when using 8-bit characters within psql. If psql detects a problematic console code page, it will warn you at startup. To change the console code page, two things are necessary:

Set the code page by entering cmd.exe /c chcp 1252. (1252 is a code page that is appropriate for German; replace it with your value.) If you are using Cygwin, you can put this command in /etc/profile.
Set the console font to Lucida Console, because the raster font does not work with the ANSI code page.

Set the code page to 1252 , and connect to server without warning:

C:>chcp 1252
Active code page: 1252

C:>psql -h 192.168.78.51 -U postgres -d postgres
Password for user postgres:
psql (9.6.3)
Type "help" for help.

postgres=#
Posted in Client | Tagged , | Leave a comment

Unable to connect to server: could not connect to server: Connection refused (0x0000274D/10061) Is the server running on host “192.168.78.51” and accepting TCP/IP connections on port 5432?

When trying to connect to a new PostgreSQL database, the below error occurred:

C:\Program Files (x86)\pgAdmin 4\v1\runtime>psql -h 192.168.78.51 -d postgres
psql: could not connect to server: Connection refused (0x0000274D/10061)
 Is the server running on host "192.168.78.51" and accepting
 TCP/IP connections on port 5432?

1) Check postgresql server is up and running :

$ systemctl status postgresql-9.6

● postgresql-9.6.service - PostgreSQL 9.6 database server
 Loaded: loaded (/usr/lib/systemd/system/postgresql-9.6.service; enabled; vendor preset: disabled)
 Active: active (running) since Sun 2017-07-16 18:08:47 AEST; 15min ago
 Process: 1238 ExecStartPre=/usr/pgsql-9.6/bin/postgresql96-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 1243 (postmaster)
 CGroup: /system.slice/postgresql-9.6.service
 ├─1243 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
 ├─1246 postgres: logger process
 ├─1248 postgres: checkpointer process
 ├─1249 postgres: writer process
 ├─1250 postgres: wal writer process
 ├─1251 postgres: autovacuum launcher process
 ├─1252 postgres: stats collector process
 └─1822 postgres: postgres postgres 192.168.78.1(56572) idle

2) From client to telnet:

C:\Users\james huang>telnet 192.168.78.51 5432
Connecting To 192.168.78.51...Could not open connection to the host, on port 5432: Connect failed

3) From server to check listener port 5432 not opened on IP 192.168.78.1 :

$ netstat -ntl|grep 5432
tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN
tcp6 0 0 ::1:5432 :::* LISTEN

4)Allow remote IP address to access PostgreSQL:

$ su - postgres
$ vi /var/lib/pgsql/9.6/data/pg_hba.conf

...
..
.

# IPv4 local connections:
host all all 127.0.0.1/32 ident
host all all 192.168.78.51/24 md5
...
..
.

5) Edit /var/lib/pgsql/9.6/data/postgresql.conf

# – Connection Settings –

...
..
.
listen_addresses = '192.168.78.51,localhost' # what IP address(es) to listen on;
 # comma-separated list of addresses;
 # defaults to 'localhost'; use '*' for all
 # (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
...
..
.

6) Stop and Start PostgreSQL server again:

$systemctl status postgresql-9.6

$systemctl stop postgresql-9.6

$systemctl start postgresql-9.6

7) Connect to database successfully:

C:\Program Files (x86)\pgAdmin 4\v1\runtime>psql -h 192.168.78.51 -d postgres -U postgres
Password for user postgres:
psql (9.6.3)
WARNING: Console code page (850) differs from Windows code page (1252)
 8-bit characters might not work correctly. See psql reference
 page "Notes for Windows users" for details.
Type "help" for help.

postgres=# \l
 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
(3 rows)
Posted in Client | Tagged , , , , | Leave a comment

Enable Telnet on Windows 10

Control Panel -> Programs -> Programs and Features ->Turn Windows Features on or off

 

Posted in Windows | Tagged , | Leave a comment

Install PostgreSQL 9.6 on RHEL 7

1)  Go to https://www.postgresql.org/download, and choose your platform accordingly.

2)  Action on the below steps as per instructions :

  • Install the repository RPM:

yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm

  • Install the client packages:

yum install postgresql96

  • Optionally install the server packages:

yum install postgresql96-server

  • Optionally initialize the database and enable automatic start:

/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6

[root@racnode1 ~]# cd /tmp
[root@racnode1 tmp]# yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-redhat96-9.6-3.noarch.rpm
Loaded plugins: ulninfo
Repodata is over 2 weeks old. Install yum-cron? Or run: yum makecache fast
pgdg-redhat96-9.6-3.noarch.rpm | 4.7 kB 00:00:00
Examining /var/tmp/yum-root-p5F8y1/pgdg-redhat96-9.6-3.noarch.rpm: pgdg-redhat96-9.6-3.noarch
Marking /var/tmp/yum-root-p5F8y1/pgdg-redhat96-9.6-3.noarch.rpm to be installed
Resolving Dependencies
--> Running transaction check
---> Package pgdg-redhat96.noarch 0:9.6-3 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 pgdg-redhat96 noarch 9.6-3 /pgdg-redhat96-9.6-3.noarch 2.7 k

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package

Total size: 2.7 k
Installed size: 2.7 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : pgdg-redhat96-9.6-3.noarch 1/1
 Verifying : pgdg-redhat96-9.6-3.noarch 1/1

Installed:
 pgdg-redhat96.noarch 0:9.6-3

Complete!
[root@racnode1 tmp]# yum install postgresql96
Loaded plugins: ulninfo
ksplice-uptrack | 951 B 00:00:00
ol7_UEKR3 | 1.2 kB 00:00:00
ol7_addons | 1.2 kB 00:00:00
ol7_latest | 1.4 kB 00:00:00
pgdg96 | 4.1 kB 00:00:00
(1/2): pgdg96/7Server/x86_64/group_gz | 249 B 00:00:01
(2/2): pgdg96/7Server/x86_64/primary_db | 151 kB 00:00:02
(1/7): ol7_addons/x86_64/primary | 73 kB 00:00:00
(2/7): ol7_addons/x86_64/updateinfo | 38 kB 00:00:00
(3/7): ksplice-uptrack/7Server/x86_64/primary | 2.0 kB 00:00:00
(4/7): ol7_UEKR3/x86_64/updateinfo | 79 kB 00:00:00
(5/7): ol7_latest/x86_64/updateinfo | 1.3 MB 00:00:01
(6/7): ol7_UEKR3/x86_64/primary | 27 MB 00:00:20
(7/7): ol7_latest/x86_64/primary | 26 MB 00:00:20
ksplice-uptrack 7/7
ol7_UEKR3 642/642
ol7_addons 238/238
ol7_latest 19326/19326
Resolving Dependencies
--> Running transaction check
---> Package postgresql96.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Processing Dependency: postgresql96-libs(x86-64) = 9.6.3-1PGDG.rhel7 for package: postgresql96-9.6.3-1PGDG.rhel7.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql96-9.6.3-1PGDG.rhel7.x86_64
--> Running transaction check
---> Package postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 postgresql96 x86_64 9.6.3-1PGDG.rhel7 pgdg96 1.3 M
Installing for dependencies:
 postgresql96-libs x86_64 9.6.3-1PGDG.rhel7 pgdg96 310 k

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package (+1 Dependent package)

Total download size: 1.6 M
Installed size: 8.0 M

Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64.rpm | 310 kB 00:00:03
(2/2): postgresql96-9.6.3-1PGDG.rhel7.x86_64.rpm | 1.3 MB 00:00:04
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 415 kB/s | 1.6 MB 00:00:04
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/2
 Installing : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/2
 Verifying : postgresql96-libs-9.6.3-1PGDG.rhel7.x86_64 1/2
 Verifying : postgresql96-9.6.3-1PGDG.rhel7.x86_64 2/2

Installed:
 postgresql96.x86_64 0:9.6.3-1PGDG.rhel7

Dependency Installed:
 postgresql96-libs.x86_64 0:9.6.3-1PGDG.rhel7

Complete!
[root@racnode1 tmp]#

[root@racnode1 tmp]# yum install postgresql96-server
Loaded plugins: ulninfo
Resolving Dependencies
--> Running transaction check
---> Package postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================================================================================================================================
 Package Arch Version Repository Size
=============================================================================================================================================================================================
Installing:
 postgresql96-server x86_64 9.6.3-1PGDG.rhel7 pgdg96 4.3 M

Transaction Summary
=============================================================================================================================================================================================
Install 1 Package

Total download size: 4.3 M
Installed size: 18 M
Is this ok [y/d/N]: y
Downloading packages:
postgresql96-server-9.6.3-1PGDG.rhel7.x86_64.rpm | 4.3 MB 00:00:05
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
 Installing : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 1/1
 Verifying : postgresql96-server-9.6.3-1PGDG.rhel7.x86_64 1/1

Installed:
 postgresql96-server.x86_64 0:9.6.3-1PGDG.rhel7

Complete!
[root@racnode1 tmp]#

[root@racnode1 tmp]# /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK

[root@racnode1 tmp]# systemctl enable postgresql-9.6
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-9.6.service to /usr/lib/systemd/system/postgresql-9.6.service.
--
-- Start PosgreSQL Server
--
[root@racnode1 tmp]# systemctl start postgresql-9.6
[root@racnode1 tmp]# ps -eaf | grep post
postfix 933 1885 0 15:40 ? 00:00:00 pickup -l -t unix -u
postgres 1259 1 0 15:41 ? 00:00:00 /usr/pgsql-9.6/bin/postmaster -D /var/lib/pgsql/9.6/data/
postgres 1262 1259 0 15:41 ? 00:00:00 postgres: logger process
postgres 1264 1259 0 15:41 ? 00:00:00 postgres: checkpointer process
postgres 1265 1259 0 15:41 ? 00:00:00 postgres: writer process
postgres 1266 1259 0 15:41 ? 00:00:00 postgres: wal writer process
postgres 1267 1259 0 15:41 ? 00:00:00 postgres: autovacuum launcher process
postgres 1268 1259 0 15:41 ? 00:00:00 postgres: stats collector process
root 1277 5694 0 15:41 pts/0 00:00:00 grep --color=auto post
root 1885 1 0 12:50 ? 00:00:00 /usr/libexec/postfix/master -w
postfix 1893 1885 0 12:50 ? 00:00:00 qmgr -l -t unix -u
[root@racnode1 tmp]#

[root@racnode1 ~]# su - postgres
Last login: Thu Jul 13 16:06:15 AEST 2017 on pts/0
-bash-4.2$ psql
psql (9.6.3)
Type "help" for help.

postgres=# \l
 List of databases
 Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
 | | | | | postgres=CTc/postgres
(3 rows)

postgres=#
Posted in Install and Upgrade, PostgreSQL | Tagged , , , , , , , , , , | Leave a comment

ORA-65066: The specified changes must apply to all containers in 12C

 

$ sqlplus sys@pdb1 as sysdba
SQL> alter user system identified by “Password!23”;
alter user system identified by “Password!23”
*
ERROR at line 1:
ORA-65066: The specified changes must apply to all containers

SQL> show con_name

CON_NAME
—————-
PDB1

 

 

SQL> connect / as sysdba
Connected.
SQL> alter user system identified by “Password!23”;

User altered.

SQL> show con_name

CON_NAME
——————————
CDB$ROOT

Posted in 12c new features, ORA- ERRORS | Tagged , | Leave a comment

ORA-02065: illegal option for ALTER SYSTEM

In an old 10g database, when tried to change SGA_TARGET parameter in spfile, “ORA-02065: illegal option for ALTER SYSTEM” occurred.

$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Jun 13 15:56:37 2017
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter sga_target

NAME_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------
TYPE
-----------
VALUE_COL_PLUS_SHOW_PARAM
--------------------------------------------------------------------------------
sga_target
big integer
4G

SQL> alter system set sga_target=2g sid='RACTEST1' scope=spfile;
 alter system set sga_target=2g sid='RACTEST1' scope=spfile
 *
ERROR at line 1:
ORA-02065: illegal option for ALTER SYSTEM

By swapping the order of “sid” and scope, it is working fine:

SQL> alter system set sga_target=2g scope=spfile sid='RACTEST1';

System altered.

SQL>
Posted in Configuration, GI and RAC, ORA- ERRORS | Tagged , | Leave a comment

ORA-02158: invalid CREATE INDEX option

Tried to apply Oracle Advanced Compression Feature onto IOT tables in 12.1.0.2, then got this error.

SQL> CREATE TABLE TEST_IOT_TBL
 ( id   number,
   sex  char(1),
   name varchar2(20),
 CONSTRAINT TEST_IOT_TBL_PK PRIMARY KEY (id,sex ) ENABLE
 ) ORGANIZATION INDEX ;

Table created.

SQL> insert into TEST_IOT_TBL values ( 1,'M','jamesh1');

1 row created.

SQL> insert into TEST_IOT_TBL values (2,'M','jamesh2');

1 row created.

SQL> commit;

Commit complete.

SQL> alter table TEST_IOT_TBL move compress for all operations;
 alter table TEST_IOT_TBL move compress for all operations
 *
ERROR at line 1:
ORA-02158: invalid CREATE INDEX option

 

Posted in ORA- ERRORS, oracle advanced compression | Tagged , , | Leave a comment

ORA-14451: unsupported feature with temporary table

It looks like temporary table can nor be compressed by using Oracle Advanced Compression Features in 12.1.0.2.

SQL> alter session set current_schema=jamesh;

SQL> CREATE GLOBAL TEMPORARY TABLE TEST_TBL ( id number, name varchar(20), CONSTRAINT TEST_TBL_PK PRIMARY KEY (id) ENABLE ) ON COMMIT DELETE ROWS;

Table created.

SQL> insert into TEST_TBL values ( 1,'jamesh');

1 row created.

SQL> select * from TEST_TBL;

ID NAME
---------- --------------------
 1 jamesh

SQL> alter table TEST_TBL move compress for all operations;
 alter table TEST_TBL move compress for all operations
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

SQL> commit;

Commit complete.

SQL> select * from TEST_TBL;

no rows selected

 

Posted in ORA- ERRORS, oracle advanced compression | Tagged , | Leave a comment