DBA Blogs

Connecting to a pluggable database -12c

Learn oracle 12c database management - Thu, 2016-03-03 10:05
******* ********** *********
USING EZCONNECT:
******* ********** *********

[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxxx@localhost:1521/pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:12:17 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

******** ********* *********
USING TNSNAMES:
******** ********* *********

Create a tnsnames entry for the pdb that you want to connect

[oracle@Linux03 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

pdborcl1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Linux03)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
 



[oracle@Linux03 ~]$ sqlplus pdborcl1/xxxxx@pdborcl1

SQL*Plus: Release 12.1.0.2.0 Production on Thu Mar 3 09:15:10 2016

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SHOW CON_NAME

CON_NAME
------------------------------
PDBORCL1

Categories: DBA Blogs

MySQL on FreeBSD: old genes

Pythian Group - Thu, 2016-03-03 10:02

Maintaining mission critical databases on our pitchfork wielding brother, the “Daemon” of FreeBSD, seems quite daunting, or even absurd, from the perspective of a die-hard Linux expert, or from someone who has not touched it in a long time. The question we ask when we see FreeBSD these days is “why?”.  Most of my own experience with FreeBSD was obtained 10-15 years ago.  Back then, in the view of the team I was working on, a custom compiled-from-source operating system like FreeBSD 5.x or 6.x was superior to a Linux binary release.

Package managers like YUM and APT were not as good.  They did not always perform MD5 checks and use SSL like today’s versions. RedHat wasn’t releasing security updates 5 minutes after a vulnerability was discovered. Ubuntu didn’t exist. Debian stable would get so very old before receiving a new version upgrade. FreeBSD was a great choice for a maintainable, secure, free open source UNIX-like OS with tight source control and frequent updates.

Most people do not understand why FreeBSD remains a great choice for security and stability. The main reason is that the entire source of the base OS and the kernel (not just the kernel) are tightly maintained and tested as a whole, monolithic, distribution.

FreeBSD 10.2 is different than versions I worked on many years ago, in a good way, at least from the standpoint of getting started. First, “pkg” has gotten quite an overhaul, making installing packages on FreeBSD as easy as with YUM or APT.  portsnap and portmaster make port upgrades much easier than they used to be. freebsd-update can take care of wholesale updates of the operating system from trusted binary sources without having to “build the world”. These are welcome changes; ones that make it easier to get to production with FreeBSD, and certainly made the task of rapidly building and updating a couple of “lab” virtual machines easier.

In my effort to get re-acquainted with FreeBSD, I hit some snags. However, once I was finished with this exercise, FreeBSD had re-established itself in my mind as a decent flavor to host a mission critical database on. Open Source enthusiasts should consider embracing it without (much) hesitation. Is there some unfamiliar territory for those who only use MySQL on MacOS and Linux? Sure. But it is important to remember that BSD is one of the oldest UNIX like operating systems. The OSS world owes much heritage to it. It is quite stable and boring, perhaps even comfortable in its own way.

Problem 1: forcing older versions of MySQL

I needed to install MySQL 5.5 first, in order to test a mysql upgrade on FreeBSD.  However, when installing percona-toolkit either via “pkg install” (binary) or /usr/ports (source), the later 5.6 version of the mysql client would inevitably be installed as a dependency. After that point, anything relating to MySQL 5.5 would conflict with the 5.6 client. If I installed in the opposite order, server first, percona-toolkit second, the percona-toolkit installation would ask me if it is OK to go ahead and upgrade both server and client to 5.6.

TIP: don’t forget make.conf

my /etc/make.conf:
MYSQL_DEFAULT?= 5.5

Once I added MYSQL_DEFAULT into make.conf, the installations for MySQL 5.5 became seamless. Note: if you want another flavor of MySQL server such as Percona Server, install the server “pkg install percona55-server” prior to “pkg install percona-toolkit” so that the client dependencies are met prior to installation.

Problem 2: Some tools don’t work

pt-diskstats does not work, because it reads from /proc/diskstats, which does not exist on FreeBSD. Other favorites like htop don’t work right out of the box. So far I have had good luck with the rest of the Percona toolkit besides pt-diskstats, but here’s how you get around the htop issue (and perhaps others).

TIP: Get the linux /proc mounted

dynamic commands:
# kldload linux
# mkdir -p /compat/linux/proc
# mount -t linprocfs linproc /compat/linux/proc

to make permanent:
# vi /boot/loader.conf (and add the following line)
linux_load="YES"
# vi /etc/fstab (and add the following line)
linproc /compat/linux/proc linprocfs rw 0 0

As you may have determined, these commands ensure that the linux compatibility kernel module is loaded into the kernel, and that the linux style /proc is mounted in a different location than you might be used to “/compat/linux/proc”. The FreeBSD /proc may also be mounted.

Problem 3: I want bash

# pkg install bash
… and once that’s done
# pw user mod root -s /usr/local/bin/bash
…and repeat ^^ for each user you would like to switch. It even comes with a prompt that looks like CentOS/RHEL.
[root@js-bsd1 ~]#

Problem 4: I can’t find stuff

BSD init is much simpler than SysV and upstart init frameworks so your typical places to look for start files are /etc/rc.d and /usr/local/etc/rc.d. To make things start on boot, it’s inevitably a line in /etc/rc.conf.

In our case, for MySQL, our start file is /usr/local/etc/rc.d/mysql-server. To have MySQL start on boot, your rc.conf line is:
mysql_enable="YES".

If you do not wish to make MySQL start on boot, you may simply say "/usr/local/etc/rc.d/mysql-server onestart"

Notes on binary replacement

Please note, just like in the Linux world, MariaDB and Percona Server are drop in replacements for MySQL so, the startfiles and enable syntax does not change. Your default location for my.cnf is /etc/my.cnf just like in the rest of the known universe.

This command lists all installed packages.
pkg info -a

use pkg remove and pkg install to add new versions of your favorite mysql software.

I ran into no greater issues with pkg than I would with yum or apt doing binary removals and installations, and no issues at all with mysql_upgrade. Remember: If you had to alter make.conf like I did earlier, remember to update it to reflect versions you want to install.

For those who like ZFS, the FreeBSD handbook has a very detailed chapter on this topic. I for one like plain old UFS. It might be the oldest filesytem that supports snapshots and can be implemented very simplistically for those who like low overhead.

Happy tinkering with FreeBSD and MySQL, and thanks for reading!

Categories: DBA Blogs

Sydney Gets New AWS Availability Zone

Pythian Group - Thu, 2016-03-03 09:47

On a scorching November day in 2012, Sydneysiders were bracing themselves for yet another heat wave when all of a sudden they became pleasantly surprised as an elastic cloud occupied the tech skies. On November 12, 2012, Amazon announced  the New Asia Pacific (Sydney) Region in Australia.

Before that, Australian customers had to reach out to Japan or Singapore for their cloud needs. That was not really feasible, as it increased up-front expenses, long-term commitments, and scaling challenges. Amazon recognized that and Sydney became another region in the world.

They have now taken it a step further. They have rendered a new Availability Zone (AZ) in Sydney. Availability zone (AZ) is basically an isolated location within data centre regions from which public cloud services originate and operate.

The new availability zone is ap-southeast-2c. This is all set to provide enhanced performance and sociability to Australian customers. This will enable them to fully leverage the potential of technologies like Lambda, the Elastic File System shared filesystem, and Amazon RDS for MS SQL Server.

Pythian’s established presence in Australia and New Zealand coupled with round the clock and world class support for AWS, SQL Server, and other cloud technologies, enables it to support Australian and New Zealand customers from the word go.

Categories: DBA Blogs

Links for 2016-03-02 [del.icio.us]

Categories: DBA Blogs

SQL Injection with MySQL SLEEP()

Pythian Group - Wed, 2016-03-02 11:40

Recently we’ve received an alert from one of our clients that running threads are high on one of their servers. Once we logged in, we noticed that all the selects were waiting for table level read lock. We scrolled through the process list, and found the selects which were causing the problems. After killing it, everything went back to normal.
At first we couldn’t understand why the query took so long, as it looked like all the others. Then we noticed, that one of the WHERE clauses was strange. There, we found a SLEEP(3) attached with OR to the query. Obviously, this server was the victim of a SQL injection attack.

What is SQL injection?

I think most of us know what SQL injection is, but as a refresher, SQL injection is when someone provides malicious input into WHERE, to run their own statements as well.
Typically this occurs when you ask a user for input, like username, but instead of a real name they give you a MySQL statement that will be run by your server without you knowing it.
Exploits of a Mom
Based on the picture, let’s see a few examples.
We have a simple table:

mysql> describe post;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| test  | varchar(127)     | YES  |     | NULL    |                |
+-------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

mysql> select * from post;
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set (0.00 sec)

Lets run a select with LIKE, which we know for sure won’t have a match:

mysql> select * from post where test like '%nomatch%';
Empty set (0.00 sec)

But what, happens if we don’t filter the inputs and someone wants to get all the data?
mysql> select * from post where test like '%nomatch ' || '1==1' && '1%';
+----+--------+
| id | test   |
+----+--------+
|  1 | text1  |
|  2 | text2  |
|  3 | text3  |
|  4 | text4  |
|  5 | text5  |
|  6 | text6  |
|  7 | text7  |
|  8 | text8  |
|  9 | text9  |
| 10 | text10 |
+----+--------+
10 rows in set, 2 warnings (0.00 sec)

That was a very mild injection, but it could be much more malicious: we could drop another table!

mysql> show tables;
+----------------------+
| Tables_in_injecttest |
+----------------------+
| game                 |
| post                 |
+----------------------+
2 rows in set (0.01 sec)

mysql> select * from post where test like '%nomatch'; drop table game;-- %';
Empty set (0.00 sec)

Query OK, 0 rows affected (0.28 sec)

mysql> show tables;
+-----------------------+
| Tables_in_inject_test |
+-----------------------+
| post                  |
+-----------------------+
1 row in set (0.00 sec)

mysql>

If we don’t know the name of the table, we can still cause trouble by blocking access to the database
If we insert SLEEP() in the WHERE part, then it will be executed for every matching row… if we inject it like: “OR SLEEP(n)”, it will be executed to every row in the table!
Okay, this will be “just” a long running select. It shouldn’t cause much trouble thanks to InnoDB and transaction isolation, unless something needs a table lock.

Some common examples of what causes table locks are:

  • explicit lock table
  • insert/update/delete on MyISAM
  • ALTER table on InnoDB

Once statements start waiting for lock on the table, all proceeding selects will wait for the previous locking statement to finish

Terminal 1:
mysql> select * from post where test like '%nomatch ' OR sleep(300) AND '1%';
….
Terminal 2:
mysql> alter table post engine=innodb;
...
Terminal 3:
mysql> select SQL_NO_CACHE count(*) from post;
…
Processlist:
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| Id       | User                 | Host      | db                 | Command | Time  | State                           | Info                                                                  |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
| 17170817 | root                 | localhost | janitest           | Query   |    19 | User sleep                      | select * from post where test like '%nomatch ' OR sleep(300) AND '1%' |
| 17170918 | root                 | localhost | janitest           | Query   |    11 | Waiting for table metadata lock | alter table post engine=innodb                                        |
| 17170957 | root                 | localhost | janitest           | Query   |     4 | Waiting for table metadata lock | select * from post                                                    |
+----------+----------------------+-----------+--------------------+---------+-------+---------------------------------+-----------------------------------------------------------------------+
3 rows in set (0.00 sec)

As we see in the example, ALTER table will wait until it can get a lock on post table, and this blocks every other select from now on to the table.
Or, if you are using MyISAM table, a simple update/insert will block access to the table, because it needs table level lock during them.

How can we defend ourselves from SQL injection?

There are several ways to secure yourself from SQL injection.

  • First of all, validate the input. If you expect only letters and numbers, filter it with regexp for example, to make sure there are no special characters there. Also escape the inputs on application side; programming languages have built-in function to do that (eg.: mysql_real_escape_string() in PHP)
  • Use prepared statement! It won’t allow 2 clause if you specified only 1. When you use prepared statements, the variables are transmitted as MySQL variables. Even if the string is not escaped, it will end up in one variable, and MySQL treats is as a longer string.
    (For more details see: http://dev.mysql.com/doc/refman/5.7/en/sql-syntax-prepared-statements.html )
  • Use a tool like MySQL Enterprise Firewall, which is a plugin for MySQL and can filter your statements to make sure there are no things like: || 1==1

I would like to start a little talk about this, so if you encountered SQL injection before, would you share it with us, how they did it, or in general how do you prevent SQL injections in your application?

 

Categories: DBA Blogs

prvf-0002 : could not retrieve local node name

Learn DB Concepts with me... - Wed, 2016-03-02 09:43

prvf-0002 : could not retrieve local node name



PRVF-0002 : could not retrieve local node name
check if the hostname is correct in sysconfig/network & /etc/hosts files:
[oracle@Linux03 ~]$ cat /etc/sysconfig/network | grep HOSTNAME
HOSTNAME=Linux03

[oracle@Linux03 ~]$ cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

[oracle@Linux03 ~]$ vi /etc/hosts

[oracle@Linux03 ~]$ su root
Password:
[root@Linux03 oracle]# vi /etc/hosts

[root@Linux03 oracle]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.215.xxx.xx Linux03   <<<--- added hostname and ip


An alternate solution is to add the hostname in bash_profile file in home dir:
[oracle@Linux03 ~]$ cat .bash_profile | grep HOSTNAME
export ORACLE_HOSTNAME=Linux03
Categories: DBA Blogs

Oracle Infrastructure Cloud Partner Briefings

Oracle Infrastructure Cloud  Webcasts - Partner Briefings ...

We share our skills to maximize your revenue!
Categories: DBA Blogs

Log Buffer #463: A Carnival of the Vanities for DBAs

Pythian Group - Tue, 2016-03-01 15:27

As the winter in the Northern hemisphere is giving way to spring, slowly but surely, blog posts are blooming in the gardens of Oracle, SQL Server and MySQL. This Log Buffer plucks some of them for your reading pleasure.

Oracle:

Providing A Persistent Data Volume to EMC XtremIO Using ClusterHQ Flocker, Docker And Marathon

There is sliced bread in SQL.

Oracle Cloud – Your service is suspended due to exceeding resource quota !

EM12c Compliance ‘Required Data Available’ flag – Understanding and Troubleshooting

How can I see my invisible columns

SQL Server:

Auto Generate Your Database Documentation

A Lightweight, Self-adjusting, Baseline-less Data Monitor

Keeping POST and GET Separated

How often should I run DBCC CHECKDB?

Disabling SQL Server Optimizer Rules with QUERYRULEOFF

MySQL:

MySQL Contributions status

Planets9s: Building scalable database infrastructures with MariaDB & HAProxy

High availability with asynchronous replication… and transparent R/W split

mysql_real_connect is not thread safe

Now available in swanhart-tools: NATIVE asynchronous query execution for any MySQL client!

Categories: DBA Blogs

Links for 2016-02-28 [del.icio.us]

Categories: DBA Blogs

delete noprompt obsolete archive log - RMAN

Learn DB Concepts with me... - Thu, 2016-02-25 10:25

RMAN> report obsolete;

using target database control file instead of recovery catalog
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc
Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc
Archive Log          191    16-FEB-16        

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          183    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc
Archive Log          189    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc
Archive Log          190    16-FEB-16          /u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc
Archive Log          191    16-FEB-16          
    
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_140_896707677.arc RECID=183 STAMP=903955885
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_141_896707677.arc RECID=189 STAMP=903956192
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_145_896707677.arc RECID=190 STAMP=903956192
deleted archived log
archived log file name=/u01/app/oracle/oraarch/ORCLSTB1_1_148_896707677.arc RECID=191 STAMP=903956192

Deleted 4 objects

Categories: DBA Blogs

Restore and recover database from RMAN backup - DB in No archive log mode

Learn DB Concepts with me... - Thu, 2016-02-25 10:19
Simple example for restore and recover database from RMAN backup.

Assuming that : I have a need to restore my DB as some important tables/data was dropped/removed and its really mission critical. I have already got some RMAN backups from past.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 1728053248 bytes
Fixed Size                  3046512 bytes
Variable Size            1224737680 bytes
Database Buffers          486539264 bytes
Redo Buffers               13729792 bytes
Database mounted.

C:\Users\oracle>rman target /

Recovery Manager: Release 12.1.0.2.0 - Production on Fri Feb 19 12:46:12 2016

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLDB (DBID=1196XXXX70)

RMAN> list backup summary;

using target database control file instead of recovery catalog

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
20      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T155541
21      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
22      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
23      B  F  A DISK        05-FEB-15       1       1       YES        FULL_ORCLDB
24      B  F  A DISK        05-FEB-15       1       1       NO         TAG20160205T160507


RMAN> restore database from TAG='FULL_ORCLDB';

Starting restore at 19-FEB-15
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00003 to E:\APP\ORADATA\ORCLDB\UNDOTBS01.DBF
:
:
:
channel ORA_DISK_1: restoring datafile 00119 to E:\APP\ORADATA\TLAPP.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0UQT63BQ_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:03:15
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
:
:
:
channel ORA_DISK_1: restoring datafile 00120 to E:\APP\ORADATA\TLLARGE.DBF
channel ORA_DISK_1: restoring datafile 00121 to E:\APP\ORADATA\TLWORK.DBF
channel ORA_DISK_1: restoring datafile 00122 to E:\APP\ORADATA\WAAPP.DBF
channel ORA_DISK_1: restoring datafile 00123 to E:\APP\ORADATA\ORCLDB\PSMATVW.DBF
channel ORA_DISK_1: reading from backup piece E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1
channel ORA_DISK_1: piece handle=E:\APP\BACKUP\ORCLDB_BKP_0VQT63ER_1_1 tag=FULL_ORCLDB
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:36
Finished restore at 19-FEB-15

Now lets try opening DB (I know it wont work but lets try..)

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: 'E:\APP\ORADATA\ORCLDB\SYSTEM01.DBF'

Now lets Recover DB

RMAN> recover database;

Starting recover at 19-FEB-15
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 3390 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO03.LOG
archived log for thread 1 with sequence 3391 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO01.LOG
archived log for thread 1 with sequence 3392 is already on disk as file E:\APP\ORADATA\ORCLDB\REDO02.LOG
RMAN-08187: WARNING: media recovery until SCN 69107847 complete
Finished recover at 19-FEB-15



SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


SQL>


We must open the database in open resetlogs mode. in order for recover to be completed. (WE CAN DO OPEN RESET LOGS FROM RMAN or SQLPLUS PROMPT)
RMAN> alter database open resetlogs;

Statement processed

RMAN> exit


Recovery Manager complete.

C:\Users\oracle>
Categories: DBA Blogs

database switch over using dgmgrl

Learn DB Concepts with me... - Thu, 2016-02-25 09:59
Perform a switch over test:


ON PRIMARY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCL      PRIM

ON STANDBY DB SERVER :

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCLSTB1      PHYSICAL STANDBY


LETS CONNECT TO DGMGRL AND SWITCH OVER ROLES

DGMGRL> switchover to 'ORCLSTB1';
Performing switchover NOW, please wait...
Operation requires a connection to instance "ORCLSTB1" on database "ORCLSTB1"
Connecting to instance "ORCLSTB1"...
Connected.
New primary database "ORCLSTB1" is opening...
Operation requires startup of instance "ORCLPRIM" on database "ORCL"
Starting instance "ORCLPRIM"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
start up and mount instance "ORCLPRIM" of database "ORCL"


ON OLD PRIMARY DB SERVER (ORCL) :

I have to start it manually coz dgmgrl was unable to connect to lsnr after role transfer.

SQL> startup mount;
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size    2258320 bytes
Variable Size  566233712 bytes
Database Buffers  310378496 bytes
Redo Buffers    6340608 bytes
Database mounted

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  MOUNTED       ORCL      PHYSICAL STANDBY


LETS CONFIRM THE DB_ROLE BY QUERYING STANDBY DATABASE

SQL> select name,open_mode,db_unique_name ,DATABASE_ROLE from v$database;

NAME  OPEN_MODE       DB_UNIQUE_NAME      DATABASE_ROLE
--------- -------------------- ------------------------------ ----------------
ORCL  READ WRITE       ORCLSTB1      PRIMARY

Categories: DBA Blogs

Relocating OCR File and Voting Disk to new Diskgroup

VitalSoftTech - Tue, 2016-02-23 00:11
Are you moving you OCR and Voting disks from one storage to another? Read the step-by-step instructions on how to to move these files to a different diskgroup.
Categories: DBA Blogs

New Round of Let’s Talk Database Events

Richard Foote - Thu, 2016-02-11 18:45
I’ll be presenting a new round of “Let’s Talk Database” events around Australia and NZ next month. These are free events but have often “sold out” in the past so booking early is recommended to avoid disappointment. All events run between 9:00am – 12:30pm and are followed by a networking lunch. Currently, the confirmed events are: Sydney – Tuesday, […]
Categories: DBA Blogs

Opatchauto Session failed: Parameter validation failed

Pakistan's First Oracle Blog - Wed, 2016-02-10 20:12
While applying PSU on Grid Home in 12c, due to the patch conflict, you might have to rollback few patches before you could apply the PSU.

After rolling back the patches from grid home, when you try to run the opatch analyze command again, you might encounter following error:





[root ~]# $Grid_Home/OPatch/opatchauto apply /stage/PSUpatch/22191349 -analyze -ocmrf /stage/ocm.rsp

OPatch Automation Tool

Copyright (c)2014, Oracle Corporation. All rights reserved.
OPatchauto Version : 12.1.0.1.10
OUI Version        : 12.1.0.2.0
Running from       : $Grid_Home

opatchauto log file: $Grid_Home/cfgtoollogs/opatchauto/22191349/opatch_gianalyze.log
NOTE: opatchauto is running in ANALYZE mode. There will be no change to your system.
OCM RSP file has been ignored in analyze mode. 

Clusterware is either not running or not configured. You have the following 2 options:
1. Configure and start the Clusterware on this node and re-run the tool
2. Run the tool with '-oh ' to first patch the Grid Home, then invoke tool with '-database ' or '-oh ' to patch the RAC home
Parameter Validation: FAILED

Opatchauto Session failed: Parameter validation failed
Exception in thread "main" java.lang.RuntimeException: java.io.IOException: Stream closed
                at oracle.opatchauto.gi.GILogger.writeWithoutTimeStamp(GILogger.java:432)
                at oracle.opatchauto.gi.GILogger.printStackTrace(GILogger.java:447)
                at oracle.opatchauto.gi.OPatchauto.main(OPatchauto.java:97)
Caused by: java.io.IOException: Stream closed
                at java.io.BufferedWriter.ensureOpen(BufferedWriter.java:98)
                at java.io.BufferedWriter.write(BufferedWriter.java:203)
                at java.io.Writer.write(Writer.java:140)
                at oracle.opatchauto.gi.GILogger.writeWithoutTimeStamp(GILogger.java:426)
                ... 2 more

opatchauto failed with error code 1.

Then if you try to start the has services, you get following error:

 [root ~]# $Grid_Home/bin/crsctl start has
CRS-6706: Oracle Clusterware Release patch level ('3749979535') does not match Software patch level ('2278979115'). Oracle Clusterware cannot be started.
CRS-4000: Command Start failed, or completed with errors.

SOLUTION:

So in order to resolve this, you need to issue following command as root user:
$ORA_GI_HOME/crs/install/roothas.pl –postpatch

It will start the has services too.

Then again run the analyze command as given above and it will work.
 



Categories: DBA Blogs

get a list of powershell date format outputs

Matt Penny - Wed, 2016-02-10 07:01

I want to create a list of Powershell date formats and their outputs, for a quick reference doc.

I’ve collected a list of formats from the Powershell help text and other locations and put them in a big text file which looks like this:

get-date -Uformat %A   # Day of the week - full name 
get-date -Uformat %u   # Day of the week - number (Monday = 1)
get-date -Uformat %d   # Day of the month - 2 digits 
get-date -Uformat %e   # Day of the month - digit preceded by a space ( 5)
get-date -Uformat %j   # Day of the year 
get-date -Uformat %p   # AM or PM
get-date -Uformat %r   # Time in 12-hour format
get-date -Uformat %R   # Time in 24-hour format - no seconds
get-date -Uformat %T   # Time in 24 hour format

I’m going to order this in what I consider to be order of usefullness (to me), because I’m going to plug it into an about_dateformats help page in my Powershell-help-powered repository of notes and tips.

To generate a list with the command, output and comment, I did this:

$Dates = foreach ($L in $(Select-String -notmatch "^$" formats.txt)) 
{
  [string]$Line = $L.Line
  $Command = $Line.split('#')[0]
  $Comment = $Line.split('#')[1]

  $scriptBlock = $executioncontext.InvokeCommand.NewScriptBlock($Line)

  $Output = invoke-command $ScriptBlock

  # write-output "$Command $Output # $Comment"
  new-object PSObject -Property @{
     Command = $Command
     Output =  $Output
     Comment = $Comment
  }

} 

$Dates | ft -a

The output looks like this:

<br />Output                            Command                                             Comment                                                           
------                            -------                                             -------                                                           
10                                Get-Date -UFormat %d                                                                                                  
Wed Feb 10 12:44:52 2016          get-date -UFormat %c                                 Date and time - abbreviated (Fri Jun 16 10:31:27 2006)           
02/10/16                          get-date -UFormat %D                                 Date in mm/dd/yy format (06/14/06)                               
02/10/16                          get-date -UFormat %x                                 Date in standard format for locale (09/12/07 for English-US)     
20                                get-date -Uformat %C                                 Century (20 for 2006)                                            
2016                              get-date -Uformat %Y                                 Year in 4-digit format (2006)                                    
16                                get-date -Uformat %y                                 Year in 2-digit format (06)                                      
Feb                               get-date -Uformat %b                                 Month name - abbreviated (Jan)                                   
February                          get-date -Uformat %B                                 Month name - full (January)                                      
02                                get-date -Uformat %m                                 Month number (06)                                                
5                                 get-date -Uformat %W                                 Week of the year (00-52)                                         
6                                 get-date -Uformat %V                                 Week of the year (01-53)                                         
Wed                               get-date -Uformat %a                                 Day of the week - abbreviated name (Mon)                         
Wednesday                         get-date -Uformat %A                                 Day of the week - full name (Monday)                             
3                                 get-date -Uformat %u                                 Day of the week - number (Monday = 1)                            
10                                get-date -Uformat %d                                 Day of the month - 2 digits (05)                                 
10                                get-date -Uformat %e                                 Day of the month - digit preceded by a space ( 5)                
41                                get-date -Uformat %j                                 Day of the year - (1-366)                                        
PM                                get-date -Uformat %p                                 AM or PM                                                         
12:44:52 PM                       get-date -Uformat %r                                 Time in 12-hour format (09:15:36 AM)                             
12:44                             get-date -Uformat %R                                 Time in 24-hour format - no seconds (17:45)                      
12:44:52                          get-date -Uformat %T                                 Time in 24 hour format (17:45:52)                                
+00                               get-date -Uformat %Z                                 Time zone offset from Universal Time Coordinate (UTC) (-07)      
12                                get-date -Uformat %H                                 Hour in 24-hour format (17)                                      
12                                get-date -Uformat %I                                 Hour in 12 hour format (05)                                      
44                                get-date -Uformat %M                                 Minutes (35)                                                     
52                                get-date -Uformat %S                                 Seconds (05)                                                     
1455108292.3719                   get-date -Uformat %s                                 Seconds elapsed since January 1, 1970 00:00:00 (1150451174.95705)
10/02/2016 12:44:52               Get-Date -DisplayHint Date                                                                                            
10/02/2016 12:44                  Get-Date -Format g                                                                                                    
2016 / 02 / 10 / Wednesday / +00  Get-Date -UFormat "%Y / %m / %d / %A / %Z"                                                                            
366                               (Get-Date -Year 2000 -Month 12 -Day 31).DayOfYear                                                                     
False                             $(get-date).IsDaylightSavingTime()                                                                                    
10/02/2016 12:44:52               $(get-date).ToUniversalTime()                                                                                         
10/02/2016 12:44:52               (Get-Date).ToString()                                                                                                 
2016-02-10T12:44:52.3749035+00:00 Get-Date -Format o                                                                                                    
2016-02-10T12.44.52.3759036+00.00 Get-Date -Format o | foreach {$_ -replace ":", "."}                           

Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs