[1] PFILE
[2] SPFILE
[3] orapwfile
[5] sqlnet.ora
Oracle configuration files are:
[1] PFILE
[2] SPFILE
[3] orapwfile,
[5] sqlnet.ora and
[4] listener.ora,
[6] tnsnames.ora
Now in a container database you can change a parameter in CDB or PDB level.
So you can have the same parameter with different values on multiple PDBs.
It's mandatory to understand those differences
Some items that I recommend studying and memorizing:
The basic parameters to start a instance (like the ones that DBCA set
during a typical database creation)
The parameters to set up a RAC.
The parameters to set up a Data Guard.
The parameters that I already commented in other topics, like the memory ones.
How to create spfile from pfile and vice-versa. (I hope you already know that)
How to check session, instance and spfile current parameter values.
V$PARAMETER Displays information about the initialization parameters that
are currently in effect for the session.
SQL> desc v$parameter
Name Type
---------------------------------------------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(4000)
DISPLAY_VALUE VARCHAR2(4000)
DEFAULT_VALUE VARCHAR2(255) >> Default value for this parameter.
ISDEFAULT VARCHAR2(9)
ISSES_MODIFIABLE VARCHAR2(5) >> Can be changed with ALTER SESSION (TRUE) or not (FALSE).
ISSYS_MODIFIABLE VARCHAR2(9) >> Can changed with ALTER SYSTEM and when the change takes effect:
ISPDB_MODIFIABLE VARCHAR2(5) >>> Can be modified inside a PDB (TRUE) or not (FALSE).
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(10)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
ISBASIC VARCHAR2(5)
DESCRIPTION VARCHAR2(255) >>> Description of the parameter
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER
CON_ID NUMBER >>>>Container ID
V$SYSTEM_PARAMETER A new session inherits parameter values from
the instance-wide values displayed by the V$SYSTEM_PARAMETER view.
# Total parameters
SQL> SELECT COUNT(*) FROM V$PARAMETER;
COUNT(*)
----------
381
SQL> SELECT CON_ID,COUNT(*) FROM V$PARAMETER
2 GROUP BY CON_ID;
CON_ID COUNT(*)
---------- ----------
1 381
SQL> select count(*) from V$SYSTEM_PARAMETER;
COUNT(*)
----------
387
SQL> SELECT COUNT(*) FROM V$PARAMETER2;
COUNT(*)
----------
384
SQL> select name,value from v$parameter2 where ORDINAL=2;
NAME VALUE
------------------------- --------------------------------------------------
control_files /u01/app/oracle/fast_recovery_area/orcl12c/control
02.ctl
local_listener LISTENER1523
connection_brokers ((TYPE=EMON)(BROKERS=1))
SQL> select name,value from v$parameter2 where ORDINAL=3;
no rows selected
SQL> select count(*) from V$SYSTEM_PARAMETER2;
COUNT(*)
----------
390
#To find an non default initialization parameters
SQL> SELECT ISDEFAULT,COUNT(*) FROM V$PARAMETER GROUP BY ISDEFAULT;
ISDEFAULT COUNT(*)
--------- ----------
TRUE 359
FALSE 22
SQL> COL NAME FORMAT A20
SQL> COL VALUE FORMAT A50
SQL> SET PAGESIZE 5000
SQL> SELECT NAME,VALUE FROM V$PARAMETER WHERE ISDEFAULT!='TRUE' order by 1;
OR
SQL> SELECT NAME,VALUE FROM V$SYSTEM_PARAMETER WHERE ISDEFAULT!='TRUE' order by 1;
SQL> SELECT COUNT(CASE WHEN ISSES_MODIFIABLE='TRUE' THEN 1 END) ISSES_MODIFIABLE,
2 COUNT(CASE WHEN ISSYS_MODIFIABLE!='FALSE' THEN 1 END) ISSYS_MODIFIABLE,
3 COUNT(CASE WHEN ISPDB_MODIFIABLE='TRUE' THEN 1 END) ISPDB_MODIFIABLE FROM V$PARAMETER;
ISSES_MODIFIABLE ISSYS_MODIFIABLE ISPDB_MODIFIABLE
---------------- ---------------- ----------------
171 259 182
[1] PFILE
It is Text File.
- We can see if we have starting the instance SPFILE or PFILE
- This parameter gives the location of the SPFILE or will be empty if we
raise the DB with PFILE
show parameters spfile
HOL:
SQL> startup pfile=$ORACLE_HOME/dbs/initorcl12c.ora
ORACLE instance started.
Total System Global Area 939524096 bytes
Fixed Size 2931088 bytes
Variable Size 348128880 bytes
Database Buffers 583008256 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string
Note:
A text initialization parameter file (PFILE) cannot contain PDB-specific parameter values.
HOL: How to create PFILE.
#Manually create PFILE.
cd $ORACLE_HOME/dbs
cat init.ora | grep -v '#' | awk 'NF>0' > initcdb.ora
#Edit the mandatory and required parameter as per requirement
vi initcdb.ora
#Create from PFILE.
SQL> create pfile from spfile;
[2] SPFILE
It is binary file.
V$SPPARAMETER
Displays information about the contents of the server parameter file.
If a server parameter file was not used to start the instance, then each row
of the view will contain FALSE in the ISSPECIFIED column.
#Check if instance start with spfile.
SQL>Shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> SQL> startup
ORACLE instance started.
Total System Global Area 939524096 bytes
Fixed Size 2931088 bytes
Variable Size 348128880 bytes
Database Buffers 583008256 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.1.0
.2/db_1/dbs/spfileorcl12c.ora
SQL> desc v$spparameter
Name Null? Type
----------------------------------------- -------- ----------------------------
FAMILY VARCHAR2(80)
SID VARCHAR2(80)
NAME VARCHAR2(80)
TYPE VARCHAR2(11)
VALUE VARCHAR2(255)
DISPLAY_VALUE VARCHAR2(255)
ISSPECIFIED VARCHAR2(6)
>>> Indicates whether the parameter was specified in the server parameter file (TRUE) or not (FALSE)
ORDINAL NUMBER
UPDATE_COMMENT VARCHAR2(255)
CON_ID NUMBER
SQL> select name from v$spparameter where ISSPECIFIED='TRUE';
NAME
-------------------------
processes
sga_target
control_files
control_files
db_block_size
compatible
db_recovery_file_dest
db_recovery_file_dest_siz
e
undo_tablespace
remote_login_passwordfile
db_domain
dispatchers
shared_servers
local_listener
local_listener
listener_networks
audit_file_dest
audit_trail
db_name
open_cursors
pga_aggregate_target
enable_ddl_logging
diagnostic_dest
enable_pluggable_database
24 rows selected.
SQL> create pfile from spfile;
File created.
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
-bash-4.1$ dbs
-bash-4.1$ cat initorcl12c.ora
orcl12c.__data_transfer_cache_size=0
orcl12c.__db_cache_size=629145600
orcl12c.__java_pool_size=4194304
orcl12c.__large_pool_size=4194304
orcl12c.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl12c.__pga_aggregate_target=314572800
orcl12c.__sga_target=939524096
orcl12c.__shared_io_pool_size=37748736
orcl12c.__shared_pool_size=255852544
orcl12c.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0'
*.control_files='/u01/app/oracle/oradata/orcl12c/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_name='orcl12c'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(service=pdb1.us.oracle.com))(dispatchers=5)(listener=PDB_LISTENER)'
*.enable_ddl_logging=TRUE
*.enable_pluggable_database=true
*.listener_networks=''
*.local_listener='PDB_LISTENER','LISTENER1523'
*.open_cursors=300
*.pga_aggregate_target=298m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=894m
*.shared_servers=2
*.undo_tablespace='UNDOTBS1'
#We can also view the spfile using string command.
$ strings spfileorcl12c.ora
orcl12c.__data_transfer_cache_size=0
orcl12c.__db_cache_size=629145600
orcl12c.__java_pool_size=4194304
orcl12c.__large_pool_size=4194304
orcl12c.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl12c.__pga_aggregate_target=314572800
orcl12c.__sga_target=939524096
orcl12c.__shared_io_pool_size=37748736
orcl12c.__shared_pool_size=255852544
orcl12c.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/orcl12c/adump'
*.audit_trail='db'
*.compatible='12.1.0.2.0
*.control_files='/u01/app/oracle/oradata/orcl12c/control01.ctl','/u01/app/oracle/fast_recovery_area/orcl12c/control02.ctl'
*.db_block_size=8192
*.db_domain='us.oracle.com'
*.db_name='orcl12c'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4560m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(ADDRESS = (PROTOCOL = TCP)(HOST = racnode1)(service=pdb1.us.oracle.com))(dispatchers=5)(listener=PDB_LISTENER)'
*.enable_ddl_logging=TRUE
*.enable_pluggable_database=true
*.listener_networks=''
*.local_listener='PDB_LISTENER','LISTENER1523'
*.open_cursors=300
*.pga_aggregate_target=298m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=894m
*.shared_servers=2
*.undo_tablespace='UNDOTBS1'
When working with Oracle Database 12c, you have to understand how the
initialization parameters
are set for the CDB and each of the PDBs associated.
The traditional ways of looking at an SPFILE will
only be useful for a CDB database. This is because the CDB is the root
level that controls many of the items which are shared amongst the CDB and
PDBs. In order to fully understand how parameters are set for a PDB,
you need to remember that PDBs inherit parameter values from a CDB;
unless they are overridden from the PDB level.
When a PDB is unplugged from a CDB,
the values of the initialization parameters that were specified for the
PDB with SCOPE=BOTH or SCOPE=SPFILE
are added to the PDB's XML metadata file. These values are restored for
the PDB when it is plugged in to a CDB.
The values that are in the XML file appear to be just general settings.
The statistics_level parameter didn’t make it into the XML file either.
parameters for the PDB are stored in the PDB data dictionary.
Instance-level parameter changes in the root container are stored in the
SPFILE in the normal way.
When you change PDB-specific initialization parameters in the PDB they
are not stored in the SPFILE.
Instead they are saved in the PDB_SPFILE$ table.
In a multi-tenant container database, since there are many PDB’s per CDB,
it is possible for set some parameters
for each individual PDB. The SPFILE for CDB stores parameter values
associated with the root which apply to the root,
and serve as default values for all other containers. Different values
can be set in PDBs for those parameters where
the column ISPDB_MODIFIABLE in V$PARAMETER is TRUE. The parameters are
set for a PDB and are stored in table PDB_SPFILE$
remembered across PDB close/open and across restart of the CDB.
===
HOL : PDB_SPFILE$ table
===
-bash-4.1$ export ORACLE_SID=orcl12c
-bash-4.1$ sql
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 PDB2 READ WRITE NO
SQL> select con_id,name,value from v$system_parameter
where name in ('open_cursors','cursor_sharing','statistics_level');
CON_ID NAME VALUE
------------------------------------------------------------------------
0 cursor_sharing similar
0 open_cursors 50
0 statistics_level TYPICAL
5 cursor_sharing FORCE
5 open_cursors 2000
SQL> alter session set container=pdb2;
Session altered.
SQL> alter system set open_cursors=555;
System altered.
SQL> alter system set statistics_level=all;
System altered.
SQL> alter system set cursor_sharing=similar;
System altered.
SQL> select con_id,name,value from v$system_parameter
where name in ('open_cursors','cursor_sharing','statistics_level');
CON_ID NAME VALUE
------------------------------------------------------------------------
5 cursor_sharing SIMILAR
5 open_cursors 555
5 statistics_level ALL
SQL> select count(*) from v$pdbs;
1
SQL> select * from pdb_spfile$;
no rows selected
SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL>
SQL> select * from pdb_spfile$;
no rows selected
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
#Current value in spfile of PDB can be seen from pdb_spfile$
SQL> select pdb.name PDB_NAME,par.name PARAMETER,par.VALUE$
FROM PDB_SPFILE$ par,v$pdbs pdb
WHERE par.pdb_uid=pdb.con_uid
PDB_NAME PARAMETER VALUE$
--------------------------------------------------------------------------------
PDB2 open_cursors 555
PDB2 cursor_sharing 'SIMILAR'
PDB2 statistics_level 'ALL'
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
5 PDB2 READ WRITE NO
SQL> alter pluggable database pdb2 close;
Pluggable database altered.
SQL> alter pluggable database pdb2 unplug into '/home/oracle/pdb2.xml';
Pluggable database altered.
SQL> select pdb.name PDB_NAME,par.name PARAMETER,par.VALUE$
2 FROM PDB_SPFILE$ par,v$pdbs pdb
3 WHERE par.pdb_uid=pdb.con_uid;
PDB_NAME PARAMETER VALUE$
--------------------------------------------------------------------------------
PDB2 open_cursors 555
PDB2 cursor_sharing 'SIMILAR'
PDB2 statistics_level 'ALL'
SQL> drop pluggable database pdb2 keep datafiles;
Pluggable database dropped.
SQL> select pdb.name PDB_NAME,par.name PARAMETER,par.VALUE$
2 FROM PDB_SPFILE$ par,v$pdbs pdb
3 WHERE par.pdb_uid=pdb.con_uid;
no rows selected
SQL> shut immediate
Database closed.
Database dismounted.
exit
ORACLE instance shut down.
-bash-4.1$ cat /home/oracle/pdb2.xml | grep spfile
*.statistics_level='ALL' <--- *.cursor_sharing='SIMILAR' *.open_cursors=555 -bash-4.1$ -bash-4.1$ export ORACLE_SID=cdb1 -bash-4.1$ sql SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
SQL> run
1 BEGIN
2 IF DBMS_PDB.CHECK_PLUG_COMPATIBILITY('/home/oracle/pdb2.xml') then
3 dbms_output.put_line('no violation found');
4 else
5 dbms_output.put_line('violation found');
6 end if;
7* END;
/
no violation found
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL> CREATE PLUGGABLE DATABASE PDB2 USING '/home/oracle/pdb2.xml' move
2 file_name_convert=('/u01/app/oracle/oradata/orcl12c/pdb2','/u01/app/oracle/oradata/cdb1/pdb2');
Pluggable database created.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL> alter pluggable database all open;
Pluggable database altered.
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> alter session set container=pdb2;
Session altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/undotbs01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/system01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/cdb1/pdb2/users.dbf
SQL> select con_id,name,value from v$system_parameter where name in ('open_cursors','cursor_sharing','statistics_level');
CON_ID NAME VALUE
--------------------------------------------------------------------------------
4 cursor_sharing SIMILAR
4 open_cursors 555
4 statistics_level ALL
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
Session altered.
SQL>select pdb.name PDB_NAME,par.name PARAMETER,par.VALUE$
FROM PDB_SPFILE$ par,v$pdbs pdb
WHERE par.pdb_uid=pdb.con_uid
PDB_NAME PARAMETER VALUE$
--------------------------------------------------------------------------------
PDB1 open_cursors 1000
PDB1 cursor_sharing 'FORCE'
PDB2 open_cursors 555
PDB2 cursor_sharing 'SIMILAR'
PDB2 statistics_level 'ALL'
SQL>
====
HOL : ALTER SYSTEM CONTAINER = CURRENT OR ALL
====
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL>
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 2000
SQL>
SQL> alter system set open_cursors=5000;
System altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
SQL>
SQL> alter session set container=pdb2;
Session altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
SQL>
SQL> alter system set open_cursors=100;
System altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 100
SQL>
SQL> alter session set container=pdb1;
Session altered.
SQL> alter system set open_cursors=200 CONTAINER=CURRENT;
System altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 200
SQL> alter session set container=cdb$root;
Session altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
SQL>
SQL> alter system set open_cursors=55 CONTAINER=CURRENT;
System altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 55
SQL>
SQL> alter session set container=pdb1;
Session altered.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 200
SQL>
SQL> alter session set container=pdb2;
Session altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 100
SQL>
SQL> alter session set container=cdb$root;
Session altered.
SQL> alter system set open_cursors=5000 CONTAINER=ALL;
System altered.
SQL> alter session set container=pdb1;
Session altered.
SQL> show parameter open_cursors;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
SQL> alter session set container=pdb2;
Session altered.
SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
open_cursors integer 5000
[3] orapwfile
$ orapwd
Usage: orapwd file= entries= force=<y/n> asm=<y/n>
dbuniquename= format=<legacy/12> sysbackup=<y/n> sysdg=<y/n>
syskm=<y/n> delete=<y/n> input_file=
Usage: orapwd describe file=
where
file - name of password file (required),
password - password for SYS will be prompted
if not specified at command line.
Ignored, if input_file is specified,
entries - maximum number of distinct DBA (optional),
force - whether to overwrite existing file (optional),
asm - indicates that the password to be stored in
Automatic Storage Management (ASM) disk group
is an ASM password. (optional).
dbuniquename - unique database name used to identify database
password files residing in ASM diskgroup only.
Ignored when asm option is specified (optional),
format - use format=12 for new 12c features like SYSBACKUP, SYSDG and
SYSKM support, longer identifiers, etc.
If not specified, format=12 is default (optional),
delete - drops a password file. Must specify 'asm',
'dbuniquename' or 'file'. If 'file' is specified,
the file must be located on an ASM diskgroup (optional),
sysbackup - create SYSBACKUP entry (optional and requires the
12 format). Ignored, if input_file is specified,
sysdg - create SYSDG entry (optional and requires the 12 format),
Ignored, if input_file is specified,
syskm - create SYSKM entry (optional and requires the 12 format),
Ignored, if input_file is specified,
input_file - name of input password file, from where old user
entries will be migrated (optional),
describe - describes the properties of specified password file
(required).
There must be no spaces around the equal-to (=) character.
===
HOL:
===
$sql
SQL> SELECT * FROM V$PWFILE_USERS;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
SQL> SHOW PDBS
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 MOUNTED
4 PDB2 MOUNTED
SQL>
SQL> GRANT SYSDBA TO SYSDG;
GRANT SYSDBA TO SYSDG
*
ERROR at line 1:
ORA-65175: cannot grant SYSDBA privilege locally in the root
SQL> GRANT SYSDBA TO SYSDG CONTAINER=ALL;
Grant succeeded.
SQL> SELECT * FROM V$PWFILE_USERS;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG TRUE FALSE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
SQL> EXIT
-bash-4.1$ rm orapwcdb1
-bash-4.1$
-bash-4.1$ sql
SQL> SELECT * FROM V$PWFILE_USERS;
no rows selected
SQL> exit
-bash-4.1$ orapwd file=orapwcdb1 entries=10
Enter password for SYS:
-bash-4.1$
-bash-4.1$ ll orapwcdb1
-rw-r----- 1 oracle oinstall 7680 Jul 30 09:45 orapwcdb1
-bash-4.1$
-bash-4.1$ sql
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SQL> exit
-bash-4.1$ rm orapwcdb1
-bash-4.1$
-bash-4.1$ orapwd file=orapwcdb1 entries=10 password=oracle_4U format=12
-bash-4.1$
-bash-4.1$ sql
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SQL> GRANT SYSDG TO SYSDG;
Grant succeeded.
SQL> GRANT SYSKM TO SYSKM;
Grant succeeded.
SQL> GRANT SYSBACKUP TO SYSBACKUP;
Grant succeeded.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSDG TRUE FALSE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SQL> exit
-bash-4.1$ rm orapwcdb1
-bash-4.1$ sql
SQL> select * from v$pwfile_users;
no rows selected
SQL> exit
-bash-4.1$ orapwd file=orapwcdb1 entries=10 password=oracle_4U format=12 SYSDG=Y SYSBACKUP=Y SYSKM=Y
Enter password for SYSBACKUP:
Enter password for SYSDG:
Enter password for SYSKM:
-bash-4.1$
-bash-4.1$ sql
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SYSBACKUP FALSE FALSE FALSE TRUE FALSE FALSE 1
SYSDG TRUE FALSE FALSE FALSE FALSE FALSE 0
SYSDG FALSE FALSE FALSE FALSE TRUE FALSE 1
SYSKM FALSE FALSE FALSE FALSE FALSE TRUE 1
SQL> exit
-bash-4.1$ orapwd file=orapwcdb1 entries=5 password=oracle_4U format=legacy
$sql
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SQL> exit
-bash-4.1$ mv orapwcdb1 orapwcdb1tmp
-bash-4.1$
-bash-4.1$ orapwd describe file=orapwcdb1tmp
Password file Description : format=LEGACY ignorecase=N
-bash-4.1$
-bash-4.1$ orapwd file=orapwcdb1 input_file=orapwcdb1tmp
-bash-4.1$
-bash-4.1$ ll orapwcd*
-rw-r----- 1 oracle oinstall 7680 Jul 23 11:40 orapwcdb
-rw-r----- 1 oracle oinstall 8192 Jul 30 10:01 orapwcdb1
-rw-r----- 1 oracle oinstall 2560 Jul 30 09:59 orapwcdb1tmp
-rw-r----- 1 oracle oinstall 7680 Jul 25 09:46 orapwcdb2
-rw-r----- 1 oracle oinstall 7680 Jul 22 12:43 orapwcdb3
-bash-4.1$
-bash-4.1$ orapwd describe file=orapwcdb1
Password file Description : format=12 ignorecase=N
-bash-4.1$
-bash-4.1$ rm orapwcdb1tmp
-bash-4.1$ sql
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS TRUE TRUE FALSE FALSE FALSE FALSE 0
SQL> exit
[4] sqlnet.ora
In this file have various settings SQL Net (both client and server) configuration.
In principle it is not necessary since the default values ??are correct.
Example1 : NAMES.DIRECTORY_PATH To specify the order of the naming methods
used for client name resolution lookups.
$ cat sqlnet.ora
#NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
NAMES.DIRECTORY_PATH= (TNSNAMES)
$ sql
SQL> connect system/oracle_4U@racnode1:1521/pdb1
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
$ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
#NAMES.DIRECTORY_PATH= (TNSNAMES)
-bash-4.1$ sql
SQL> connect system/oracle_4U@racnode1:1521/pdb1
Connected.
SQL>
SQL> exit
$ cat sqlnet.ora
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, EZCONNECT)
#NAMES.DIRECTORY_PATH= (TNSNAMES)
-bash-4.1$ sql
SQL> connect system/oracle_4U@racnode1:1521/pdb1
Connected.
SQL>
SQL> exit
Table 5-1 NAMES.DIRECTORY_PATH Values
Naming Method Value Description
tnsnames (local naming method)
Set to resolve a net service name through the tnsnames.ora file on the client.
ldap (directory naming method)
Set to resolve a database service name, net service name, or net service alias through a directory server.
ezconnect or hostname
(Easy Connect naming or host naming method) Select to enable clients to use
a TCP/IP connect identifier, consisting of a host name and optional port and service name.
nis (Network Information Service (NIS) external naming method)
Set to resolve service information through an existing NIS.
Doc Reference :
SQLNET.EXPIRE_TIME
To specify a time interval, in minutes, to send a check to verify that
client/server connections are active.
SQLNET.INBOUND_CONNECT_TIMEOUT
To specify the time, in seconds, for a client to connect with the database
server and provide the necessary authentication information.
WALLET_LOCATION
To specify the location of wallets. Wallets are certificates, keys,
and trustpoints processed by SSL.
TCP.EXCLUDED_NODES
To specify which clients are denied access to the database.
TCP.INVITED_NODES
To specify which clients are allowed access to the database.
This list takes precedence over the TCP.EXCLUDED_NODES parameter if both
lists are present.
TCP.VALIDNODE_CHECKING
To enable and disable valid node checking for incoming connections.
Note : The TCP.INVITED_NODES and TCP.EXCLUDED_NODES parameters are valid
only when the TCP.VALIDNODE_CHECKING parameter is set to yes.
TCP.NODELAY
To preempt delays in buffer flushing within the TCP/IP protocol stack.
SQLNET.COMPRESSION
To enable or disable data compression. If both the server and client have
this parameter set to ON, then compression is used for the connection.
SQLNET.ALLOWED_LOGON_VERSION_SERVER
To set the minimum authentication protocol allowed when connecting to
Oracle Database instances.
SQLNET.ALLOWED_LOGON_VERSION_CLIENT
To set the minimum authentication protocol allowed for clients, and
when a server is acting as a client,such as connecting over a database link,
when connecting to Oracle Database instances.
SEND_BUF_SIZE
Specify the buffer space limit for send operations of sessions.
RECV_BUF_SIZE
Specify the buffer space limit for receive operations of sessions.
===
HOL: Generate the sqlnet.ora file using NETCA
===
In the initial screen of the wizard ->
-> Select “Naming Methods configuration” ->
-> Click “Next” ->
-> Move methods “Local Naming”, “Directory Naming” and “Easy Connect Naming” ->
-> Click “Next” ->
-> Click “Next” ->
-> Click “Finish”
===
HOL: Using sample file
===
net
cd samples/
ls
more sqlnet.ora
You can also use Silent Method to configure the listener.ora,tnsnames.ora
and sqlnet.ora For [5] listener.ora and [6] tnsnames.ora kindly refer below blog
Configure the network environment to allow connections to multiple databases
=====================
Path to Documentation:
=====================
Initialization Parameters
Database Net Services Reference=>
5 Parameters for the sqlnet.ora File=>sqlnet.ora Profile Parameters
No responses yet