Feed aggregator

Lot of time to add partitions to a Big Table

Tom Kyte - Sat, 2016-11-26 07:06
Hi Tom, I have a big table with 6 partitions(hash), the table have <b>270 Million records</b>, i try to add 2 partitions(i want to have 8 partitions for use "power of 2"), after prepare temp space and undo space, then i run process to add to partiti...
Categories: DBA Blogs

Synonyms

Tom Kyte - Sat, 2016-11-26 07:06
Hi, Please answer the below questions. 1. What Is the Difference between PUBLIC and PRIVATE synonyms? 2.Do we need GRANT privilages to acces PUBLIC SYNONYM? 3.Is PUBLIC Synonym a NON-Schema Object Or Schema Object?
Categories: DBA Blogs

INSTEAD OF TRIGGERS

Tom Kyte - Sat, 2016-11-26 07:06
INSTEAD OF triggers are executed instead of DML statements that fired it. Is it correct??
Categories: DBA Blogs

Drop MetaData

Tom Kyte - Sat, 2016-11-26 07:06
Hi, I installed 11g(enterprise) on my personal laptop.I Droped the Metadata DBA_OBJECTS,DBA_USERS(my intention is to know DB fuctioning),my Data base working properly.But When I execute Select query(SELECT * FROM dba_objects) getting *ora-10775 ...
Categories: DBA Blogs

Moving table partitions

Tom Kyte - Sat, 2016-11-26 07:06
Tom, Is it possible to move a table partition from one tablespace to another in the same way it is now possible to move a table from one tablespace to another ?
Categories: DBA Blogs

Oracle database In Memory

Tom Kyte - Sat, 2016-11-26 07:06
Hello Tom, I seen a new feature for Oracle 12c : In-Memory database cache. I seen it's a new memory area to organize data in column format for BI/DWH queries. I would like to know how Oracle decides when use the data from In-memory area or from...
Categories: DBA Blogs

Links for 2016-11-25 [del.icio.us]

Categories: DBA Blogs

Reading xml data from nested xml nodes using PL/SQL

Tom Kyte - Fri, 2016-11-25 12:46
Hello Tom, I am new to xml and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading and reviewed many of the OTN references but I...
Categories: DBA Blogs

Oracle 12.2 new features: long names

Tom Kyte - Fri, 2016-11-25 12:46
I was just going through https://blogs.oracle.com/sql/entry/12_things_developers_will_love and checking the new Oracle 12.2 features, and noticed something about the "Loooooooooooooooong Names". So say if I define a variable as v_table_name use...
Categories: DBA Blogs

Synonyms

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Please Go through the below code SQL> select * from san; --querying tbale NUM ---------- 1 2 SQL> select * from sam; --querying table VAR ---------- santhosh reddy SQL> SE...
Categories: DBA Blogs

Relation

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Is it possible to create a relationship between tables without using foreign key?
Categories: DBA Blogs

Impact of archivelogs on DB during Hot backup and Rman inc0 backup

Tom Kyte - Fri, 2016-11-25 12:46
Hi Tom, Please clarify on below queries. 1. if database is in Hot backup mode (between 2:00 AM to 4:00 AM), the parallel tx at that time will be written to archive logs. And once the hot backup mode finished these logs will be applied to ...
Categories: DBA Blogs

oracle function - rounding

Tom Kyte - Fri, 2016-11-25 12:46
how to round 122.41 value to 122.40. thank you.
Categories: DBA Blogs

Package

Tom Kyte - Fri, 2016-11-25 12:46
Hi, I would like to know the benefits of creating a package with multiple procedures and functions instead of creating them individually?
Categories: DBA Blogs

#DOAG2016

Yann Neuhaus - Fri, 2016-11-25 09:36

That’s a very late ‘wrap-up’ post about the DOAG Conference.
Well, not so late because it was last week but time flies and blog posts are already listed.

12.2

It started very well when Oracle has released the full 12.2 on the DBaaS cloud a few days before the conference. As lot of speakers, my slides and demo was full of 12.2 features. Of course, as beta tester we can ask the permission to talk about it but it’s not easy to replace any “12.2” with “the next generation of Oracle database” especially in titles that are limited to 60 characters ;)
So 12cR2 came on the Oracle Public Cloud, easily available in free trial, full documentation became public and my demos were still working… this was perfect.
The 12.2 beta program started one year ago. At the time of abstract submission, all speakers believed that 12.2 would be out at DOAG conference time. This was just in time.

Book

Exactly one year ago, at DOAG 2015, Vit Spinka came to me with this crazy idea to write a book on Multitenant. It was not the perfect fit in the planning for me as I was starting to prepare OCM 12c but that was a really good idea. The book was mostly finished in June but we had to wait for the public availability of 12.2. Now Oracle Press got the authorization tou publish it so it will probably be available soon. I had lot of questions about the date at DOAG. We have no date, but I hope to see it before Christmas.

Sharing

Of course those international events are very good for networking. Yes, DOAG conference is international. Lot of speakers coming from other continents. DOAG organized a nice dinner with speakers coming from abroad and/or being Oakies and/or ACED. Very nice conversations, and unconferences on different subjects: #cloud, #DBADev, PL/SQL … and those conversations will continue in two weeks in Birmingham.

Among the conversations, I was very happy to discuss with Tobias Deml who I asked to explain me the #DOAGNextGen community. Oracle DBA is a job where you don’t find a lot of young people. We always have that image of senior bearded guys in front of a text screen. It’s good to have experienced people but this must be mixed with some fresh blood. I really like what the DOAG Next Generation Community is doing. They have a lot of energy and ideas.

Tokyo

Speaking in the Tokio room is great. Especially when doing live demos. You don’t worry about your mintty fonts being too small or not.

2016-11-15 16.42.36

I was talking about the Multitenant features that can help in avoiding too many roundtrips between Dev and Ops.

DSC09076 - S

The future is about automation, fast provisioning, fast refresh and clear separation of roles. Call it #cloud if you like.

dbi services

DOAG is a big event for dbi services: lot of us participate, most being speakers, we have a booth to meet our customers and any one with questions about IT infrastructure. Of course, a bit about Cloud.

2016-11-17 08.42.41-crop

csm_DOAG_85_ba6c760e40

2017

I’m looking forward to DOAGDB in Dusseldorf (30-31 May 2017) – calls for paper currently opened. And next conference 21-24 November 2017

In the meantime if you want to learn more about 12.2, plan your upgrades and master the multitenant architecture… our 12c New Features workshop will be on 12cR2 in 2017 – first date in February: http://www.dbi-services.com/trainings/oracle-12c-new-features-workshop/

 

Cet article #DOAG2016 est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 3 – Tablespaces

Yann Neuhaus - Fri, 2016-11-25 09:02

In the last posts of this series we talked about restore points and how you could do things that would require the dual table in Oracle. In this post we’ll look at tablespaces. This is one of the features that is available in PostgreSQL but is totally different from what you know from Oracle. In Oracle you need to create a datafile which is attached to a tablespace. Once you have this you can start creating tables in there if you have the permissions to do so. How does this work in PostgreSQL?

Before we start playing with our own tablespaces you need to know that there are two default tablespaces in each PostgreSQL instance:

(postgres@[local]:5439) [postgres] > \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 21 MB  | 
 pg_global  | postgres |          |                   |         | 497 kB | 
(2 rows)

When you create a table and do not specify in which tablespace you want to get it created it will be created in the pg_default tablespace (this is the default tablespace for template0 and template1 and therefore will be the default for every user created database if not overwritten). pg_global contains the shared system catalog.

This means, whenever you create a table without specifying a tablespace in the create table statement it will go to the pg_default tablespace:

(postgres@[local]:5439) [postgres] > create table t1 ( a int );
CREATE TABLE
Time: 99.609 ms
(postgres@[local]:5439) [postgres] > select tablespace from pg_tables where tablename = 't1';
 tablespace 
------------
 NULL
(1 row)

NULL, in this case, means default. If you want to know where exactly the files that make up the tables are you can use oid2name:

postgres@pgbox:/home/postgres/ [PG961] oid2name -t t1
From database "postgres":
  Filenode  Table Name
----------------------
     24592          t1
postgres@pgbox:/home/postgres/ [PG961] find $PGDATA -name 2459*
/u02/pgdata/PG961/base/13322/24592

In addition oid2name tells you more about the databases and the default tablespace associated to them:

postgres@pgbox:/home/postgres/ [PG961] oid2name 
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13322       postgres  pg_default
  13321      template0  pg_default
      1      template1  pg_default

So far for the basics. Time to create our own tablespace. When you look at the syntax:

(postgres@[local]:5439) [postgres] > \h create tablespace
Command:     CREATE TABLESPACE
Description: define a new tablespace
Syntax:
CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

… this is quite different from what you know when you work with Oracle. The important point for now is the “LOCATION”. This refers to a directory somewhere the PostgreSQL owner has write access to. This can be a local directory, can be a directory on any storage the host has access to and it even can be on a ramdisk. It really doesn’t matter as long as the PostgreSQL OS user has write permissions to it.

Lets create our first tablespace:

(postgres@[local]:5439) [postgres] > \! mkdir /var/tmp/tbs1
(postgres@[local]:5439) [postgres] > create tablespace tbs1 location '/var/tmp/tbs1';
CREATE TABLESPACE
Time: 26.362 ms
(postgres@[local]:5439) [postgres] > \db+
                                     List of tablespaces
    Name    |  Owner   |   Location    | Access privileges | Options |  Size   | Description 
------------+----------+---------------+-------------------+---------+---------+-------------
 pg_default | postgres |               |                   |         | 21 MB   | 
 pg_global  | postgres |               |                   |         | 497 kB  | 
 tbs1       | postgres | /var/tmp/tbs1 |                   |         | 0 bytes | 
(3 rows)

What happened? The first thing to notice is that we can now see the “Location” column populated when we display all the tablespaces and that the size of our new tablespace is zero (well, not surprising as nothing is created in the tablespace right now). Did PostgreSQL already create datafiles in this location you might ask?

(postgres@[local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/
total 0
drwx------. 2 postgres postgres 6 Nov 25 11:03 PG_9.6_201608131

At least a directory which contains the version of PostgreSQL was created. What is inside this directory?

(postgres@[local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/PG_9.6_201608131/
total 0

Nothing, so lets create a table in this brand new tablespace:

(postgres@[local]:5439) [postgres] > create table t1 ( a int ) tablespace tbs1;
CREATE TABLE
(postgres@[local]:5439) [postgres] > \d+ t1
                          Table "public.t1"
 Column |  Type   | Modifiers | Storage | Stats target | Description 
--------+---------+-----------+---------+--------------+-------------
 a      | integer |           | plain   |              | 
Tablespace: "tbs1"

How does the directory look like now?:

(postgres@[local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/PG_9.6_201608131/
total 0
drwx------. 2 postgres postgres 18 Nov 25 12:02 13322

Ok, 13322 is the OID of the database which the table belongs to:

(postgres@[local]:5439) [postgres] > \! oid2name
All databases:
    Oid  Database Name  Tablespace
----------------------------------
  13322       postgres  pg_default
  13321      template0  pg_default
      1      template1  pg_default

And below that?

(postgres@[local]:5439) [postgres] > \! ls -l /var/tmp/tbs1/PG_9.6_201608131/13322/
total 0
-rw-------. 1 postgres postgres 0 Nov 25 12:02 24596

This is the OID of the table. So in summary this is the layout you get per tablespace:

|
|---[LOCATION]
|       |
|       | ----- [FIXED_VERSION_DIRECTORY]
|       |                  |
|       |                  |---------[DATABASE_OID]
|       |                  |              |
|       |                  |              |-----------[TABLE_AND_INDEX_FILES_OID]

One point that is often forgotten is that you can set various parameters on a tablespace level:

CREATE TABLESPACE tablespace_name
    [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
    LOCATION 'directory'
    [ WITH ( tablespace_option = value [, ... ] ) ]

What you can set per tablespace is:

This can be very helpful when you have tablespaces on disks (ramdisk?) that have very different performance specifications.

A very important point to keep in mind: Each tablespace you create in PostgreSQL creates a symlink in the clusters data directory:

CREATE TABLESPACE tablespace_name
postgres@pgbox:/home/postgres/ [PG961] ls -l $PGDATA/pg_tblspc 
total 0
lrwxrwxrwx. 1 postgres postgres 13 Nov 25 11:03 24595 -> /var/tmp/tbs1

Again, the number (24595) is the OID, in this case of the tablespace:

|
(postgres@[local]:5439) [postgres] > select oid,spcname from pg_tablespace where spcname = 'tbs1';
  oid  | spcname 
-------+---------
 24595 | tbs1
(1 row)

This is important to know because when you do backups of you PostgreSQL instance it is critical that you backup the tablespaces as well. You can find all the pointers/symlinks in the pg_tblspc directory.

What else can you do with tablespaces? Of course you can change the default tablespace for the whole instance:

|
(postgres@[local]:5439) [postgres] > alter system set default_tablespace='tbs1';
ALTER SYSTEM
Time: 120.406 ms

(postgres@[local]:5439) [postgres] > select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 4.279 ms
(postgres@[local]:5439) [postgres] > show default_tablespace ;
 default_tablespace 
--------------------
 tbs1
(1 row)

You can assign a tablespace to a database:

|
(postgres@[local]:5439) [postgres] > create database db1 TABLESPACE = tbs1;
CREATE DATABASE
Time: 1128.020 ms
(postgres@[local]:5439) [postgres] > \l+
                                                                    List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   |  Size   | Tablespace |                Description                 
-----------+----------+----------+-------------+-------------+-----------------------+---------+------------+--------------------------------------------
 db1       | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7233 kB | tbs1       | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |                       | 7343 kB | pg_default | default administrative connection database
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7233 kB | pg_default | unmodifiable empty database
           |          |          |             |             | postgres=CTc/postgres |         |            | 
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +| 7233 kB | pg_default | default template for new databases
           |          |          |             |             | postgres=CTc/postgres |         |            | 

You can make someone else the owner of a tablespace:

|
(postgres@[local]:5439) [postgres] > create user u1 password 'u1';
CREATE ROLE
Time: 31.414 ms
(postgres@[local]:5439) [postgres] > ALTER TABLESPACE tbs1 OWNER TO u1;
ALTER TABLESPACE
Time: 2.072 ms
(postgres@[local]:5439) [postgres] > \db
          List of tablespaces
    Name    |  Owner   |   Location    
------------+----------+---------------
 pg_default | postgres | 
 pg_global  | postgres | 
 tbs1       | u1       | /var/tmp/tbs1
(3 rows)

And finally you can set one or more tablespaces to be used as temporary tablespaces:

|
(postgres@[local]:5439) [postgres] > alter system set temp_tablespaces='tbs1';
ALTER SYSTEM
Time: 4.175 ms

(postgres@[local]:5439) [postgres] > select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

Time: 3.638 ms
(postgres@[local]:5439) [postgres] > show temp_tablespaces ;
 temp_tablespaces 
------------------
 tbs1
(1 row)

Conclusion: Yes, you can have tablespaces in PostgreSQL and they give you great flexibility on how you can organize your PostgreSQL files on disk. The implementation is very different from other vendors, though.

 

Cet article Can I do it with PostgreSQL? – 3 – Tablespaces est apparu en premier sur Blog dbi services.

12.2 New Features -- 3 : Flashback Pluggable Database

Hemant K Chitale - Fri, 2016-11-25 08:35
12.1 allows Point In Time Recovery of a Pluggable Database but not Flashback of an individual PDB.

12.2 now allows Flashback of an individual PDB.   This is easier with a Local Undo Tablespace instead of a Shared Undo Tablespace.

Here is a quick demo (all times in UTC timezone) :

[oracle@HKCORCL ~]$ sqlplus system/Oracle_4U@PDB1

SQL*Plus: Release 12.2.0.1.0 Production on Fri Nov 25 14:19:06 2016

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

Last Successful login time: Thu Nov 24 2016 01:03:52 +00:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL> drop table hr.employees_part purge;

Table dropped.

SQL> connect / as sysdba
Connected.
SQL> alter system switch logfile;

System altered.

SQL> alter pluggable database pdb1 close;

Pluggable database altered.

SQL>
SQL> select sysdate, sysdate-oldest_flashback_time
2 from v$flashback_database_log;

SYSDATE SYSDATE-OLDEST_FLASHBACK_TIME
--------- -----------------------------
25-NOV-16 2.36273148

SQL>
SQL> flashback pluggable database pdb1
2 to timestamp sysdate-2/24;

Flashback complete.

SQL> alter pluggable database pdb1 open;
alter pluggable database pdb1 open
*
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17:
'/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf
_pdb1undo_d3dgxhbh_.dbf'


SQL> alter pluggable database pdb1 open resetlogs;

Pluggable database altered.

SQL>
SQL> connect system/Oracle_4U@PDB1
Connected.
SQL> select count(*) from hr.employees_part;

COUNT(*)
----------
107

SQL>


(Note : The 12.2 documentation shows the TO TIME clause, it is actually TO TIMESTAMP).
I have done a flashback of PDB1 to a time as of 2hours ago, when the table HR.EMPLOYEES_PART still existed.

Let's look for messages in the alert log.

2016-11-25T14:19:52.992589+00:00
Archived Log entry 11 added for T-1.S-11 ID 0x38800462 LAD:1
2016-11-25T14:19:57.621705+00:00
alter pluggable database pdb1 close
2016-11-25T14:19:57.640353+00:00
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:19:58.885892+00:00
Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 close
2016-11-25T14:26:10.205824+00:00
flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:10.627900+00:00
Flashback Restore Start
2016-11-25T14:26:11.513882+00:00
Restore Flashback Pluggable Database PDB1 (3) until change 3536013
Flashback Restore Complete
2016-11-25T14:26:11.707236+00:00
Flashback Media Recovery Start
2016-11-25T14:26:11.718480+00:00
Serial Media Recovery started
2016-11-25T14:26:12.006472+00:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 11 Reading mem 0
Mem# 0: /u04/app/oracle/redo/redo02.log
2016-11-25T14:26:12.283587+00:00
Incomplete Recovery applied until change 3536477 time 11/25/2016 12:26:56
Flashback Media Recovery Complete
Flashback Pluggable Database PDB1 (3) recovered until change 3536477, at 11/25/2016 12:26:56
Completed: flashback pluggable database pdb1
to timestamp sysdate-2/24
2016-11-25T14:26:21.451523+00:00
alter pluggable database pdb1 open
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:21.659109+00:00
Pdb PDB1 hit error 1113 during open read write (1) and will be closed.
2016-11-25T14:26:21.659410+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_ora_7920.trc:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:21.804780+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 9: '/u02/app/oracle/oradata/HKCORCL/PDB1/system01.dbf'
ORA-1113 signalled during: alter pluggable database pdb1 open...
2016-11-25T14:26:22.086212+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 10: '/u02/app/oracle/oradata/HKCORCL/PDB1/sysaux01.dbf'
2016-11-25T14:26:22.175778+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 12: '/u02/app/oracle/oradata/HKCORCL/PDB1/users01.dbf'
2016-11-25T14:26:22.270876+00:00
Errors in file /u01/app/oracle/diag/rdbms/hkcorcl/HKCORCL/trace/HKCORCL_m000_9995.trc:
ORA-01110: data file 17: '/u02/app/oracle/oradata/HKCORCL/4157E08302CC2021E053B2D4100AABA3/datafile/o1_mf_pdb1undo_d3dgxhbh_.dbf'
Checker run found 4 new persistent data failures
2016-11-25T14:26:39.804216+00:00
alter pluggable database pdb1 open resetlogs
2016-11-25T14:26:40.377390+00:00
Online datafile 17
Online datafile 12
Online datafile 10
Online datafile 9
2016-11-25T14:26:40.881181+00:00
PDB1(3):Autotune of undo retention is turned on.
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868281239 end:868281333 diff:94 ms (0.1 seconds)
PDB1(3):Database Characterset for PDB1 is AL32UTF8
PDB1(3):JIT: pid 7920 requesting stop
2016-11-25T14:26:42.441388+00:00
PDB1(3):Autotune of undo retention is turned on.
2016-11-25T14:26:42.827673+00:00
PDB1(3):Endian type of dictionary set to little
PDB1(3):[7920] Successfully onlined Undo Tablespace 7.
PDB1(3):Undo initialization finished serial:0 start:868283079 end:868283168 diff:89 ms (0.1 seconds)
PDB1(3):Pluggable database PDB1 dictionary check beginning
2016-11-25T14:26:43.706672+00:00
PDB1(3):Pluggable Database PDB1 Dictionary check complete
PDB1(3):Database Characterset for PDB1 is AL32UTF8
2016-11-25T14:26:44.083617+00:00
PDB1(3):Opatch validation is skipped for PDB PDB1 (con_id=0)
PDB1(3):Opening pdb with no Resource Manager plan active
2016-11-25T14:26:45.205147+00:00
Starting control autobackup

Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/415864F430FE5FFEE053B2D4100A149C/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qldlnv_.bkp
2016-11-25T14:26:46.523130+00:00
Deleted Oracle managed file /u03/app/oracle/fast_recovery_area/HKCORCL/3E09703FB0AF1A7EE053DE4BC40A6C1D/backupset/2016_11_16/o1_mf_nnndf_TAG20161116T024856_d2qlfzqg_.bkp
Control autobackup written to DISK device

handle '/u03/app/oracle/fast_recovery_area/HKCORCL/autobackup/2016_11_25/o1_mf_s_928852005_d3jlk651_.bkp'

Pluggable database PDB1 closed
Completed: alter pluggable database pdb1 open resetlogs


The set of ORA-01113 and ORA-01110 errors are when I tried to open PDB1 without a RESETLOGS.
The OPEN RESETLOGS issued at 2016-11-25T14:26:39.804216+00:00 was successful.
(Note : The ALTER SYSTEM SWITCH LOGFILE wasn't required but I like to archive out the CURRENT redo whenever I make a significant action against the database).

.
.
.

Categories: DBA Blogs

Documentum story – Lockbox file is newer than the active Lockbox library

Yann Neuhaus - Fri, 2016-11-25 02:00

A few weeks ago at a customer, I was installing a completely new sandbox environment for one of our Application Teams. On this environment, we used a new version stack with the most recent patch set (at that time): CS 7.2 P16, D2 4.6 P05, aso… A new patch level means a lot of “fun” trying to install and execute the updated installers of Documentum/BPM/D2… And of course a lot of new bugs ;). In this blog I will talk about a new issue we never faced before that is related to the Lockbox libraries.

 

With the older version stack, when we installed D2, we always kept the D2 Lockbox libraries under $DOCUMENTUM/d2-lib/lockbox on the CS and then we referenced these libraries in the environment variable ($PATH, $LD_LIBRARY_PATH and $CLASSPATH) of our Installation Owner. In the documentation, it is not asked to set these variables as “permanent” by adding them in the bash_profile for example or something similar but this is what we used to do to avoid adding them again and again each time we needed to update the D2 Lockbox.

 

For this new version stack and since the Official Documentation didn’t change, we kept the same approach. After installing D2, we had to restart the Content Server for a maintenance at the OS level. As you probably already know if you are familiar with the CS 7.2, there is also a Lockbox specific to the CS that is normally stored under $DOCUMENTUM/dba/secure/. Whenever you restart the host, you will need to store the lockbox inside the Shared Memory and that’s how it is done:

[dmadmin@content_server_01 ~]$ pwlockbox="L0ckb0xP4ssphr4se"
[dmadmin@content_server_01 ~]$ pwaek="A3kP4ssphr4se"
[dmadmin@content_server_01 ~]$ 
[dmadmin@content_server_01 ~]$ dm_crypto_boot -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -passphrase $pwaek -all

Please wait. This will take a few seconds ...

Please wait, this will take a few seconds..
Setting up the (single) passphrase for all keys in the shared memory region..
Operation succeeded
[dmadmin@content_server_01 ~]$ 
[dmadmin@content_server_01 ~]$ 
[dmadmin@content_server_01 ~]$ dm_crypto_manage_lockbox -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -resetfingerprint
Lockbox lockbox.lb
Lockbox Path $DOCUMENTUM/dba/secure/lockbox.lb
Reset host done

 

These two commands are working properly so the Lockbox has been put in the Shared Memory – normally – and the docbases should now be able to start. As explained before, we are referencing the D2 Lockbox libraries inside the environment variable by default and not the CS specific versions. So I tried to start the docbase as always and checked the logs to ensure that it was running properly:

[dmadmin@content_server_01 ~]$ $DOCUMENTUM/dba/dm_start_GR_DOCBASE
starting Documentum server for repository: [GR_DOCBASE]
with server log: [$DOCUMENTUM/dba/log/GR_DOCBASE.log]
server pid: 7056
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ cat $DOCUMENTUM/dba/log/GR_DOCBASE.log
The Lockbox file is newer than the active Lockbox library. Retry with a newer version of the Lockbox library.
The Lockbox file is newer than the active Lockbox library. Retry with a newer version of the Lockbox library.
2016-09-29T12:02:38.314624      7056[7056]      0000000000000000        [DM_CRYPTO_F_KEYSTORE_INIT]fatal:  "Failed to initialize keystore at $DOCUMENTUM/dba/secure/CSaek. Internal error - 1057226514"

[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$

 

So that’s the error I wanted to share in this blog. As you can see, the docbase wasn’t able to start because the lockbox file is “newer” than the active Lockbox libraries. So I started to do some tests to try to understand what was happening exactly and how to solve it.

 

Ok so the first test I did is removing the D2 Lockbox libraries from the environment variables (.bash_profile + reload of shell session) and then try to start the docbase again:

[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ exit
logout
[morganpatou@content_server_01 ~]$ su - dmadmin
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$
[dmadmin@content_server_01 ~]$ pwlockbox="L0ckb0xP4ssphr4se"
[dmadmin@content_server_01 ~]$ pwaek="A3kP4ssphr4se"
[dmadmin@content_server_01 ~]$ dm_crypto_boot -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -passphrase $pwaek -all

Please wait. This will take a few seconds ...

Please wait, this will take a few seconds..
Setting up the (single) passphrase for all keys in the shared memory region..
Operation succeeded
[dmadmin@content_server_01 ~]$ 
[dmadmin@content_server_01 ~]$ 
[dmadmin@content_server_01 ~]$ dm_crypto_manage_lockbox -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -resetfingerprint
Lockbox lockbox.lb
Lockbox Path $DOCUMENTUM/dba/secure/lockbox.lb
Lockbox open failed The Lockbox file is newer than the active Lockbox library. Retry with a newer version of the Lockbox library.
** Operation failed **
[dmadmin@content_server_01 ~]$

 

As you can see, after removing the D2 Lockbox libraries from the environment, the lockbox can’t be stored in the Shared Memory anymore… So based on this information, it is clear that the Content Server Lockbox libraries (stored under $DM_HOME/bin) are different than the D2 Lockbox libraries (in our case stored under $DOCUMENTUM/d2-lib/lockbox)… It’s “fun” that two patches that have been released exactly at the same time are using different versions of the Lockbox libraries… The annoying point here is that the CS Lockbox isn’t readable anymore using the CS Lockbox libraries and that can be checked using the following command:

[dmadmin@content_server_01 ~]$ dm_crypto_create -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -keyname CSaek -passphrase $pwaek -algorithm AES_256_CBC -check

The Lockbox file is newer than the active Lockbox library. Retry with a newer version of the Lockbox library.
** No AEK store exists in lockbox lockbox.lb and got status code returned as '1057226514'.
[dmadmin@content_server_01 ~]$ 
[dmadmin@content_server_01 ~]$

 

The problem I can see here is that when installing D2, the CS Lockbox has apparently somehow been corrupted by the D2 Lockbox libraries. The fact is that when a docbase is trying to start, the start script (dm_start_GR_DOCBASE) is setting the environment using the script $DM_HOME/bin/dm_set_server_env.sh which overwrites our environment variables because this file is using the CS Lockbox libraries. That’s the reason why the docbase wasn’t able to start earlier: the docbase is trying to start using the CS libraries (older) while the lockbox has been tempered with the D2 libraries (newer).

 

There are several ways to quickly workaround this specific issue and I will try to describe them below.

 

First solution: Create a copy of the dm_crypto_create and dm_crypto_create.bin files to force it to use the environment variable defined in the .bash_profile and not reload them based on the dm_set_server_env.sh (removing the 3 lines related to the LD_LIBRARY_PATH):

[dmadmin@content_server_01 ~]$ cd $DM_HOME/bin
[dmadmin@content_server_01 bin]$ cp dm_crypto_create my_dm_crypto_create
[dmadmin@content_server_01 bin]$ cp dm_crypto_create.bin my_dm_crypto_create.bin
[dmadmin@content_server_01 bin]$ vi my_dm_crypto_create
[dmadmin@content_server_01 bin]$ diff dm_crypto_create my_dm_crypto_create
21,23d20
< else
<   LD_LIBRARY_PATH=${location}
<   export LD_LIBRARY_PATH

 

Once this is done, you can compare the result of the check command:

[dmadmin@content_server_01 bin]$ ./dm_crypto_create -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -keyname CSaek -passphrase $pwaek -algorithm AES_256_CBC -check

The Lockbox file is newer than the active Lockbox library. Retry with a newer version of the Lockbox library.
** No AEK store exists in lockbox lockbox.lb and got status code returned as '1057226514'.
[dmadmin@content_server_01 bin]$
[dmadmin@content_server_01 bin]$
[dmadmin@content_server_01 bin]$ ./my_dm_crypto_create -lockbox lockbox.lb -lockboxpassphrase $pwlockbox -keyname CSaek -passphrase $pwaek -algorithm AES_256_CBC -check

Key - CSaek uses algorithm AES_256_CBC.

** An AEK store with the given passphrase exists in lockbox lockbox.lb and got status code returned as '0'.
[dmadmin@content_server_01 bin]$
[dmadmin@content_server_01 bin]$

 

=> As you can see, this is now working with the updated script but that’s not really a sexy solution…

 

 

Second solution: Update the file dm_set_server_env.sh to force it to use the D2 Lockbox libraries instead of the CS ones. You can check the Official Documentation about how to setup environment variables for D2 and then just put that at the end of this file. In our case, this was something like:

[dmadmin@content_server_01 bin]$ tail -6 $DM_HOME/bin/dm_set_server_env.sh
export LD_LIBRARY_PATH=$DOCUMENTUM/d2-lib/lockbox/lib/native/linux_gcc34_x64:$LD_LIBRARY_PATH
export PATH=$DOCUMENTUM/d2-lib/lockbox/lib/native/linux_gcc34_x64:$PATH
export CLASSPATH=$DOCUMENTUM/d2-lib/D2.jar:$DOCUMENTUM/d2-lib/LB.jar:$DOCUMENTUM/d2-lib/LBJNI.jar:$CLASSPATH
# The environment that exists after this script executes is output to
# the dm_set_server_env.log file in $DOCUMENTUM_SHARED/logs
env >> $DOCUMENTUM_SHARED/logs/dm_set_server_env.log

 

This solution is a little bit better but as soon as you will upgrade/migrate your Content Server, this file might be overwritten by the Patch and therefore you will face this issue again… :(

 

The solution N°2 is what EMC recommended us to do so that’s what we did in the first place and we were able to start the docbase, aso… But one week later, we tried to install a new docbase and then the Server Configuration Program wasn’t able to read the lockbox anymore and therefore we weren’t able to create any new docbase! This is an extract from the file Server Configuration Program log file:

13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserSelection: "lockbox.lb"
13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserSelection: "AES_256_CBC"
13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserInput: "CSaek"
13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserInputPassword: "**********"
13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserSelection: "true"
13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserInput: "lockbox.lb"
13:58:10,266  INFO [AWT-EventQueue-0] com.documentum.install.server.ui.panels.DiWPServerUseExistingLockBox - UserInputPassword: "**********"
13:58:10,507 ERROR [Thread-97] com.documentum.install.server.installanywhere.actions.DiWAServerValidateLockboxPassphrase - Check AEK key passphrase failed

 

Therefore the solutions 1 and 2 are actually not good because we are able to start the docbase but not to install a new one… So we had to find another, better solution and it all started with this question: which component(s) is/are actually using the D2 Lockbox on the Content Server? Of course the answer to this question is: only the Java Method Server.

 

Based on this information, we therefore decided to restrict the access to the D2 Lockbox libraries only to the JMS. This has been done by removing the environment variables mentioned above from the bash_profile and/or from the dm_set_server_env.sh and then by updating the JMS startup script with the same:

[dmadmin@content_server_01 ~]$ head -6 $DOCUMENTUM_SHARED/jboss7.1.1/server/startMethodServer.sh
#!/bin/sh

#for D2.lockbox
export LD_LIBRARY_PATH=$DOCUMENTUM/d2-lib/lockbox/lib/native/linux_gcc34_x64:$LD_LIBRARY_PATH
export PATH=$DOCUMENTUM/d2-lib/lockbox/lib/native/linux_gcc34_x64:$PATH
export CLASSPATH=$DOCUMENTUM/d2-lib/D2.jar:$DOCUMENTUM/d2-lib/LB.jar:$DOCUMENTUM/d2-lib/LBJNI.jar:$CLASSPATH

 

After that, we “simply” regenerated the Content Server Lockbox with the CS libraries (basically you need to remove completely the current encryption inside the docbase and force the CS to recreate it from scratch… Arnaud Berbier will most probably write a blog on this subject in the next few days/weeks). Then we restarted the whole Content Server and everything was working properly with the right Lockbox libraries and we were able to create new docbases.

 

Cet article Documentum story – Lockbox file is newer than the active Lockbox library est apparu en premier sur Blog dbi services.

Can I do it with PostgreSQL? – 2 – Dual

Yann Neuhaus - Fri, 2016-11-25 00:42

In the first post of this series we talked about restore points. Another question that pops up from time to time is how you can do things in PostgreSQL that would require the dual table in Oracle. Lets go …

The question is: When do you need the dual table in Oracle? Well, everything time you have nothing to select from, meaning no table you could provide in the from clause and you need exactly one row. This could be the case when you want to do math:

SQL> select 1+2+3*4/2 from dual;

 1+2+3*4/2
----------
	 9

This can be the case when you want to generate test data:

SQL> select 'a' from dual connect by level <= 5;

'
-
a
a
a
a
a

This can be the case when you want to select from a PL/SQL function, such as:

SQL> create table ta (a number);

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','TA',USER) from dual;

DBMS_METADATA.GET_DDL('TABLE','TA',USER)
--------------------------------------------------------------------------------

  CREATE TABLE "SYS"."TA"
   (	"A" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS

… any many more.

The easy answer to the question if you can do it in PostgreSQL is: You don’t need to. Why? Because you can do things like this:

(postgres@[local]:5439) [postgres] > select 'Time for a beer';
    ?column?     
-----------------
 Time for a beer
(1 row)

… or this:

(postgres@[local]:5439) [postgres] > select 1+2+3*4/2;
 ?column? 
----------
        9
(1 row)

The same is true for getting the results of a function:

(postgres@[local]:5439) [postgres] > create function f1 (integer,integer) returns integer
as 'select $1 * $2;'
language sql;
CREATE FUNCTION
Time: 249.499 ms
(postgres@[local]:5439) [postgres] > select f1(5,5);
 f1 
----
 25
(1 row)

PostgreSQL does not force you to provide a table to select from. You can completely skip this. Looks strange when you are used to work with Oracle, I know, but hey: This is much more easy: Why provide a from clause when it is not necessary?

If you really, really can’t live without dual:

(postgres@[local]:5439) [postgres] > create table dual (dummy varchar(1));
CREATE TABLE
(postgres@[local]:5439) [postgres] > insert into dual (dummy) values ('a');
INSERT 0 1
(postgres@[local]:5439) [postgres] > select 'I can not live without dual' from dual;
          ?column?           
-----------------------------
 I can not live without dual
(1 row)
(postgres@[local]:5439) [postgres] > select 1+2+3*4/2 from dual;
 ?column? 
----------
        9
(1 row)

And here you go …

 

Cet article Can I do it with PostgreSQL? – 2 – Dual est apparu en premier sur Blog dbi services.

Foreign key reference table

Tom Kyte - Thu, 2016-11-24 18:26
Hi, A table contains a primary key so it referes to another table,I know table name and its primary key column name but i dont know to which table it referes. So is it possible to know the reference table name and foreign key column name?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator