Restore datafile from service: A cool #Oracle 12c Feature

You can restore a datafile directly from a physical standby database to the primary. Over the network. With compressed backupsets. How cool is that?

Here’s a demo from my present class Oracle Database 12c: Data Guard Administration. prima is the primary database on host01, physt is a physical standby database on host03. There is an Oracle Net configuration on both hosts that enable host01 to tnsping physt and host03 to tnsping prima

 

[oracle@host01 ~]$ rman target sys/oracle@prima

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Jul 2 16:43:39 2014

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

connected to target database: PRIMA (DBID=2084081935)

RMAN> run
{
set newname for datafile 4 to '/home/oracle/stage/users01.dbf';
restore (datafile 4 from service physt) using compressed backupset;
catalog datafilecopy '/home/oracle/stage/users01.dbf';
}

executing command: SET NEWNAME

Starting restore at 02-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=47 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: using compressed network backup set from service physt
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /home/oracle/stage/users01.dbf
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 02-JUL-14

cataloged datafile copy
datafile copy file name=/home/oracle/stage/users01.dbf RECID=8 STAMP=851877850

This does not require backups taken on the physical standby database.

, ,

4 Comments

Data Guard 12c New Features: Far Sync & Real-Time Cascade

UKOUG Oracle Scene has published my article about two exciting Data Guard 12c New Features:

http://viewer.zmags.com/publication/62b883ad#/62b883ad/44

Far Sync Instance enables Zero-Data-Loss across large distance

Hope you find it useful :-)

,

Leave a comment

Common Users & SYSDBA with #Oracle 12c Multitenancy

A 12c multitenant database introduces the new concept of local users and common users. This article shows simple use cases why DBAs may want to create common users – in contrast to the common users that are created automatically, like SYS, SYSTEM, MDSYS etc.

A typical requirement is to have a superuser other than SYS, but with the same power. Like the common user C##_SYS in the picture below.

Or suppose we have many pluggable databases (PDBs) and different superusers responsible for different PDBs like C##_ADMIN1 and C##_ADMIN2:

Common Users in Oracle 12cLet’s implement it as above. Initially, my demo environment looks like this:

 

SQL> select name,open_mode,con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
PDB1                           READ WRITE          3
PDB2                           READ WRITE          4
PDB3                           READ WRITE          5

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1

At first, I create C##_SYS and grant SYSDBA as a common privilege to that new user:

SQL> create user c##_sys identified by oracle container=all;

User created.

SQL> grant sysdba to c##_sys container=all;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
C##_SYS                        TRUE  FALSE FALSE FALSE FALSE FALSE          0

C##_SYS can now do anything to any PDB:

SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> shutdown immediate
Pluggable Database closed.
SQL> connect / as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
PDB1                           MOUNTED             3
PDB2                           READ WRITE          4
PDB3                           READ WRITE          5

SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> startup
Pluggable Database opened.
SQL> connect / as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
PDB1                           READ WRITE          3
PDB2                           READ WRITE          4
PDB3                           READ WRITE          5

Notice that there is a subtle difference in granting SYSDBA of the container database (CDB) as local or common privilege:

SQL> revoke sysdba from c##_sys container=all;

Revoke succeeded.

SQL> grant sysdba to c##_sys;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
C##_SYS                        TRUE  FALSE FALSE FALSE FALSE FALSE          1

SQL> connect c##_sys/oracle@edd2r6p0/pdb1 as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.

C##_SYS has SYSDBA of the CDB “only”, therefore the error. Although:

SQL> connect c##_sys/oracle as sysdba
Connected.
SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT
SQL> alter session set container=pdb1;

Session altered.

SQL> shutdown immediate
Pluggable Database closed.
SQL> show con_name

CON_NAME
------------------------------
PDB1
SQL> startup
Pluggable Database opened.
SQL> connect c##_sys/oracle as sysdba
Connected.
SQL> select name,open_mode,con_id from v$pdbs;

NAME                           OPEN_MODE      CON_ID
------------------------------ ---------- ----------
PDB$SEED                       READ ONLY           2
PDB1                           READ WRITE          3
PDB2                           READ WRITE          4
PDB3                           READ WRITE          5

However, the proper way is probably granting it as a common privilege:

SQL> revoke sysdba from c##_sys;

Revoke succeeded.

SQL> grant sysdba to c##_sys container=all;

Grant succeeded.

Now to the implementation of C##_ADMIN1 and C##_ADMIN2. The point is here, that SYSDBA can be granted as a local privilege for certain PDBs only, to the effect that different superusers can be responsible for different groups of PDBs:

SQL> create user c##_admin1 identified by oracle container=all;

User created.

SQL> alter session set container=pdb1;

Session altered.

SQL> grant sysdba to c##_admin1 container=current;

Grant succeeded.

SQL> connect / as sysdba
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
C##_SYS                        TRUE  FALSE FALSE FALSE FALSE FALSE          0
C##_ADMIN1                     TRUE  FALSE FALSE FALSE FALSE FALSE          3

6 rows selected.

For now, C##_ADMIN1 can only connect to PDB1:

SQL> connect c##_admin1/oracle@edd2r6p0/pdb1 as sysdba
Connected.
SQL> select count(*) from session_privs;

  COUNT(*)
----------
       233

SQL> connect c##_admin1/oracle@edd2r6p0/pdb2 as sysdba
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

The completed implementation of the picture above:

SQL> connect / as sysdba
Connected.
SQL> alter session set container=pdb2;

Session altered.

SQL> grant sysdba to c##_admin1 container=current;

Grant succeeded.

SQL> connect / as sysdba
Connected.
SQL> create user c##_admin2 identified by oracle;

User created.

SQL> alter session set container=pdb3;

Session altered.

SQL> grant sysdba to c##_admin2 container=current;

Grant succeeded.

SQL> connect / as sysdba
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          1
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          1
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           1
C##_SYS                        TRUE  FALSE FALSE FALSE FALSE FALSE          0
C##_ADMIN1                     TRUE  FALSE FALSE FALSE FALSE FALSE          3
C##_ADMIN1                     TRUE  FALSE FALSE FALSE FALSE FALSE          4
C##_ADMIN2                     TRUE  FALSE FALSE FALSE FALSE FALSE          5

8 rows selected.

The whole example is from my present 12c New Features in Düsseldorf, by the way. Hope you find it useful :-)
As always: Don’t believe it, test it!

Leave a comment

Follow

Get every new post delivered to your Inbox.

Join 2,604 other followers

%d bloggers like this: