Feed aggregator

cluvfy is your friend

Michael Dinh - Sun, 2016-10-09 18:54

Just a reminder to self to use cluvfy

olsnodes -i -n -s -t
grep 'master node' $CRS_HOME/log/`hostname -s`/cssd/ocssd.*|tail -1

cluvfy stage -pre help
cluvfy stage -post  help

++++++++++


[grid@rac01:+ASM1:/home/grid]
$ olsnodes -i -n -s -t
rac01   1       rac01-vip       Active  Unpinned
rac02   2       rac02-vip       Active  Unpinned

++++++++++

[grid@rac01:+ASM1:/home/grid]
$ env|grep HOME
CRS_HOME=/u01/app/11.2.0.4/grid
HOME=/home/grid
XAG_HOME=/u01/app/grid/xag
ORACLE_HOME=/u01/app/11.2.0.4/grid

++++++++++

[grid@rac01:+ASM1:/home/grid]
$ grep 'master node' $CRS_HOME/log/`hostname -s`/cssd/ocssd.*|tail -1
/u01/app/11.2.0.4/grid/log/rac01/cssd/ocssd.log:2016-10-09 10:48:55.837: [    CSSD][28161792]clssgmCMReconfig: reconfiguration successful, incarnation 371471500 with 2 nodes, local node number 1, master node number 1

++++++++++

[grid@rac01:+ASM1:/home/grid]
$ cluvfy stage -pre help

ERROR:
Unexpected symbol "help". See usage for detail.

USAGE:
cluvfy stage {-pre|-post}    [-verbose]

SYNTAX (for Stages):
cluvfy stage -pre cfs -n  -s  [-verbose]
cluvfy stage -pre
                   crsinst -file  [-fixup [-fixupdir ]] [-verbose]
                   crsinst -upgrade [-n ] [-rolling] -src_crshome  -dest_crshome 
                           -dest_version  [-fixup [-fixupdir ]] [-verbose]
                   crsinst -n  [-r {10gR1|10gR2|11gR1|11gR2}]
                           [-c ] [-q ]
                           [-osdba ] [-orainv ]
                           [-asm [-asmgrp ] [-asmdev ]] [-crshome ]
                           [-fixup [-fixupdir ]] [-networks ]
                           [-verbose]
cluvfy stage -pre acfscfg -n  [-asmdev ] [-verbose]
cluvfy stage -pre
                   dbinst -n  [-r {10gR1|10gR2|11gR1|11gR2}] [-osdba ] [-d ]
                          [-fixup [-fixupdir ]] [-verbose]
                   dbinst -upgrade -src_dbhome  [-dbname ] -dest_dbhome  -dest_version 
                          [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -pre dbcfg -n  -d  [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -pre hacfg [-osdba ] [-orainv ] [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -pre nodeadd -n  [-vip ] [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -post hwos -n  [-s ] [-verbose]
cluvfy stage -post cfs -n  -f  [-verbose]
cluvfy stage -post crsinst -n  [-verbose]
cluvfy stage -post acfscfg -n  [-verbose]
cluvfy stage -post hacfg [-verbose]
cluvfy stage -post nodeadd -n  [-verbose]
cluvfy stage -post nodedel -n  [-verbose]

++++++++++

[grid@rac01:+ASM1:/home/grid]
$ cluvfy stage -post  help

ERROR:
Unexpected symbol "help". See usage for detail.

USAGE:
cluvfy stage {-pre|-post}    [-verbose]

SYNTAX (for Stages):
cluvfy stage -pre cfs -n  -s  [-verbose]
cluvfy stage -pre
                   crsinst -file  [-fixup [-fixupdir ]] [-verbose]
                   crsinst -upgrade [-n ] [-rolling] -src_crshome  -dest_crshome 
                           -dest_version  [-fixup [-fixupdir ]] [-verbose]
                   crsinst -n  [-r {10gR1|10gR2|11gR1|11gR2}]
                           [-c ] [-q ]
                           [-osdba ] [-orainv ]
                           [-asm [-asmgrp ] [-asmdev ]] [-crshome ]
                           [-fixup [-fixupdir ]] [-networks ]
                           [-verbose]
cluvfy stage -pre acfscfg -n  [-asmdev ] [-verbose]
cluvfy stage -pre
                   dbinst -n  [-r {10gR1|10gR2|11gR1|11gR2}] [-osdba ] [-d ]
                          [-fixup [-fixupdir ]] [-verbose]
                   dbinst -upgrade -src_dbhome  [-dbname ] -dest_dbhome  -dest_version 
                          [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -pre dbcfg -n  -d  [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -pre hacfg [-osdba ] [-orainv ] [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -pre nodeadd -n  [-vip ] [-fixup [-fixupdir ]] [-verbose]
cluvfy stage -post hwos -n  [-s ] [-verbose]
cluvfy stage -post cfs -n  -f  [-verbose]
cluvfy stage -post crsinst -n  [-verbose]
cluvfy stage -post acfscfg -n  [-verbose]
cluvfy stage -post hacfg [-verbose]
cluvfy stage -post nodeadd -n  [-verbose]
cluvfy stage -post nodedel -n  [-verbose]

++++++++++

[grid@rac01:+ASM1:/home/grid]
$ cluvfy stage -pre crsinst -n rac01,rac02 -fixup

Performing pre-checks for cluster services setup

Checking node reachability...
Node reachability check passed from node "rac01"


Checking user equivalence...
User equivalence check passed for user "grid"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "eth1"
Node connectivity passed for interface "eth1"
TCP connectivity check passed for subnet "192.168.56.0"


Check: Node connectivity for interface "eth2"
Node connectivity passed for interface "eth2"
TCP connectivity check passed for subnet "10.0.0.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.56.0".
Subnet mask consistency check passed for subnet "10.0.0.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "192.168.56.0" for multicast communication with multicast group "230.0.1.0"...
Check of subnet "192.168.56.0" for multicast communication with multicast group "230.0.1.0" passed.

Checking subnet "10.0.0.0" for multicast communication with multicast group "230.0.1.0"...
Check of subnet "10.0.0.0" for multicast communication with multicast group "230.0.1.0" passed.

Check of multicast communication passed.

Checking ASMLib configuration.
Check for ASMLib configuration passed.
Total memory check passed
Available memory check passed
Swap space check passed
Free disk space check passed for "rac02:/u01/app/11.2.0.4/grid,rac02:/tmp"
Free disk space check passed for "rac01:/u01/app/11.2.0.4/grid,rac01:/tmp"
Check for multiple users with UID value 54322 passed
User existence check passed for "grid"
Group existence check passed for "oinstall"
Group existence check passed for "dba"
Membership check for user "grid" in group "oinstall" [as Primary] passed
Membership check for user "grid" in group "dba" passed
Run level check passed
Hard limits check passed for "maximum open file descriptors"
Soft limits check passed for "maximum open file descriptors"
Hard limits check passed for "maximum user processes"
Soft limits check passed for "maximum user processes"
System architecture check passed
Kernel version check passed
Kernel parameter check passed for "semmsl"
Kernel parameter check passed for "semmns"
Kernel parameter check passed for "semopm"
Kernel parameter check passed for "semmni"
Kernel parameter check passed for "shmmax"
Kernel parameter check passed for "shmmni"
Kernel parameter check passed for "shmall"
Kernel parameter check passed for "file-max"
Kernel parameter check passed for "ip_local_port_range"
Kernel parameter check passed for "rmem_default"
Kernel parameter check passed for "rmem_max"
Kernel parameter check passed for "wmem_default"
Kernel parameter check passed for "wmem_max"
Kernel parameter check passed for "aio-max-nr"
Package existence check passed for "binutils"
Package existence check passed for "compat-libcap1"
Package existence check passed for "compat-libstdc++-33(x86_64)"
Package existence check passed for "libgcc(x86_64)"
Package existence check passed for "libstdc++(x86_64)"
Package existence check passed for "libstdc++-devel(x86_64)"
Package existence check passed for "sysstat"
Package existence check passed for "gcc"
Package existence check passed for "gcc-c++"
Package existence check passed for "ksh"
Package existence check passed for "make"
Package existence check passed for "glibc(x86_64)"
Package existence check passed for "glibc-devel(x86_64)"
Package existence check passed for "libaio(x86_64)"
Package existence check passed for "libaio-devel(x86_64)"
Check for multiple users with UID value 0 passed
Current group ID check passed

Starting check for consistency of primary group of root user

Check for consistency of root user's primary group passed

Starting Clock synchronization checks using Network Time Protocol(NTP)...

NTP Configuration file check started...
No NTP Daemons or Services were found to be running

Clock synchronization check using Network Time Protocol(NTP) passed

Core file name pattern consistency check passed.

User "grid" is not part of "root" group. Check passed
Default user file creation mask check failed
Check failed on nodes:
        rac02,rac01
Checking consistency of file "/etc/resolv.conf" across nodes

File "/etc/resolv.conf" does not have both domain and search entries defined
domain entry in file "/etc/resolv.conf" is consistent across nodes
search entry in file "/etc/resolv.conf" is consistent across nodes
All nodes have one search entry defined in file "/etc/resolv.conf"
The DNS response time for an unreachable node is within acceptable limit on all nodes

File "/etc/resolv.conf" is consistent across nodes

Time zone consistency check passed

Starting check for Reverse path filter setting ...

Check for Reverse path filter setting passed

Pre-check for cluster services setup was unsuccessful on all the nodes.

++++++++++

[grid@rac01:+ASM1:/home/grid]
$ umask
0002
[grid@rac01:+ASM1:/home/grid]
$ ssh rac02 "umask"
0022
[grid@rac0

+++++++++

[grid@rac01:+ASM1:/home/grid]
$ cluvfy stage -post hwos -n rac01,rac02

Performing post-checks for hardware and operating system setup

Checking node reachability...
Node reachability check passed from node "rac01"


Checking user equivalence...
User equivalence check passed for user "grid"

Checking node connectivity...

Checking hosts config file...

Verification of the hosts config file successful

Check: Node connectivity for interface "eth1"
Node connectivity passed for interface "eth1"
TCP connectivity check passed for subnet "192.168.56.0"


Check: Node connectivity for interface "eth2"
Node connectivity passed for interface "eth2"
TCP connectivity check passed for subnet "10.0.0.0"

Checking subnet mask consistency...
Subnet mask consistency check passed for subnet "192.168.56.0".
Subnet mask consistency check passed for subnet "10.0.0.0".
Subnet mask consistency check passed.

Node connectivity check passed

Checking multicast communication...

Checking subnet "192.168.56.0" for multicast communication with multicast group "230.0.1.0"...
Check of subnet "192.168.56.0" for multicast communication with multicast group "230.0.1.0" passed.

Checking subnet "10.0.0.0" for multicast communication with multicast group "230.0.1.0"...
Check of subnet "10.0.0.0" for multicast communication with multicast group "230.0.1.0" passed.

Check of multicast communication passed.
Check for multiple users with UID value 0 passed
Time zone consistency check passed

Checking shared storage accessibility...

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sde                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdd                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdg                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdh                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdi                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdf                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdb                              rac02 rac01

  Disk                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /dev/sdc                              rac02 rac01

  ACFS                                  Sharing Nodes (2 in count)
  ------------------------------------  ------------------------
  /acfsmount                            rac02 rac01


Shared storage check was successful on nodes "rac02,rac01"

Checking integrity of name service switch configuration file "/etc/nsswitch.conf" ...
All nodes have same "hosts" entry defined in file "/etc/nsswitch.conf"
Check for integrity of name service switch configuration file "/etc/nsswitch.conf" passed


Post-check for hardware and operating system setup was successful.
[grid@rac01:+ASM1:/home/grid]
$

Upcoming Public Appearances

Randolf Geist - Sun, 2016-10-09 16:54
It's that time of the year again... I'll be speaking at the following events:

- SSWUG 2016 Fall Virtual Conference: On November 15th I'll do the geeky stuff that was rejected by DOAG this year, which is "Oracle Database Parallel Execution plans deep dive - understanding the internals of Parallel Execution plans". You should only attend this session if you already have a good understanding of serial execution plans.

- DOAG Conference 2016: On November the 16th I'll present "Oracle Database Cloud Performance" where I talk about my experience regarding the performance of the Oracle DBaaS offerings. Note that some printed conference schedule material is outdated, I'll speak at 3pm, not 10am

- IT Tage 2016: On December 13th I'll present "Oracle 12c - Cost Based Optimizer Grundlagen" which describes the basics of the Cost Based Optimizer with an update how things might be different with Oracle 12c

Hope to see you at some of these events - if you're going to be there, say Hello!

oracle real time apply on standby

Learn DB Concepts with me... - Sun, 2016-10-09 11:47
 
                   Oracle Real Time Apply on Standby


By default, log apply services wait for the full archived redo log file to arrive on the standby database before applying it to the standby database. If the real-time apply feature is enabled, log apply services can apply redo data as it is received from the Primary DB, without waiting for the current standby redo log file to be archived. We can use the ALTER DATABASE statement to enable the real-time apply feature, as below:

  • For physical standby databases, issue the ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE statement.

  • For logical standby databases, issue the ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE statement.

NOTE : Standby redo log files are required to use real-time apply.


Lets Test it:

oracle@ORCLSTDBY:[~] $ sqlplus /"as sysdba"

SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 4 10:57:52 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  IDLE

-- Dest_id can be different in your database. but mostly it will be set to local.
-- Lets start applying logs and start the recovery mode Default (apply on log fill up)

SQL> recover managed standby database disconnect from session;
Media recovery complete.

-- Query the Recovery Mode now:

SQL> col DEST_NAME format A20
col status format A10
col type format A10
col recovery_mode format A30

select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME            STATUS     TYPE       SRL RECOVERY_MODE
-------------------- ---------- ---------- --- ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL      NO  MANAGED

-- See that Recovery Mode will be just Managed. 

-- Lets stop log Apply and change it the recovery mode to Real-Time Apply

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> alter database recover managed standby database using current logfile disconnect from session;

Database altered.

SQL> select dest_name,status,type,srl,recovery_mode from v$archive_dest_status where dest_id=1;

DEST_NAME                       STATUS      TYPE       SRL        RECOVERY_MODE
--------------------                   ----------       ----------     ---          ------------------------------
LOG_ARCHIVE_DEST_1   VALID      LOCAL   NO  MANAGED REAL TIME APPLY


-- We can also check this in alertlog_File.log


Completed: ALTER DATABASE RECOVER  managed standby database cancel
Tue Oct 04 11:00:47 2016
.
.
alter database recover managed standby database using current logfile disconnect from session
Attempt to start background Managed Standby Recovery process (ORCLSTDBY)
Tue Oct 04 11:00:47 2016
MRP0 started with pid=58, OS id=40557
MRP0: Background Managed Standby Recovery process started (ORCLSTDBY)
 started logmerger process
Tue Oct 04 11:00:52 2016
Managed Standby Recovery starting Real Time Apply
Parallel Media Recovery started with 64 slaves
Waiting for all non-current ORLs to be archived...


Reference Oracle Docs:

https://docs.oracle.com/cd/B19306_01/server.102/b14239/log_apply.htm#i1034632


 Similar Posts :


Categories: DBA Blogs

How to patch Postgres Plus Advanced Server in a Standby configuration

Yann Neuhaus - Sun, 2016-10-09 09:57

In the last post we looked at how you can patch a Postgres Plus Advanced server. Wouldn’t it be nice, in a standby configuration, to patch the standby first without touching the master, then do a controlled switchover and finally patch the old master? In case this is a configuration with EDB Failover Manager the only downtime which would happen is the relocation of the VIP from one node to another (if you use a VIP). Without using a VIP but using pgpool-II the downtime is even less. Lets see if it works by starting from my usual EDB Failover Manager configuration.

This is the current status of my failover cluster:

[root@edbbart efm-2.1]# /usr/efm-2.1/bin/efm cluster-status efm
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.243       UP     UP        
	Standby     192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.245

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       
	Standby     192.168.22.245       0/4A000140       

	Standby database(s) in sync with master. It is safe to promote.

All is fine, I have one master, one standby and one witness. Going straight forward lets shutdown the standby (please notice that I have disabled auto failover):

Shutdown the standby database:

postgres@edbppasstandby:/home/postgres/ [pg950] pg_ctl -D /u02/pgdata/PGSITE2 stop -m fast
waiting for server to shut down.... done
server stopped

What happened to my cluster?

[root@edbbart efm-2.1]# /usr/efm-2.1/bin/efm cluster-status efm
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.243       UP     UP        
	Standby     192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.245

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       
	Unknown     192.168.22.245       UNKNOWN          Connection to 192.168.22.245:4445 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

	One or more standby databases are not in sync with the master database.
[root@edbbart efm-2.1]# 

Not really surprising EFM complains that the standby is not reachable anymore. Thats fine. Lets patch the standby:

postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] chmod +x postgresplusas-9.5.4.9-1-linux-x64.run
postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only true --prefix /u01/app/postgres/product/95/db_1/9.5AS/
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.

----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be 
installed.

Installation Directory [/u01/app/postgres/product/95/db_1/9.5AS]: 

----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your 
computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.

 Installing Database Server
 0% ______________ 50% ______________ 100%
 #########################################

----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.

postgres@edbppasstandby:/u01/app/postgres/software/ [pg950] 

… bring it up again:

postgres@edbppasstandby:/home/postgres/ [pg950] pg_ctl -D /u02/pgdata/PGSITE2 start
server starting

… checking the PostgreSQL log file all is fine, streaming restarted:

2016-10-05 11:35:25.745 GMT - 2 - 4984 -  - @ LOG:  entering standby mode
2016-10-05 11:35:25.751 GMT - 3 - 4984 -  - @ LOG:  consistent recovery state reached at 0/4A000108
2016-10-05 11:35:25.751 GMT - 4 - 4984 -  - @ LOG:  redo starts at 0/4A000108
2016-10-05 11:35:25.751 GMT - 5 - 4984 -  - @ LOG:  invalid record length at 0/4A000140
2016-10-05 11:35:25.751 GMT - 4 - 4982 -  - @ LOG:  database system is ready to accept read only connections
2016-10-05 11:35:25.755 GMT - 1 - 4988 -  - @ LOG:  started streaming WAL from primary at 0/4A000000 on timeline 8

What is the status of EFM?

postgres@edbppasstandby:/home/postgres/ [pg950] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.243       UP     UP        
	Idle        192.168.22.245       UP     UNKNOWN   

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	(List is empty.)

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       

	No standby databases were found.

Idle Node Status (idle nodes ignored in XLog location comparisons):

	Address              XLog Loc         Info
	--------------------------------------------------------------
	192.168.22.245       0/4A000140       DB is in recovery.

Status “Idle” for the standby which is fine, just resume:

postgres@edbppasstandby:/home/postgres/ [pg950] sudo /usr/efm-2.1/bin/efm resume efm
Resume command successful on local agent.
postgres@edbppasstandby:/home/postgres/ [pg950] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Witness     192.168.22.244       UP     N/A       
	Standby     192.168.22.245       UP     UP        
	Master      192.168.22.243       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.245

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.243       0/4A000140       
	Standby     192.168.22.245       0/4A000140       

	Standby database(s) in sync with master. It is safe to promote.

… and everything is back as it should be. Time to switchover:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] sudo /usr/efm-2.1/bin/efm promote efm -switchover
Promote/switchover command accepted by local agent. Proceeding with promotion and will reconfigure original master. Run the 'cluster-status' command for information about the new cluster state.

The master and the standby should have switched its roles:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Master      192.168.22.245       UP     UP        
	Witness     192.168.22.244       UP     N/A       
	Standby     192.168.22.243       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.243

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       
	Standby     192.168.22.243       0/4B0001A8       

	Standby database(s) in sync with master. It is safe to promote.
postgres@edbppasstandby:/home/postgres/ [PGSITE2] 

Same procedure again, stop the standby:

postgres@edbppas:/home/postgres/ [PGSITE1] pg_ctl -D /u02/pgdata/PGSITE1 stop -m fast
waiting for server to shut down.... done
server stopped
postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     192.168.22.243       UP     UP        
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.243

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       
	Unknown     192.168.22.243       UNKNOWN          Connection to 192.168.22.243:4445 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections.

	One or more standby databases are not in sync with the master database.

Apply the patch:

postgres@edbppas:/u01/app/postgres/software/ [PGSITE1] ./postgresplusas-9.5.4.9-1-linux-x64.run --extract-only true --prefix /u01/app/postgres/product/95/db_1/9.5AS/
Language Selection

Please select the installation language
[1] English - English
[2] Japanese - 日本語
[3] Simplified Chinese - 简体中文
[4] Traditional Chinese - 繁体中文
[5] Korean - 한국어
Please choose an option [1] : 1
----------------------------------------------------------------------------
Welcome to the Postgres Plus Advanced Server Setup Wizard.

----------------------------------------------------------------------------
Please specify the directory where Postgres Plus Advanced Server will be 
installed.

Installation Directory [/u01/app/postgres/product/95/db_1/9.5AS]: 

----------------------------------------------------------------------------
Setup is now ready to begin installing Postgres Plus Advanced Server on your 
computer.

Do you want to continue? [Y/n]: y

----------------------------------------------------------------------------
Please wait while Setup installs Postgres Plus Advanced Server on your computer.

 Installing Database Server
 0% ______________ 50% ______________ 100%
 #########################################

----------------------------------------------------------------------------
Setup has finished installing Postgres Plus Advanced Server on your computer.

Startup again:

postgres@edbppas:/u01/app/postgres/software/ [PGSITE1] pg_ctl -D /u02/pgdata/PGSITE1 start
server starting

Streaming restarted:

2016-10-05 11:45:36.807 GMT - 2 - 4883 -  - @ LOG:  entering standby mode
2016-10-05 11:45:36.810 GMT - 3 - 4883 -  - @ LOG:  consistent recovery state reached at 0/4B0000C8
2016-10-05 11:45:36.810 GMT - 4 - 4883 -  - @ LOG:  redo starts at 0/4B0000C8
2016-10-05 11:45:36.810 GMT - 5 - 4883 -  - @ LOG:  invalid record length at 0/4B0001A8
2016-10-05 11:45:36.810 GMT - 4 - 4881 -  - @ LOG:  database system is ready to accept read only connections
2016-10-05 11:45:36.815 GMT - 1 - 4887 -  - @ LOG:  started streaming WAL from primary at 0/4B000000 on timeline 9

Same status “Idle” as before:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Idle        192.168.22.243       UP     UNKNOWN   
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	(List is empty.)

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       

	No standby databases were found.

Idle Node Status (idle nodes ignored in XLog location comparisons):

	Address              XLog Loc         Info
	--------------------------------------------------------------
	192.168.22.243       0/4B0001A8       DB is in recovery.

Resume:

postgres@edbppas:/home/postgres/ [PGSITE1] sudo /usr/efm-2.1/bin/efm resume efm
Resume command successful on local agent.

Fully back:

postgres@edbppasstandby:/home/postgres/ [PGSITE2] efmstat
Cluster Status: efm
VIP: 192.168.22.250
Automatic failover is disabled.

	Agent Type  Address              Agent  DB       Info
	--------------------------------------------------------------
	Standby     192.168.22.243       UP     UP        
	Witness     192.168.22.244       UP     N/A       
	Master      192.168.22.245       UP     UP        

Allowed node host list:
	192.168.22.244 192.168.22.245 192.168.22.243

Membership coordinator: 192.168.22.245

Standby priority host list:
	192.168.22.243

Promote Status:

	DB Type     Address              XLog Loc         Info
	--------------------------------------------------------------
	Master      192.168.22.245       0/4B0001A8       
	Standby     192.168.22.243       0/4B0001A8       

	Standby database(s) in sync with master. It is safe to promote.

Works like a charm. The organizational overhead is much more than what you actually need to do. Technically this is a task of a few minutes.

 

Cet article How to patch Postgres Plus Advanced Server in a Standby configuration est apparu en premier sur Blog dbi services.

reports (cvucheckreport_*.xml) from ora.cvu resource causing excessive disk space

Syed Jaffar - Sun, 2016-10-09 06:34
On Exadata dbnodes, we noticed that Clusterware 12.1.0.2 ora.cvu resource generating XML reports every 6 hours and occupied large amounts of space on /u01 filesystem. Perhaps this is the same on non-Exadata systems too, if you are running 12.1.0.2 Clusterware.

Upon looking into MOS ID (reports from ora.cvu resource using excessive disk space (Doc ID 1964482.1) it was learned that this is an expected behavior with 12.1.0.2 where ora.cvu invokes Clusterware Health check (CHC) every 6 hrs and stores the XML reports under $GI_HOME/cvu/baseline or $ORACLE_BASE/crsdata/@global/cvu/baseline' location. However, the CVU didn't clean-up the reports periodically which ultimately causes  disk full.

As per the note, the bug is fixed as follows:

Bug 18143707 is fixed in 12.1.0.2
Bug 19703199 is fixed in 12.1.0.2.4 PSU
Bug 20177779 is fixed in 12.1.0.2.5 PSU

Quick workaround to this problem is to delete the files (xml) manually in order to release the space.

References:

reports from ora.cvu resource using excessive disk space (Doc ID 1964482.1)
ora.cvu Resource Reports not Purged (Doc ID 2054765.1)
12.1.0.2 Grid Infrastructure Patch Set Updates - List of Fixes in each GI PSU (Doc ID 1928853.1)

sql query related question

Tom Kyte - Sun, 2016-10-09 05:26
i want to compare this month data(ex:sep-2016) with previous year month data(ex:sep-2015) in same page of SQL PLUS by using date column(i.e.DAL_ARR_DATE_TIME) of my table,so please give me a solution for the same.thanks in advance. my table structur...
Categories: DBA Blogs

Implicit rollback on error in NDS and dbms_sql

Tom Kyte - Sun, 2016-10-09 05:26
Good day. Could you please explain to me why the following script returns the next output? <code> in foo_proc Static call - 1 in foo_proc NDS - 0 in foo_proc dbms_sql - 0 </code> Why the inserted row is implicitly rollbacked before th...
Categories: DBA Blogs

Physical Standby database - user sync

Tom Kyte - Sun, 2016-10-09 05:26
Will new users created on the primary database be synced to the standby database automatically?
Categories: DBA Blogs

non-cdb architecture on 12c

Tom Kyte - Sun, 2016-10-09 05:26
Tom. If we upgrade our 11g database to 12c 12.1.0.2, can we still use the non-cdb model? With 11g going into extended support very soon we would like to upgrade to 12c however, we are not familiar with PDBs yet so we would prefer to mo...
Categories: DBA Blogs

ORA-01156 When Adding Standby Redo Log in Dataguard Configuration

Pakistan's First Oracle Blog - Sat, 2016-10-08 20:20
Database version = 11.2.0.3.0

If you get following error while adding a Standby Redo Log (SRL) to a standby database in dataguard configuration:

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;
alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M
*
ERROR at line 1:
ORA-01156: recovery or flashback in progress may need access to files


Then it means that first you have to stop the redo apply, add the SRL and then start the redo apply. Best way to do is from dgmgrl like this:

DGMGRL> connect /
Connected.

DGMGRL> edit database 'test' set state='APPLY-OFF';
Succeeded.

SQL> alter database add standby logfile group 3 '/logs/test/sbyredo3a.log' size 500M;

Database altered.

DGMGRL> edit database 'CONPRO_UK' set state='APPLY-ON';
Succeeded.

HTH.











Categories: DBA Blogs

POUG

Rittman Mead Consulting - Sat, 2016-10-08 13:20

I've just attended my first user group in Poland, the very-excellent POUG. This was the first international version of the conference, having been run previously just within Poland. Based on these two days, I would say it was a resounding success! There was a great atmosphere there, really engaged and enthusiastic. The size of the event, friendliness, and fun made it a very welcoming one, and reminded me a lot of my first ever conference that I attended (back in 2010!), the Rittman Mead BI Forum.

I travelled out to Warsaw on the Thursday, and attended the speakers dinner. It's always nice to see familiar faces and meet new ones - as well as enjoy some jolly good food. The next morning I went for a run at the nearby park, enjoying the sunrise over the water

Good morning Warsaw! #POUG https://t.co/aHAuYonlhE pic.twitter.com/LYgU50bIei

— Robin Moffatt (@rmoff) October 7, 2016

The conference had two tracks, focussing primarily on the database but with some BI content too, as well a talk about property graphs. The first session I attended was also one of my favourites. It was "DBA, Heal Thyself: Five Diseases of IT Organizations and How to Cure Them", presented with great humour and energy by Jim Czuprynski. Some of the uncomfortable truths about mistakes made in the field of IT were laid bare, with no prisoners taken! Whilst it was based on the database, much of it was applicable to OBIEE. Things like reliance on bespoke scripts, avoidance of new features, and general ignorance in the field were covered. You can find an article on the topic from Jim here.

After Jim's session was Hans Viehmann talking about Property Graphs. I wrote an article that was published on OTN just last week about this and it was great to get chance to meet Hans, and hear him speak.

I skipped the next session to do a bit of slide polishing and geek chat with Christian Berg, before getting lunch - which was really good:

Just a light lunch at #POUG pic.twitter.com/B3Bntf1JCU

— Robin Moffatt (@rmoff) October 7, 2016

After lunch was OBIEE presentations in both tracks, by Christian Berg, and Kiran Taylor. I sat in on Christian's "Fifty shades of #fail", which is a fun walk through some of the many ways that OBIEE gets done wrong.

My talk, "(Still) No Silver Bullets - OBIEE 12c Performance in the Real World" was the last of the day. I've given this talk quite a few times now, but still enjoy delivering it each time. The topic's one I've spent so much time working on and find so interesting, that it never gets stale! You can find the slides here and set of related links here.

.@rmoff - OBIEE performance in the real world - where is it slow? - #POUG pic.twitter.com/IanRoHBX2G

— Kiran Tailor (@KiranTailorUK) October 7, 2016

The day finished with the POUG After Party, which was at a bar in the center of Warsaw. Good beer, good food, good music - and plenty of geek talk! I really have to take my hat off to the organisers of POUG, they did a great job.

#POUG #bagpipes pic.twitter.com/ZC8WGwq8Z6

— Robin Moffatt (@rmoff) October 7, 2016

The second day of POUG brought more good presentations. I got to see Neil Chandler speak, about execution plans and how they can vary - and how to ensure they don't unless you want them too. It was really interesting, and took me back a few years to when I last looked in-depth at this kind of stuff. After Neil was Jim again, talking about analytic functions. Most of these I knew about, but one that was new to me (and I'll definitely be using) was the PERCENT syntax for FETCH FIRST - very neat.

Great stuff from @JimTheWhyGuy at #POUG. FETCH FIRST I knew… but now I know you can use PERCENT with it too. Cool! pic.twitter.com/f10yoz8yge

— Robin Moffatt (@rmoff) October 8, 2016

The audience at POUG seemed to be predominantly DBAs and database developers, and this kind of talk is just great for spreading awareness of new functionality that is going to make people's jobs easier, and their code run faster. The final talk of the morning was from Martin Widlake, presenting a great discussion about efficient and maintainable bulk processing with SQL and PL/SQL. With his very accessible and engaging presentation style, Martin's talk was an extremely pragmatic and valuable one. Everyone loves a deep-dive geekout on system internals (don't they??), but arguably the most value to the widest section of the audience comes in learning, or being reminded of, how to code and design systems well.

Even without lots of BI content, I found the conference very useful. Whilst Oracle CBO internals may not be my day to day work, many of the topics discussed in a database context can easily be transplanted to the BI world. Performance is performance. Diagnostic approaches are tool-agnostic. As well as the presentations, the opportunity to exchange ideas and war-stories with other experts in the industry (over a beer, usually…) is the kind of thing you just don't get from reading the manuals or a bunch of PDFs.

So that was POUG, and all too soon time return home. Bravo to the organisers of POUG, and here's hoping they run the conference again next year!

Well that was it for #poug. So long @POUG_ORG and thanks for all the fish! pic.twitter.com/mv9Es95yfr

— Christian Berg (@Nephentur) October 8, 2016
Categories: BI & Warehousing

How to speed up an Insert with SELECT

Tom Kyte - Sat, 2016-10-08 11:06
Hi Tom, good morning Sir. I am trying to speed up an Insert statement where the SELECT is selecting all the source table according to this example: var_select_a_insert := 'insert into lwm_usrappods.ITEM_LOC_SOH select to_number...
Categories: DBA Blogs

High number of misses in library cache during execute - where to dig for explanations?

Tom Kyte - Sat, 2016-10-08 11:06
I'm relatively familiar with using SQL_TRACE and tkprof for tuning, but I recently spotted something in a tkprof report for one of our customers that raised my suspicions. There is a lot of SQL in the file where the number of library cache executi...
Categories: DBA Blogs

Session wait time of a transaction

Tom Kyte - Sat, 2016-10-08 11:06
Dear Tom, please help me with the below. Session 1: update table set field = 'A' where field2 = 'B'; it is not committed/rollbacked Session 2: update table set field = 'C' where field2 = 'B'; The session will be waiting for sessio...
Categories: DBA Blogs

How to observe current values of a running plsql code.

Tom Kyte - Sat, 2016-10-08 11:06
Hello, I can observe the current value of a bind variable, using v$sql_bind_capture. I don't know how to do it, when having no bind variable. Having a simple code like this below, I would like to see a VALUE of rec.column1 being passed to my_functi...
Categories: DBA Blogs

BULK Delete

Tom Kyte - Sat, 2016-10-08 11:06
Hi Tom, This is my first question to you and hope to receive a positive response :) Straight to the Question: I have a table named trail_log. This is a huge table and a daily growth of this table is 12GB Approx. Currently 76216160 rows. Import...
Categories: DBA Blogs

How to determine if db objects are still used?

Tom Kyte - Sat, 2016-10-08 11:06
I am working on a database that has nearly 3,900 tables, and 450 views, 90 packages, 740 procedures, and 325 functions. Some of those are called by apps and report servers that I do not have access to all the source code. I have a feeling that m...
Categories: DBA Blogs

UNDO tablespace usage

Tom Kyte - Sat, 2016-10-08 11:06
Hi Team, I want a customized query which should give me which SQL statement is taking more amount of undo tablespace size with all the details. Request you to please help me on this. Regards, Sridhar
Categories: DBA Blogs

Copy millions of blobs from one table to the other one

Tom Kyte - Sat, 2016-10-08 11:06
Hello colleagues, I've got a "small" problem, I have to copy millions of blobs from one table to another one. <b>Target</b> table: PAGE ---------------------- ID NOT NULL NUMBER(19) DOCUPLOADCODE VARCHAR2(255) MIMETYPE ...
Categories: DBA Blogs

macOS Sierra (OS X 10.12)

Tim Hall - Sat, 2016-10-08 08:15

In the 80s and 90s the Sierra was an incredibly popular, but boring saloon car from Ford. Today I upgraded to macOS Sierra, which will eventually be an incredibly popular (for Mac owners), but boring operating system from Apple.

So what’s new? You get Siri! Oh yes, you also get Siri! There is also Siri! And finally, for good measure you get Siri!

If you own multiple devices, there is some fluff, like shared clipboard, but I don’t own multiple Apple devices, so basically this OS is what I had before with a sprinkling of Siri.

What was the experience of upgrading like? An absolute nightmare! It said it was going to take about 18 minutes, but it seemed to hang for a couple of hours. I ended up doing about 5 hard reboots before it actually came up. After all that hassle and wasted time, I got El Crapitan + Siri. Amazing. Way to innovate Apple!

Of course, Apple fanboys will love it and it will change their lives… Whatever!

Cheers

Tim…

macOS Sierra (OS X 10.12) was first posted on October 8, 2016 at 2:15 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Pages

Subscribe to Oracle FAQ aggregator