Learn oracle 12c database management
I will be concentrating mostly on 12c database on this site. you can visit my other blog for knowledge on previous versions of oracle database.
Arvind Toorpuhttp://www.blogger.com/profile/14794652012220177259noreply@blogger.comBlogger38125
Updated: 15 hours 40 min ago
Data Pump Exit Codes
oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$
About to export specified tables via Conventional Path ...
. . exporting table AUD$ 494321 rows exported
Export terminated successfully without warnings.
oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $?
0
oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log
LRM-00101: unknown parameter name 'logifle'
IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help
IMP-00000: Import terminated unsuccessfully
oracle@Linux01:[/u01/oracle/DPUMP] $ echo $?
1
Can be used in export shell scripts for status verification:
if test $status -eq 0
then
echo "export was successfull."
else
echo "export was not successfull."
fi
Also check below page fore reference :
Categories: DBA Blogs
Automate recyclebin purge in oracle
Setup this simple scheduler job as sysdba to purge the objects in the recycbin.
This is one of the most space cosuming location that often dba's forget to cleanup and the
objects get piled up occupying lot of space. Based on how long you want to save these dropped object setup a job under scheduler to run below plsql block either daily, weekly or monthly.
I suggest to run weekly.
--For user_recycbin purge--
-- plsql --
declare
VSQL varchar2(500);
begin
VSQL:='purge user_recyclebin';
execute immediate VSQL;
dbms_output.put_line('USER RECYCLEBIN has been purged.');
end;
/
--For dba_recycbin purge--
-- plsql --
declare
VSQL varchar2(500);
begin
VSQL:='purge dba_recyclebin';
execute immediate VSQL;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end;
/
Prerequisites
The database object must reside in your own schema or you must have the
DROP
ANY
... system privilege for the type of object to be purged, or you must have the SYSDBA
system privilege. To perform the PURGE
DBA_RECYCLEBIN
operation, you must have the SYSDBA
or PURGE
DBA_RECYCLEBIN
system privilege.
Categories: DBA Blogs
Pluggable database save state -12C
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;
Pluggable database altered.
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL MOUNTED
PDBORCL2 MOUNTED
PDBORCL1 READ WRITE
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
***********************************************
DISCARD PDB SAVED STATE
***********************************************
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;
Pluggable database altered.
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL READ WRITE
PDBORCL2 READ WRITE
PDBORCL1 READ WRITE
SQL> ALTER PLUGGABLE DATABASE pdborcl1 SAVE STATE;
Pluggable database altered.
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> SHUTDOWN IMMEDIATE;
STARTUP;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> SELECT name, open_mode FROM v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDBORCL MOUNTED
PDBORCL2 MOUNTED
PDBORCL1 READ WRITE
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
***********************************************
DISCARD PDB SAVED STATE
***********************************************
SQL> COLUMN con_name FORMAT A20
COLUMN instance_name FORMAT A20
SELECT con_name, instance_name, state FROM dba_pdb_saved_states;SQL> SQL> SQL>
CON_NAME INSTANCE_NAME STATE
-------------------- -------------------- --------------
PDBORCL1 orcl OPEN
SQL> ALTER PLUGGABLE DATABASE pdborcl1 discard state;
Pluggable database altered.
SQL> SELECT con_name, instance_name, state FROM dba_pdb_saved_states;
no rows selected
Categories: DBA Blogs
how to create nested cursor in plsql
select * from empinfo;
"ID" "FNAME" "LNAME" "DEPTNO"
1 "arv" "red" 100
2 "sam" "kum" 200
3 "tom" "com" 100
4 "Hef" "Kom" 200
5 "fam" "ily" 300
select * from deptinfo;
"ID" "DEPTNO" "DEPTNAME" "MGR"
1 100 "HR" "Bill"
2 200 "IT" "Greg"
3 300 "SALES" "Phil"
-- sqlcode starts here
declare
CURSOR CUR1 is select * from empinfo ;
REC1 empinfo%rowtype;
CURSOR CUR2(DID number) is select * from deptinfo where deptno=DID;
REC2 deptinfo%rowtype;
begin
open CUR1 ;
loop
FETCH CUR1 into REC1;
EXIT WHEN CUR1%NOTFOUND;
OPEN CUR2(REC1.deptno);
loop
FETCH CUR2 into REC2;
EXIT WHEN CUR2%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('REC1.id: '||REC1.ID||' belongs to dept ID : '||REC2.DEPTNO||' DEPT :'||REC2.DEPTNAME);
END LOOP;
CLOSE CUR2;
end loop;
close CUR1;
end;
Categories: DBA Blogs
Setup Oracle database using Docker container
Step-1:
Install docker container. Based on your windows OS Version. if you are using windows 7 will need docker engine and Kitematic
if you are on windows 10 or higher use : Docker Community Edition (Download)
Docker Instructions : https://docs.docker.com/install/
Once installed you will see Docker Quickstart Terminal and Kitematic.
open docker machine and then Kitematic.
Search for oracle XE 11g by Seth89. Download and install the below shown container
Once installed You will have oracle database installed and ready to use. you can ignore the below error message /docker-entrypoint-init.d/cache: no such file or dir.
Another way to confirm successful installation is you will see Unauthorized image in web preview section
Make sure you have bridge network configured. As below
Check if below configured ports are configured.
How to connect to oracle database in the container :
Based on the IP Address and port configured in above image. Use those credentials to connect to DB.
Username : System
Password : oracle
Or to connect via sqlplus use as below:
Optional settings :
To configure or change volumes use. Volumes will help you to copy files over or attach a local working directory to container:
Categories: DBA Blogs
Expdp exclude table/exclude schema
expdp export exclude syntax:Here is a simple example on how to use exclude in your export cmds.
----------------------------------------------
Exclude Tables
----------------------------------------------
Using parfile:
First create a parfile with details.
$ vi impdp_full.par
--add the details to parfile
directory=DPUMP logfile=SCOTT_EXP.log dumpfile=SCOTT_%U.dmp parallel=4 EXCLUDE=TABLE:"IN ('TABLE1','TABLE2','TABLE3')
Then execute using parfile:
expdp SCOTT/XXXXXX parfile=impdp_full.par
on cmdline:
expdp SCOTT/XXXXX directory=DPUMP dumpfile=SCOTT_%U.dmp logfile=SCOTT.log schemas=SCOTT parallel=6 EXCLUDE=TABLE:\"IN (\'TABLE1\', \'TABLE2\')\"
----------------------------------------------
Exclude Schemas
----------------------------------------------
First create a parfile with details.
$ vi expdp_full.par
--add the details to parfile
directory=DPUMP FULL=Y dumpfile=FULLDB_%U.dmp logfile=FULL.log parallel=6 EXCLUDE=SCHEMA:"IN ('SCHEMA1','SCHEMA2','SCHEMA3')
Then execute using parfile:
expdp SYSTEM/XXXXXX parfile=expdp_full.par
on cmdline:
expdp SYSTEM/XXXXX directory=DPUMP dumpfile=FULLDB_%U.dmp logfile=FULL.log FULL=Y parallel=6 EXCLUDE=TABLE:\"IN (\'SCHEMA1\', \'SCHEMA2\')\"
Categories: DBA Blogs
ORA-31633: unable to create master table ".SYS_IMPORT_FULL_05"
Today I encountered a problem while importing a schema into my local database. I have exported a schema from ORCL (lets say) using expdp command. I tried to import it to another database and I was getting this error. ORA-31633: unable to create master table
[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges
I tried again and again same error. Then I checked may be it is missing some grants, I havegranted sysdba privillage to SAM now and tried.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant sysdba to SAM;
Lets try again:
[oracle@orcl dpump]$ impdp SAM/SAM directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.
Then I realized that it might be missing create table permission and granted that permission to user SAM.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant create table to SAM;
Grant succeeded.
Now tried again :
[oracle@orcl dpump]$ impdp SAM/SAM directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01": sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Possible Solutions :
[oracle@orcl dpump]$ impdp sam/oracle directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges
I tried again and again same error. Then I checked may be it is missing some grants, I havegranted sysdba privillage to SAM now and tried.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant sysdba to SAM;
Lets try again:
[oracle@orcl dpump]$ impdp SAM/SAM directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 13:59:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-31626: job does not exist
ORA-31633: unable to create master table "SAM.SYS_IMPORT_FULL_05"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT", line 1038
ORA-01031: insufficient privileges.
Then I realized that it might be missing create table permission and granted that permission to user SAM.
SQL*Plus: Release 11.2.0.4.0 Production on Fri Nov 14 14:02:27 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> grant create table to SAM;
Grant succeeded.
Now tried again :
[oracle@orcl dpump]$ impdp SAM/SAM directory=DPUMP dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Import: Release 11.2.0.4.0 - Production on Fri Nov 14 14:02:54 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SAM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SAM"."SYS_IMPORT_SCHEMA_01": sam/******** dumpfile=abc_2014_11_14.dmp logfile=abc_imp.log schemas=sam1,sam2
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PASSWORD_HISTORY
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Possible Solutions :
- It might be a simple missing of create table grant if you are trying to import it through another user.
- Try to check if there is already a table created (SYS_IMP*) with the name oracle is trying to create table.
- Check if the job name being used in session is already created in database.
Categories: DBA Blogs
IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
IMPDP TABLE_EXISTS_ACTION PARAMETER EXPLAINED
Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.
When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.
$ impdp help=y
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.
APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.
TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the CONTENT=DATA_ONLY for the impdp.
Method to Import only rows does not exist in the target table
See some examples here.
In this example lets use abc table in my schema (ATOORPU)
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
I took the data pump dump export EXPDP of employee table.
oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC" 5.921 KB 6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36
oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
------ TABLE_EXISTS_ACTION=SKIP ------
In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.
oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42
------ TABLE_EXISTS_ACTION=APPEND ------
I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.
SQL> delete from employee;
4 rows deleted.
SQL> insert into abc (select * from abc_bak);
4 rows created.
SQL> commit;
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ARVd 70 20-DEC-12
ARVIND2 69 20-DEC-12
ATOORPUe 64 19-DEC-12
BIf 63 19-DEC-12
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42
Now 4 more rows appended to the table. Lets verify that
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ARVd 70 20-DEC-12
ARVIND2 69 20-DEC-12
ATOORPUe 64 19-DEC-12
BIf 63 19-DEC-12
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
8 rows selected.
------ TABLE_EXISTS_ACTION=TRUNCATE ------
Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46
SQL > select * from abc
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
------ TABLE_EXISTS_ACTION=REPLACE ------
This option drop the current table in the database and the import recreate the new table as in the dumpfile.
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07
Now if you check the last_ddl_time for the table it would be the same as the import time.
SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';
OBJECT_NAME CREATED
-------------------------- -------------------
ABC 24-10-2014 09:42:06
Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.
When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.
$ impdp help=y
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.
APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.
TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the CONTENT=DATA_ONLY for the impdp.
Method to Import only rows does not exist in the target table
See some examples here.
In this example lets use abc table in my schema (ATOORPU)
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
I took the data pump dump export EXPDP of employee table.
oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC" 5.921 KB 6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36
oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
------ TABLE_EXISTS_ACTION=SKIP ------
In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.
oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42
------ TABLE_EXISTS_ACTION=APPEND ------
I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.
SQL> delete from employee;
4 rows deleted.
SQL> insert into abc (select * from abc_bak);
4 rows created.
SQL> commit;
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ARVd 70 20-DEC-12
ARVIND2 69 20-DEC-12
ATOORPUe 64 19-DEC-12
BIf 63 19-DEC-12
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42
Now 4 more rows appended to the table. Lets verify that
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ARVd 70 20-DEC-12
ARVIND2 69 20-DEC-12
ATOORPUe 64 19-DEC-12
BIf 63 19-DEC-12
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
8 rows selected.
------ TABLE_EXISTS_ACTION=TRUNCATE ------
Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46
SQL > select * from abc
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
------ TABLE_EXISTS_ACTION=REPLACE ------
This option drop the current table in the database and the import recreate the new table as in the dumpfile.
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07
Now if you check the last_ddl_time for the table it would be the same as the import time.
SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';
OBJECT_NAME CREATED
-------------------------- -------------------
ABC 24-10-2014 09:42:06
Categories: DBA Blogs
In-Database Archiving in Oracle Database - Data Archiving in 12C
In-Database Archiving in Oracle Database 12c - Data Archiving in 12C
Rather than deleting data physically, some applications have a concept of "mark for delete" logical delete, so the data remains present in the table, but is not visible to the application. This can be achieved by doing the following.
Add an extra column to the relevant tables that holds a flag to indicate the data is deleted.
Add an extra predicate to every statement that checks the deleted status, like "WHERE deleted = 'N'", to exclude the deleted rows from the SQL. The predicate can be hard coded into the SQL, or applied dynamically using a security policy, like in Virtual Private Database (VPD).
In-Database Archiving is a feature added to Oracle Database 12c to allow this type of "mark for delete" functionality out-of-the-box, with fewer changes to the existing application code.
Enable In-Database Archiving
Archiving (Deleting) Rows
Displaying Archived Rows
Enable In-Database Archiving
The ROW ARCHIVAL clause is used to enable in-database archiving. It can be used during table creation as part of the CREATE TABLE command, or after table creation using the ALTER TABLE command.
DROP TABLE ARCH_TEST PURGE;
-- Create the table with in-database with archiving of data enabled.
CREATE TABLE ARCH_TEST (
id NUMBER,
description VARCHAR2(50),
CONSTRAINT ARCH_TEST_pk PRIMARY KEY (id)
)
ROW ARCHIVAL;
-- Insert data into TEST TABLE ARCH_TEST
declare
n number;
VSQL Varchar(2000);
--i number:=1;
begin
for n in 1..750
loop
-- n := n+1;
VSQL:='insert into ARCH_TEST (id ,DESCRIPTION) values ('||n||',''VALUE OF N IS '||n||''')';
-- DBMS_OUTPUT.PUT_LINE(VSQL);
Execute Immediate VSQL;
end loop;
commit;
end;
/
-- Check the contents of the table.
COUNT(*)
----------
750
COLUMN column_name FORMAT A20
COLUMN data_type FORMAT A20
SELECT column_id,
column_name,
data_type,
data_length,
hidden_column
FROM user_tab_cols
WHERE table_name = 'ARCH_TEST'
ORDER BY column_id;
COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HID
---------- -------------------- -------------------- ----------- ---
1 ID NUMBER (22) NO
2 DESCRIPTION VARCHAR2 (50) NO
ORA_ARCHIVE_STATE VARCHAR2 (4000) YES
By default, this column is populated with the value '0' for each row.
COLUMN ora_archive_state FORMAT A20
SELECT ora_archive_state, COUNT(*)
FROM ARCH_TEST
GROUP BY ora_archive_state
ORDER BY ora_archive_state;
ORA_ARCHIVE_STATE COUNT(*)
-------------------- ----------
0 750
-- Disable, the re-enable in-database archiving.
ALTER TABLE ARCH_TEST NO ROW ARCHIVAL;
ALTER TABLE ARCH_TEST ROW ARCHIVAL;
************************************************************Archiving (Deleting) Rows************************************************************
Rather than deleting unneeded rows, update the ORA_ARCHIVE_STATE system generated hidden column with the value '1'. This will make the rows invisible to your applications.
UPDATE ARCH_TEST
SET ora_archive_state = '1'
WHERE id BETWEEN 550 and 750;
COMMIT;
SELECT COUNT(*) FROM ARCH_TEST;
COUNT(*)
----------
549
We can actually set ORA_ARCHIVE_STATE column to any string value other than '0' to archive the data, but the DBMS_ILM package uses the following constants.
ARCHIVE_STATE_ACTIVE='0'
ARCHIVE_STATE_ARCHIVED='1'
SQL>
************************************************************
Displaying Archived Rows
************************************************************
The hidden rows can be made visible to a session by setting ROW ARCHIVAL VISIBILITY to the value ALL. Setting it back to ACTIVE makes the rows invisible again.
-- Make archived rows visible.
ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ALL;
SELECT COUNT(*) FROM ARCH_TEST;
COUNT(*)
----------
750
SQL>
COLUMN ora_archive_state FORMAT A20
SELECT ora_archive_state, COUNT(*)
FROM ARCH_TEST
GROUP BY ora_archive_state
ORDER BY ora_archive_state;
ORA_ARCHIVE_STATE COUNT(*)
-------------------- ----------
0 549
1 201
2 rows selected.
SQL>
-- Make archived rows invisible again.ALTER SESSION SET ROW ARCHIVAL VISIBILITY = ACTIVE;
SELECT COUNT(*) FROM ARCH_TEST;
COUNT(*)
----------
549
SQL>
Categories: DBA Blogs
TNS-00583: Valid node checking: unable to parse configuration parameters
TNS-12560: TNS:protocol adapter error TNS-00583: Valid node checking: unable to parse configuration parametersI Received following errors when trying to startup my listener. I verified the listener.ora and sqlnet.ora files and everything seemed to look normal.
[oracle@Linux03 encryption_keystore]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
[oracle@Linux03 encryption_keystore]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:07:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
I had below line in my sqlnet.ora file.
[oracle@Linux03 admin]$ cat sqlnet.ora_bak
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)
Solution (in my case): Removing the ENCRYPTION_WALLET_LOCATION info did the trick for me.
[oracle@Linux03 encryption_keystore]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:31:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 12-JUL-2016 10:31:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
[oracle@Linux03 encryption_keystore]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:05:32
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
[oracle@Linux03 encryption_keystore]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:07:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-12560: TNS:protocol adapter error
TNS-00583: Valid node checking: unable to parse configuration parameters
Listener failed to start. See the error message(s) above...
I had below line in my sqlnet.ora file.
[oracle@Linux03 admin]$ cat sqlnet.ora_bak
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
ENCRYPTION_WALLET_LOCATION =
(SOURCE =(METHOD = FILE)(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)
Solution (in my case): Removing the ENCRYPTION_WALLET_LOCATION info did the trick for me.
[oracle@Linux03 encryption_keystore]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 12-JUL-2016 10:31:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 12-JUL-2016 10:31:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
Categories: DBA Blogs
Steps to rebuild of existing standby database after failover
Steps to quickly rebuild of existing standby database:
There are situations where you will have to rebuild your existing standby database as a result of various situations like primary db was restored from backup with open reset logs.
1. Disable log shipping to standby database (that you want to rebuild "alter system set log_archive_dest_state_2=defer").
2. Take full bakup from PRIMARY DB.
3. Take standby controlfile backup.
4. Copy backup and standby control file to standby server.
5. Drop datalafiles and controlfiles on standby Database.
6. Copy new standby control files to all controlfile locations.
7. Mount standby Database
8. Restore standby database.
8. Enable log shipping to standby database(alter system set log_archive_dest_state_2=enable).
9. Recover managed standby database (on standby).
There are situations where you will have to rebuild your existing standby database as a result of various situations like primary db was restored from backup with open reset logs.
1. Disable log shipping to standby database (that you want to rebuild "alter system set log_archive_dest_state_2=defer").
2. Take full bakup from PRIMARY DB.
3. Take standby controlfile backup.
4. Copy backup and standby control file to standby server.
5. Drop datalafiles and controlfiles on standby Database.
6. Copy new standby control files to all controlfile locations.
7. Mount standby Database
8. Restore standby database.
8. Enable log shipping to standby database(alter system set log_archive_dest_state_2=enable).
9. Recover managed standby database (on standby).
Categories: DBA Blogs
Create Temporary Tables in Oracle
Global Temporary Tables in Oracle
Temporary tables are useful in applications where a result set is to be buffered, perhaps because it is constructed by running multiple DML operations. For example, consider the following:
A Web-based airlines reservations application allows a customer to create several optional itineraries. Each itinerary is represented by a row in a temporary table. The application updates the rows to reflect changes in the itineraries. When the customer decides which itinerary she wants to use, the application moves the row for that itinerary to a persistent table.
During the session, the itinerary data is private. At the end of the session, the optional itineraries are dropped.
This statement creates a temporary table that is transaction specific:
NOTE : Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
*********************************************************************************
HERE is an example to create a global temporary table with on commit DELETE ROWS :
*********************************************************************************
sql> CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT DELETE ROWS;
sql> insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');
1 row inserted.
sql> select * from ADMIN_WORK_AREA;
commit;
Commit complete.
sql> select * from ADMIN_WORK_AREA;
NOTE: records in this temp table will be deleted upon commit. This is equivalent to truncating table on commit.
*********************************************************************************
HERE is an example to create a global temporary table with on commit PRESERVE ROWS :
*********************************************************************************
sql> CREATE GLOBAL TEMPORARY TABLE admin_work_area
(startdate DATE,
enddate DATE,
class CHAR(20))
ON COMMIT PRESERVE ROWS;
sql> insert into ADMIN_WORK_AREA values (sysdate,sysdate+ 1,'A');
1 row inserted.
1 row inserted.
sql> select * from ADMIN_WORK_AREA;
commit;
Commit complete.
sql> select * from ADMIN_WORK_AREA;
NOW exit the session and login back and select the table.
sql> select * from ADMIN_WORK_AREA;
table is empty
NOTE: records (rows) in this temp table will be deleted upon session exit only, as long as you are using same session you can see these rows.
This is equivalent to truncating table on session exit.
Categories: DBA Blogs
Inserting Data into table with DML Error Logging (catching errors whiles inserting data into table)
Inserting Data with DML Error Logging:
When you load a table using an INSERT statement with sub query, if an error occurs, the statement is terminated and rolled back in its entirety. This can be wasteful of time and system resources. For such INSERT statements, you can avoid this situation by using the DML error logging feature.
To use DML error logging, you add a statement clause that specifies the name of an error logging table into which the database records errors encountered during DML operations. When you add this error logging clause to the INSERT statement, certain types of errors no longer terminate and roll back the statement. Instead, each error is logged and the statement continues. You then take corrective action on the erroneous rows at a later time.
DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements. This section focuses on INSERT statements.
--------------------------------------------------------
-- DDL for Table ATEST1
--------------------------------------------------------
CREATE TABLE "ATOORPU"."ATEST1"
( "ID" NUMBER constraint ATEST1_PK PRIMARY KEY,
"TDATE" DATE,
"AMOUNT" VARCHAR2(20 BYTE),
"ORD_NO" NUMBER
) ;
--------------------------------------------------------
INSERT SOME VALUES INTO TEST TABLE
--------------------------------------------------------
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (1,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (2,to_date('04-APR-16','DD-MON-RR'),null,300);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (3,to_date('01-MAR-16','DD-MON-RR'),null,100);
Insert into ATEST1 (ID,TDATE,AMOUNT,ORD_NO) values (4,to_date('01-MAR-16','DD-MON-RR'),'100',200);
--------------------------------------------------------
CREATE ERROR LOG TABLE USING THE DBMS PACKAGE :
--------------------------------------------------------
EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG('ATEST1', 'ERR_ATEST1'); -- ATEST1 source table and ERR_ATEST1 error log table
Error Logging Restrictions and Caveats:
- Oracle Database logs the following errors during DML operations:
- Column values that are too large
- Constraint violations (NOT NULL, unique, referential, and check constraints)
- Errors raised during trigger execution
- Errors resulting from type conversion between a column in a subquery and the corresponding column of the table
- Partition mapping errors
--------------------------------------------------------
-- This will generate some insert errors
--------------------------------------------------------
INSERT INTO ATEST1
SELECT ID+3,TDATE,AMOUNT,ORD_NO
FROM ATEST1
WHERE id > 1
LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;
--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.
Note:
If the statement exceeds the reject limit and rolls back, the error logging table retains the log entries recorded so far.
--------------------------------------------------------
-- This will generate some update errors
--------------------------------------------------------
update ATEST1 set ID=3 where ID>5 LOG ERRORS INTO ERR_ATEST1 ('daily_load') REJECT LIMIT 9;
--- daily_load is TAG, REJECT LIMT will set the max errs before terminating insert statement.
--------------------------------------------------------
LETS CHECK THE ERROR MESSAGES RECORDED:
--------------------------------------------------------
select * from ERR_ATEST1;
Categories: DBA Blogs
USING SELECT 'X' in query/sub-queries
USING SELECT 'X' in query/sub-queries.
--------------------------------------------------------
-- DDL for Table TAB1
--------------------------------------------------------
CREATE TABLE "ATEST"."TAB1"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Insert into ATEST.TAB1 (ID,NAME) values (1,'AAA');
Insert into ATEST.TAB1 (ID,NAME) values (2,'BBB');
Insert into ATEST.TAB1 (ID,NAME) values (3,'EEE');
Insert into ATEST.TAB1 (ID,NAME) values (4,'FFF');
--------------------------------------------------------
-- DDL for Table TAB2
--------------------------------------------------------
CREATE TABLE "ATEST"."TAB2"
( "ID" NUMBER,
"NAME" VARCHAR2(20 BYTE)
) ;
Insert into ATEST.TAB2 (ID,NAME) values (1,'CCC');
Insert into ATEST.TAB2 (ID,NAME) values (2,'DDD');
Get records that exits in TAB1 and not in TAB2 using select 'X' :
select * from TAB1 f where not exists (select 'X' from TAB2 where id=f.id);
ID NAME
-- ----
4 FFF
3 EEE
IN the above query we get output of all the records from TAB1 that doesnt match with TAB2 ID's.
Hence we do not get the records with ID's 1 & 2 as they only exits in TAB1.
This is just like using "select * from TAB1 f where not exists (select ID from TAB2 where id=f.id);"
Get records that exits in TAB1 and in TAB2 using select 'X' :
select * from TAB1 f where exists (select 'X' from TAB2 where id=f.id);
ID NAME
-- ----
1 AAA
2 BBB
IN the above query we get output of all the records from TAB1 that exist with same ID in TAB2 .
Hence we get only records with ID 1 & 2 as they exists in both TABLES.
This is just like using "select * from TAB1 f where exists (select ID from TAB2 where id=f.id);"
Categories: DBA Blogs
validate_con_names: PDB$SEED is not open catconInit: Unexpected error returned by validate_con_names Unexpected error encountered in catconInit; exiting
[oracle@Linux03 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d
$ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to /tmp/utl32k_output_catcon_19470.lst
catcon: See /tmp/utl32k_output*.log files for output generated by scripts
catcon: See /tmp/utl32k_output_*.lst files for spool files, if any
validate_con_names: PDB$SEED is not open
catconInit: Unexpected error returned by validate_con_names
Unexpected error encountered in catconInit; exiting
[oracle@Linux03 admin]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:27:06 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> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED MOUNTED
PDBORCL MIGRATE
PDBORCL2 MIGRATE
PDBORCL1 MIGRATE
SQL> startup;
ORA-01081: cannot start already-running ORACLE - shut it down first
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
CHECK THE STATUS OF PLUGGABLE DATABASE.
SQL> STARTUP;
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED MOUNTED
PDBORCL MOUNTED
PDBORCL2 MOUNTED
PDBORCL1 MOUNTED
WE NEED TO START PDB$SEED PLUGGABLE DATABASE in UPGRADE STATE FOR THAT
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE;
Pluggable database altered.
SQL> select name,open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED MIGRATE
PDBORCL MIGRATE
PDBORCL2 MIGRATE
PDBORCL1 MIGRATE
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@Linux03 admin]$ pwd
/u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin
[oracle@Linux03 admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -d
$ORACLE_HOME/rdbms/admin -b /tmp/utl32k_output utl32k.sql
catcon: ALL catcon-related output will be written to /tmp/utl32k_output_catcon_19826.lst
catcon: See /tmp/utl32k_output*.log files for output generated by scripts
catcon: See /tmp/utl32k_output_*.lst files for spool files, if any
catcon.pl: completed successfully
Categories: DBA Blogs
ALTER SYSTEM SET max_string_size=extended ORA-02097: parameter cannot be modified because specified value is invalid
MAX_STRING_SIZE PARAMETER:
The extended data types functionality is controlled using the MAX_STRING_SIZE initialization parameter. The default value is STANDARD, which restricts the maximum sizes to the traditional lengths. Setting the parameter value to EXTENDED allows for the new maximum lengths.
This is a pre-requisite for enabling Extended Data Types in Oracle Database 12c. Which helps us to increase the max size of varchar data type from 4000 bytes to 32767 bytes.
[oracle@Linux03 ~]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Apr 21 09:10:02 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> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP UPGRADE;
ALTER SYSTEM SET max_string_size=extended;
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SQL> ALTER SYSTEM SET max_string_size=extended;
ALTER SYSTEM SET max_string_size=extended
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL> alter system set max_string_size=EXTENDED scope=spfile;
System altered.
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string STANDARD
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 2929352 bytes
Variable Size 541068600 bytes
Database Buffers 239075328 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show parameter max_string_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_string_size string EXTENDED
Categories: DBA Blogs
CREATE ORACLE DATABASE SERVICES/SERVICE_NAME
dbms_service.create_service : We can call dbms_service.create_service procedure to create new service names , then start these service names for user connections. The procedure dbms_service.create_service requires the service name and service network name, use the service name to manage the service name.
oracle@LINUX201:[~] $ sqlplus /"As sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
************************************************************LEST CREATE A NEW DB SERVICE
SAMPLE :
BEGIN
DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',
network_name=>'QPDEV.WORLD'); (or) network_name=>'QPDEV');
END;
/
SQL> begin
dbms_service.create_service('ORCLTEST','ORCLTEST');
end;
/
PL/SQL procedure successfully completed.
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
SQL> begin
DBMS_SERVICE.START_SERVICE('ORCL');
end;
/ 2 3 4
PL/SQL procedure successfully completed.
************************************************************CHECK IF THE SERVICE IS CREATED AND STARTED************************************************************
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
7 ORCLTEST
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls
samples shrept.lst tnsnames.ora
************************************************************LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201.world.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLTEST)
)
)
************************************************************LETS TEST IT :
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))
OK (10 msec)
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
You can also use more advanced features like failover parameters like belowExamples
DBMS_SERVICE.CREATE_SERVICE('ORCL','ORCL.WORLD');
DECLARE
params dbms_service.svc_parameter_array;
BEGIN
params('FAILOVER_TYPE') :='TRANSACTION';
params('REPLAY_INITIATION_TIMEOUT'):=1800;
params('RETENTION_TIMEOUT') :=86400;
params('FAILOVER_DELAY') :=10;
params('FAILOVER_RETRIES') :=30;
params('commit_outcome') :='true';
params('aq_ha_notifications') :='true';
DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
END;
oracle@LINUX201:[~] $ sqlplus /"As sysdba"
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:17:50 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
************************************************************LEST CREATE A NEW DB SERVICE
************************************************************
BEGIN
DBMS_SERVICE.CREATE_SERVICE(service_name=>'QPDEV',
network_name=>'QPDEV.WORLD'); (or) network_name=>'QPDEV');
END;
/
SQL> begin
dbms_service.create_service('ORCLTEST','ORCLTEST');
end;
/
PL/SQL procedure successfully completed.
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
SQL> begin
DBMS_SERVICE.START_SERVICE('ORCL');
end;
/ 2 3 4
PL/SQL procedure successfully completed.
************************************************************CHECK IF THE SERVICE IS CREATED AND STARTED************************************************************
SQL> select SERVICE_ID,name from V$SERVICES;
SERVICE_ID NAME
---------- ----------------------------------------------------------------
7 ORCLTEST
4 ORCL
1 SYS$BACKGROUND
2 SYS$USERS
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@LINUX201:[~] $ cd $ORACLE_HOME/network/admin
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ ls
samples shrept.lst tnsnames.ora
************************************************************LETS ADD THIS NEW SERVICE IN TO TNSNAMES FILE:
************************************************************
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201.world.com)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCLTEST)
)
)
************************************************************LETS TEST IT :
************************************************************
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ tnsping ORCLTEST
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 05-APR-2016 13:21:31
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = LINUX201)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCLTEST)))
OK (10 msec)
oracle@LINUX201:[/u01/app/oracle/product/11.2.0/db_1/network/admin] $ sqlplus atoorpu@ORCLTEST
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 5 13:21:46 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter password:
You can also use more advanced features like failover parameters like belowExamples
DBMS_SERVICE.CREATE_SERVICE('ORCL','ORCL.WORLD');
DECLARE
params dbms_service.svc_parameter_array;
BEGIN
params('FAILOVER_TYPE') :='TRANSACTION';
params('REPLAY_INITIATION_TIMEOUT'):=1800;
params('RETENTION_TIMEOUT') :=86400;
params('FAILOVER_DELAY') :=10;
params('FAILOVER_RETRIES') :=30;
params('commit_outcome') :='true';
params('aq_ha_notifications') :='true';
DBMS_SERVICE.MODIFY_SERVICE('GOLD',params);
END;
Categories: DBA Blogs
Restricting database access to Hostname or IP addeess
**************************************************************
C:\Windows\System32>tnsping pdborcl1
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)
**************************************************************
NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :
**************************************************************
ON DB SERVER:
oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tcp.validnode_checking = yes ## I have just added this line to test if it works
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-00584: Valid node checking configuration error
TNS-12560: TNS:protocol adapter error
As expected it doesn't work as listener needs to know where to accept or deny connections from.
Now I tweak the sqlnet.ora file to below
[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03) ## I only want to accept connections from my localhost i.e Linux03
RESTART THE LISTENER NOW.
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
(OR)
[oracle@Linux03 admin]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 07-MAR-2016 15:28:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
****************************************************
NOW I TRY TO CONNECT FROM CLIENT AGAIN :
****************************************************
C:\Windows\System32>tnsping pdborcl1
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
TNS-12537: TNS:connection closed
C:\Windows\System32>
Nope it doesn't allow me to connect.
*************************************************************
Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see
**************************************************************
[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
C:\Windows\System32>tnsping pdborcl1
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)
**************************************************************************
SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER
*************************************************************************
tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)
YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :
tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)
FROM MY CLIENT MACHINE:
**************************************************************TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:20
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)
**************************************************************
NOW I want my DB to be only be accessible from this machine and restrict all other. For this I will need to make changed to my DB server sqlnet.ora :
**************************************************************
ON DB SERVER:
oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tcp.validnode_checking = yes ## I have just added this line to test if it works
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:20:03
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
TNS-00584: Valid node checking configuration error
TNS-12560: TNS:protocol adapter error
As expected it doesn't work as listener needs to know where to accept or deny connections from.
Now I tweak the sqlnet.ora file to below
[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03) ## I only want to accept connections from my localhost i.e Linux03
RESTART THE LISTENER NOW.
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:21:06
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
(OR)
[oracle@Linux03 admin]$ lsnrctl stop
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:36
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ lsnrctl start
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:28:41
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Starting /u01/app/oracle/product/12.1.0.2/db_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.2.0 - Production
System parameter file is /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date 07-MAR-2016 15:28:41
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/Linux03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Linux03)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
****************************************************
NOW I TRY TO CONNECT FROM CLIENT AGAIN :
****************************************************
C:\Windows\System32>tnsping pdborcl1
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:57
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
TNS-12537: TNS:connection closed
C:\Windows\System32>
Nope it doesn't allow me to connect.
*************************************************************
Lets try to remove the tcp.validnode_checking & tcp.invited_nodes and see
**************************************************************
[oracle@Linux03 admin]$ cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0.2/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:31:58
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
[oracle@Linux03 admin]$ vi sqlnet.ora
[oracle@Linux03 admin]$ lsnrctl reload
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 07-MAR-2016 15:32:16
Copyright (c) 1991, 2014, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Linux03)(PORT=1521)))
The command completed successfully
C:\Windows\System32>tnsping pdborcl1
TNS Ping Utility for 32-bit Windows: Version 11.2.0.1.0 - Production on 07-MAR-2016 15:32:36
Copyright (c) 1997, 2010, Oracle. All rights reserved.
Used parameter files:
C:\app\oracle\product\11.2.0\client_1\network\admin\sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.215.XX4.XX)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = pdbo
cl1)))
OK (0 msec)
**************************************************************************
SUCCESS YOU HAVE SUCCESFULLY IMPLEMENTED ANOTHER LAYER OF SECURITY ON DB SERVER
*************************************************************************
tcp.validnode_checking = yes
tcp.invited_nodes = (Linux03,HOSTNAME1,HOSTNAME2 ...)
YOU CAN ALSO EXPLICITLY EXCLUDE NODE LIKE THIS :
tcp.excluded_nodes = (192.168.100.101,HOSTNAME2,)
Categories: DBA Blogs
PDB ADMIN account created while Pluggable database creation doesn't have DBA level privs
PDB ADMIN ACCOUNT DOESN'T HAVE PERMISSIONS TO CREATE/DROP by DEFAULT unless explicitly granted:
********************* ******************************
pdborcl1 is the user created while creating PDB via DBCA:
********************* ******************************
[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1/oracle@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:36:23 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Mar 07 2016 11:21:22 -06:00
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> select table_name from tabs;
no rows selected
SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> exit
***************************** ******************************
NOW LOGIN AS USER pdborcl1_usr2 who has a table created in his user
***************************** ******************************
[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:39:21 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Mar 07 2016 11:46:08 -06:00
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> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
********************* ******************************
pdborcl1 is the user created while creating PDB via DBCA:
********************* ******************************
[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1/oracle@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:36:23 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Mar 07 2016 11:21:22 -06:00
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> select table_name from tabs;
no rows selected
SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> exit
***************************** ******************************
NOW LOGIN AS USER pdborcl1_usr2 who has a table created in his user
***************************** ******************************
[oracle@Linux03 pdborcl3]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 14:39:21 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Mar 07 2016 11:46:08 -06:00
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> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
Categories: DBA Blogs
CREATE LOCAL USERS IN 12C DATABASE
******************** ********************************** **************
CREATE LOCAL USERS IN 12C PDB DATABASE:
******************** ********************************** **************
When connected to a multi-tenant database the management of users and privileges is a little different to traditional Oracle environments. In multi-tenant environments there are two types of user.
Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Likewise, there are two types of roles.
******************** **************
Create Local Users
******************** **************
When creating a local user the following requirements must all be met.
You must be connected to a user with the CREATE USER privilege.
The username for the local user must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
The following example shows how to create local users with and without the CONTAINER clause from the root container.
CONN / AS SYSDBA
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdborcl1;
-- Create the local user using the CONTAINER clause.
CREATE USER pdborcl1_usr1 IDENTIFIED BY pdborcl1_usr1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO pdborcl1_usr1 CONTAINER=CURRENT;
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr1/pdborcl1_usr1@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:52:50 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 option
SQL> sho user
USER is "PDBORCL1_USR1"
******************** ------ ******************** ----- **************
-- Connect to a privileged user in the PDB.
******************** ------ ******************** ----- **************
CONN system/password@pdb1
-- Create the local user using the default CONTAINER setting.
SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
SQL> GRANT CREATE SESSION TO pdborcl1_usr2;
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:56:29 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
SQL> show user
USER is "PDBORCL1_USR2"
If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.
This dba role will be only specific to prdborcl1 local PDB database.
SQL> grant pdb_dba to PDBORCL1_USR2;
Grant succeeded.
SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
As per above doc (https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG4414) PDB_DBA Granted automatically to the local user that is created when you create a new PDB from the seed PDB. No privileges are provided with this role.
IF I try to use a dba user in pdborcl1 and try to connect onto pdborcl2. It wont work
oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:13:09 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
NOW I LOGGED IN AS SUPER USER AND GRANTED DBA TO PDBORCL1_USR2;
[oracle@Linux03 ~]$ sqlplus /"as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:32:01 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> ALTER SESSION SET CONTAINER = pdborcl1 ;
Session altered.
SQL> grant dba to PDBORCL1_USR2;
connect back as PDBORCL1_USR2 user and tried to create table :
SQL> show con_name
CON_NAME
------------------------------
PDBORCL1
SQL> show user
USER is "PDBORCL1_USR2"
SQL> create table test (name varchar2(20));
Table created.
SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
SQL> insert into test values ('arvind') ;
1 row created.
SQL> commit;
SQL> select * from test;
NAME
--------------------
arvind
SQL> create view test1 as select * from test;
View created.
Note: I was able to have a great deal of priv as dba but only till "pdborcl1". I still do not have any priv on "prdorcl2" or any other DB's in CDB.
~~~~~~~~~~~~~
NOW LETS TRY LOGGEING INTO PDBORCL2 (Nope still doest work), as this dba role has been granted to pdborcl1_usr2 only for PDB "pdborcl1"
~~~~~~~~~~~~~
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:35:11 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
*************************** **********************************
Now lets create same user in OTHER PDB "PDBORCL2"
*************************** **********************************
[oracle@Linux03 ~]$ sqlplus /"as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:48:56 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> ALTER SESSION SET CONTAINER = pdborcl2;
Session altered.
SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
User created.
SQL> grant CREATE SESSION,dba to pdborcl1_usr2;
Grant succeeded.
SQL> conn pdborcl1_usr2/pdborcl1_usr2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:50:33 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
------------------------------
PDBORCL2
SQL> show user
USER is "PDBORCL1_USR2"
SQL> select username from dba_users where username like 'PDB%';
USERNAME
--------------------------------------------------------------------------------
PDBORCL2
PDBORCL1_USR2
Conclusion :
1) You can have same users with same username and pass in two diff PDB's as local user.Yet they have no permissions on each other.
2) Youe will need CDB_DBA -- it provides the privileges required for administering a CDB, such as SET CONTAINER, SELECT ON PDB_PLUG_IN_VIOLATIONS, and SELECT ON CDB_LOCAL_ADMIN_PRIVS
CREATE LOCAL USERS IN 12C PDB DATABASE:
******************** ********************************** **************
When connected to a multi-tenant database the management of users and privileges is a little different to traditional Oracle environments. In multi-tenant environments there are two types of user.
Common User : The user is present in all containers (root and all PDBs).
Local User : The user is only present in a specific PDB. The same username can be present in multiple PDBs, but they are unrelated.
Likewise, there are two types of roles.
******************** **************
Create Local Users
******************** **************
When creating a local user the following requirements must all be met.
You must be connected to a user with the CREATE USER privilege.
The username for the local user must not be prefixed with "C##" or "c##".
The username must be unique within the PDB.
You can either specify the CONTAINER=CURRENT clause, or omit it, as this is the default setting when the current container is a PDB.
The following example shows how to create local users with and without the CONTAINER clause from the root container.
CONN / AS SYSDBA
-- Switch container while connected to a common user.
ALTER SESSION SET CONTAINER = pdborcl1;
-- Create the local user using the CONTAINER clause.
CREATE USER pdborcl1_usr1 IDENTIFIED BY pdborcl1_usr1 CONTAINER=CURRENT;
GRANT CREATE SESSION TO pdborcl1_usr1 CONTAINER=CURRENT;
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr1/pdborcl1_usr1@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:52:50 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 option
SQL> sho user
USER is "PDBORCL1_USR1"
******************** ------ ******************** ----- **************
-- Connect to a privileged user in the PDB.
******************** ------ ******************** ----- **************
CONN system/password@pdb1
-- Create the local user using the default CONTAINER setting.
SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
SQL> GRANT CREATE SESSION TO pdborcl1_usr2;
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl1
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 10:56:29 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
SQL> show user
USER is "PDBORCL1_USR2"
If a local user is to be used as a DBA user, it requires the PDB_DBA role granted locally to it.
This dba role will be only specific to prdborcl1 local PDB database.
SQL> grant pdb_dba to PDBORCL1_USR2;
Grant succeeded.
SQL> create table test (name varchar2(20));
create table test (name varchar2(20))
*
ERROR at line 1:
ORA-01031: insufficient privileges
As per above doc (https://docs.oracle.com/database/121/DBSEG/authorization.htm#DBSEG4414) PDB_DBA Granted automatically to the local user that is created when you create a new PDB from the seed PDB. No privileges are provided with this role.
IF I try to use a dba user in pdborcl1 and try to connect onto pdborcl2. It wont work
oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:13:09 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
NOW I LOGGED IN AS SUPER USER AND GRANTED DBA TO PDBORCL1_USR2;
[oracle@Linux03 ~]$ sqlplus /"as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:32:01 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> ALTER SESSION SET CONTAINER = pdborcl1 ;
Session altered.
SQL> grant dba to PDBORCL1_USR2;
connect back as PDBORCL1_USR2 user and tried to create table :
SQL> show con_name
CON_NAME
------------------------------
PDBORCL1
SQL> show user
USER is "PDBORCL1_USR2"
SQL> create table test (name varchar2(20));
Table created.
SQL> select table_name from tabs;
TABLE_NAME
--------------------------------------------------------------------------------
TEST
SQL> insert into test values ('arvind') ;
1 row created.
SQL> commit;
SQL> select * from test;
NAME
--------------------
arvind
SQL> create view test1 as select * from test;
View created.
Note: I was able to have a great deal of priv as dba but only till "pdborcl1". I still do not have any priv on "prdorcl2" or any other DB's in CDB.
~~~~~~~~~~~~~
NOW LETS TRY LOGGEING INTO PDBORCL2 (Nope still doest work), as this dba role has been granted to pdborcl1_usr2 only for PDB "pdborcl1"
~~~~~~~~~~~~~
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:35:11 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
*************************** **********************************
Now lets create same user in OTHER PDB "PDBORCL2"
*************************** **********************************
[oracle@Linux03 ~]$ sqlplus /"as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:48:56 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> ALTER SESSION SET CONTAINER = pdborcl2;
Session altered.
SQL> CREATE USER pdborcl1_usr2 IDENTIFIED BY pdborcl1_usr2;
User created.
SQL> grant CREATE SESSION,dba to pdborcl1_usr2;
Grant succeeded.
SQL> conn pdborcl1_usr2/pdborcl1_usr2
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> exit
[oracle@Linux03 ~]$ sqlplus pdborcl1_usr2/pdborcl1_usr2@pdborcl2
SQL*Plus: Release 12.1.0.2.0 Production on Mon Mar 7 11:50:33 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
------------------------------
PDBORCL2
SQL> show user
USER is "PDBORCL1_USR2"
SQL> select username from dba_users where username like 'PDB%';
USERNAME
--------------------------------------------------------------------------------
PDBORCL2
PDBORCL1_USR2
Conclusion :
1) You can have same users with same username and pass in two diff PDB's as local user.Yet they have no permissions on each other.
2) Youe will need CDB_DBA -- it provides the privileges required for administering a CDB, such as SET CONTAINER, SELECT ON PDB_PLUG_IN_VIOLATIONS, and SELECT ON CDB_LOCAL_ADMIN_PRIVS
Categories: DBA Blogs