Year: 2017

Move OCR, Voting Disk File, ASM SPILE to New Diskgroup

It is recommended to put OCR, Voting File and ASM SPILE onto a dedicated diskgroup.

This exercise is to move everything from old OCR/VOTING diskgroup OCR_VOTE  to a new diskgroup OCR_VOTE2 in 11.2.0.4.

Subscribe to get access

Read more of this content when you subscribe today.

Subscribe

Dropping an Empty Partition Without “Update Indexes” Necessary

“Update Indexes” option can be omitted when dropping an empty partition.

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 following example shows when we drop an empty partition,  the ” Update Indexes” clause is not required.

1) Create partition table
SQL>CREATE TABLE TEST_TBL
    ( 
       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 TEST_TBL values (i, 'TEST'||to_char(i) ); 
  end loop; 
  commit;
end;

PL/SQL procedure successfully completed.

3)Create global index:

SQL> create index id_idx on TEST_TBL (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 TEST_TBL ;

COUNT(*)
----------      
1800

SQL>  select count(*) from TEST_TBL partition ( p1);

COUNT(*)
----------       
999

SQL>  select count(*) from TEST_TBL partition ( p2);

COUNT(*)
----------       
801

SQL>  delete from TEST_TBL 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 TEST_TBL 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

The Master Node of RAC Cluster

The ways to find master node in Oracle RAC environment.

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

WARNING: Console code page (850) differs from Windows code page (1252)

Set the code page by entering “C:> chcp 1252”.

When trying to connect to my first PostgreSQL database, got the following 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 successfully.

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=#

psql: could not connect to server: Connection refused

To avoid “Connection refused”, Configuration is required for client to access PostgreSQL database server.

When trying to connect to a new PostgreSQL database, the following 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?
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
From client telnet to server
C:\Users\postgres>telnet 192.168.78.51 5432
Connecting To 192.168.78.51...Could not open connection to the host, on port 5432: Connect failed
From server 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
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
...
..
.
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)
...
..
.
Stop and Start PostgreSQL server
$systemctl status postgresql-9.6

$systemctl stop postgresql-9.6

$systemctl start postgresql-9.6
Connect to PostgreSQL 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)