Beiträge getaggt mit 12c New Features
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:
Let’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!
Online Move of Datafiles for Pluggable Databases
From my present Oracle Database 12c New Features course in Zürich: We have introduced the handy new functionality that you can move datafiles online in 12c. That is at first glance having an issue for pluggable databases:
SQL> @whoami
USER: SYS
SESSION ID: 253
CURRENT_SCHEMA: SYS
INSTANCE NAME: cdb1
CDB NAME: cdb1
CONTAINER NAME: CDB$ROOT
DATABASE ROLE: PRIMARY
OS USER: oracle
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/system01.dbf
/u01/app/oracle/oradata/cdb1/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/system01.dbf
/u01/app/oracle/oradata/cdb1/users01.dbf
/u01/app/oracle/oradata/cdb1/pdbseed/sysaux01.dbf
/u01/app/oracle/oradata/pdb1_1/system01.dbf
/u01/app/oracle/oradata/pdb1_1/sysaux01.dbf
/u01/app/oracle/oradata/pdb1_1/SAMPLE_SCHEMA_users01.dbf
/u01/app/oracle/oradata/pdb1_1/example01.dbf
10 rows selected.
SQL> alter database move datafile '/u01/app/oracle/oradata/pdb1_1/example01.dbf' to '/home/oracle/example01.dbf';
alter database move datafile '/u01/app/oracle/oradata/pdb1_1/example01.dbf' to '/home/oracle/example01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "11"
The error message is quite useless in this case. It works, but you need to be in the PDBs container where the datafile belongs to:
SQL> alter session set container=pdb1_1;
Session altered.
SQL> alter database move datafile '/u01/app/oracle/oradata/pdb1_1/example01.dbf' to '/home/oracle/example01.dbf';
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/pdb1_1/system01.dbf
/u01/app/oracle/oradata/pdb1_1/sysaux01.dbf
/u01/app/oracle/oradata/pdb1_1/SAMPLE_SCHEMA_users01.dbf
/home/oracle/example01.dbf
Don’t believe it (even error messages may lie!), test it 🙂
Speed up Import with TRANSFORM=DISABLE_ARCHIVE_LOGGING in #Oracle 12c
A very useful 12c New Feature is the option to suppress the generation of redo during Data Pump import. I was talking about it during my recent 12c New Features class in Finland and like to share that info with the Oracle Community here. My usual demo user ADAM owned a table named BIG with one index on it. Both were in LOGGING mode when I exported them. The Data Pump export did not use any 12c New Feature and is not shown therefore.
SQL> select log_mode,force_logging from v$database; LOG_MODE FORCE_LOGGING ------------ --------------------------------------- ARCHIVELOG NO SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 0 0 0 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
The database is not in force logging mode – else the new Data Pump parameter would be ignored. Archive log mode was just turned on, therefore no archive log file yet. First I will show the redo generating way to import, which is the default. Afterwards the new feature for comparison.
SQL> host impdp adam/adam directory=DPDIR tables=big Import: Release 12.1.0.1.0 - Production on Mon Jan 20 11:50:42 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "ADAM"."SYS_IMPORT_TABLE_01": adam/******** directory=DPDIR tables=big Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "ADAM"."BIG" 660.1 MB 5942016 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jan 20 11:54:32 2014 elapsed 0 00:03:48 SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 13.47 0 12 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected.
The conventional way with redo generation took almost 4 minutes and generated 12 archive logs – my online logs are 100 megabyte in size. Now let’s see the new feature:
SQL> drop table big purge; Table dropped. SQL> host impdp adam/adam directory=DPDIR tables=big transform=disable_archive_logging:y Import: Release 12.1.0.1.0 - Production on Mon Jan 20 11:57:19 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options Master table "ADAM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded Starting "ADAM"."SYS_IMPORT_TABLE_01": adam/******** directory=DPDIR tables=big transform=disable_archive_logging:y Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported "ADAM"."BIG" 660.1 MB 5942016 rows Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER Job "ADAM"."SYS_IMPORT_TABLE_01" successfully completed at Mon Jan 20 11:58:21 2014 elapsed 0 00:01:01 SQL> select * from v$recovery_area_usage; FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES CON_ID ----------------------- ------------------ ------------------------- --------------- ---------- CONTROL FILE 0 0 0 0 REDO LOG 0 0 0 0 ARCHIVED LOG 13.47 0 12 0 BACKUP PIECE 0 0 0 0 IMAGE COPY 0 0 0 0 FLASHBACK LOG 0 0 0 0 FOREIGN ARCHIVED LOG 0 0 0 0 AUXILIARY DATAFILE COPY 0 0 0 0 8 rows selected. SQL> select table_name,logging from user_tables; TABLE_NAME LOG ------------------------------------------------------------ --- BIG YES SQL> select index_name,logging from user_indexes; INDEX_NAME LOG ------------------------------------------------------------ --- BIG_IDX YES
Note that the segment attributes are not permanently changed to NOLOGGING by the Data Pump import.
The comparison shows a striking improvement in run time – because the 2nd run did not generate additional archive logs, we still see the same number as before the 2nd call.
Another option is to suppress redo generation only for the import of indexes, in my example with the command
impdp adam/adam directory=DPDIR tables=big transform=disable_archive_logging:y:index
That is a safer choice because indexes are always reproducible. Keep in mind that any NOLOGGING operation is a risk – that is the price to pay for the speed up.
As always: Don’t believe it, test it! 🙂
Pickleball spielen auf YouTube
Pickleball spielen 