Oracle - Notes & tips

mail

Oracle glossary

control file
a small binary file recording the database's status and structure (details : 1, 2) :
  • database name
  • names and locations of datafiles and redo log files
  • timestamp of the database creation
  • current log sequence number
  • checkpoint information
  • recent RMAN backups
datafile
files used to store data. They are grouped together into tablespaces. (details)
database
in Oracle logic and documentation, a database is just a set of files
init file (aka pfile)
  • plain text file containing database instance initialization settings
  • used when creating / updating an instance configuration : it is read and compiled into spfile, then becomes inert
  • typically found at : $ORACLE_HOME/dbs/initORACLE_SID.ora (see it in file tree, source)
  • How to create it
instance (aka database instance, see : Oracle 12.1 documentation, Oracle 19 documentation)
processes + memory areas dedicated to a single database
listener
  • process listening for incoming database connections
  • controlled with the lsnrctl utility
  • configuration : listener.ora
    • typical path to configuration : $ORACLE_HOME/network/admin/listener.ora, i.e. /app/oracle/product/12.1.0.2/db_1/network/admin/listener.ora
    • How to configure a listener ?
  • full details
ORACLE_SID
  • unique name for a database instance on a specific host
  • its value :
    • is shared via an environment variable (details) : export ORACLE_SID=myDatabase
    • is often shown full uppercase but it's just a common practice. It is case-sensitive on Unix / Linux systems, but not on Windows (source).
    • is made of the first 8 characters of the database name (but it is common to have database name = ORACLE_SID)
    • can contain only the a-z, A-Z and 0-9 characters (source)
oraarch aka archived redo log files
  • Exist only in ARCHIVELOG mode
  • An archived redo log file is a copy of one of the filled members of a redo log group (i.e. a full redolog file) made by the ARCn process.
  • Are used to recover a database, or to update a standby database.
PMON
  • background process that monitors other background processes and performs process recovery when a server or dispatcher process crashes
  • more about Oracle processes : 1, 2
parameter file
There are 2 kinds of "parameter files" :
  • initialization parameter file : this is the init file
  • server parameter file : hello spfile
Sometimes the Oracle documentation (12, 19) uses terms that are so generic (and too many synonyms) that it becomes confusing. Parameter file must be understood as a means to avoid repeating in the pfile or in the spfile.
redo log files aka oraredolog
  • A set of files (at least 2 of them) that record all changes made to a database. When a transaction is committed, transaction details are written from the redo log buffer to a redo log file.
  • They are used in failure situations to replay transactions.
  • They often are a bottleneck causing degraded performance.
  • redo log files are circularly written/overwritten by the LGWR process. This depends on the instance ARCHIVELOG / NOARCHIVELOG mode. (details)
(details : 1, 2)
standby redo logs (SRL) are somewhat similar to online redo logs except that (source) :
  • standby redo logs exist only on a standby server (and online redo logs on a primary server)
  • standby redo logs are used to store redo data that has been received from a primary database. online redo logs are used to store redo data generated by user activity.
schema
set of all objects belonging to a user : tables, views, indexes, PL/SQL scripts, ... In practice, it is safe to consider a schema is an Oracle user account. (details)
SMON
  • background process in charge of a variety of system-level cleanup duties
  • see PMON for links about processes
spfile
tablespace
transaction
an inseparable list of operations which must be executed either in its entirety or not at all.
An example of a transaction is a bank transfer of funds from one account to another. Even though it might consist of multiple individual operations (such as debiting one account and crediting another), it must succeed of fail as a unit, but can never be left in an inconsistent state. (details)

General notes :

  • redo log files as well as control files can (should!) be multiplexed on multiple disks to ensure that they will not get lost.
  • It is NOT SAFE to alter files / directories (move, delete, rename, ...) without letting Oracle know about that first. Even when those files are eating 100% of a disk.
mail

Triggers

Create a trigger (more) :

CREATE OR REPLACE TRIGGER triggerName after startup on database
DECLARE
	(declare SQL variables here)
BEGIN
	(trigger code here)
END;
/

List existing triggers (source) :

select trigger_name from dba_triggers;

View code of a trigger (sources 1, 2) :

set long 100000
select dbms_metadata.get_ddl('TRIGGER','TRIGGERNAME',USER) from dual;
OR : 
select text from user_source where name='TRIGGERNAME' and type='TRIGGER';

TRIGGERNAME must be uppercase !

Delete a trigger (source) :

drop trigger triggerName;

Trigger dropped.

mail

Oracle file tree

/ system root
etc system main config directory
oratab list of $ORACLE_SID:$ORACLE_HOME:[N|Y]: entries (details)
$ORACLE_HOME
dbs
init.ora example init file
init$ORACLE_SID.ora init file
spfile$ORACLE_SID.ora spfile
orapw$ORACLE_SID password file (details : 1, 2)
network
admin
listener.ora
tnsnames.ora used to resolve Oracle net service names (VERY basically, like /etc/hosts does for hosts names, details)

Details about files

oratab
  • created by $ORACLE_HOME/root.sh ($ORACLE_HOME/install/utl/rootinstall.sh, actually) and updated by DBCA when creating a new DB
  • format : ORACLE_SID:ORACLE_HOME:Y|N. The Y|N flags indicate if the instance should be started at boot time with dbstart and dbshut scripts
  • details : 1, 2

List all the configuration files of an instance

ORACLE_SID=value
ls -l $ORACLE_HOME/dbs/{init,spfile}$ORACLE_SID.ora $ORACLE_HOME/dbs/orapw$ORACLE_SID $ORACLE_HOME/network/admin/{listener,tnsnames}.ora
mail

Oracle directories and aliases

List known directories and aliases :

SET pagesize 50
SET linesize 200
column OWNER format a15
column DIRECTORY_NAME format a30
column DIRECTORY_PATH format a50
select * from dba_directories;
OWNER	DIRECTORY_NAME			DIRECTORY_PATH
------- ------------------------------- --------------------------------------------------
SYS	DATAPUM_PATH			/appli/oracle/ORACLE_SID/oraarch
SYS	EXPORACLE_SIDPARALLEL		/sauvebase/ORACLE_SID/20140620
SYS	EXPORACLE_SID			/sauvebase/ORACLE_SID/EXPORACLE_SID
SYS	EXPORTBASE			/exportbase
SYS	REP_MDP				/outillage/controle/c_oracle/sql
SYS	DIRECTORY_PATH			/sauvebase/ORACLE_SID
SYS	SAUVE_BASE_ORACLE_SID		/sauvebase/ORACLE_SID
SYS	sauvebase_ORACLE_SID		/sauvebase/ORACLE_SID
SYS	exportbase			/exportbase
SYS	DATA_PUMP_DIR			/appli/oracle/ORACLE_SID
SYS	EXPDP_DIR			/exportbase
SYS	DIR_ALR_DEPOT			/appli/vdp/work/donnees/donnees_de_reference/
SYS	ORACLE_OCM_CONFIG_DIR		/logiciels/oracle/oradb/11.2.0/ora/ccr/state

Create a new directory and its alias (sources 1, 2) :

  1. CREATE OR REPLACE DIRECTORY directoryAlias AS '/path/to/directory';
  2. (optional if used by '/ as sysdba') : GRANT READ, WRITE ON 'directoryAlias' TO bob;
  3. list directories to make sure it's effectively there.
    Looks like the specified directory alias is turned uppercase automatically.
  4. Do whatever you need with it
  5. Remove it : DROP directory 'directoryAlias';
mail

Datapump : export / import

Prerequisites :

  • Commands below must be run as oracle
  • The ORACLE_SID environment variable must be set
  • The database must be open

Export a schema :

expdp \"/ as sysdba\" SCHEMAS=mySchema DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_MYSCHEMA_YYYYMMDD.dmp LOGFILE=expdp_MYSCHEMA_YYYYMMDD.log

The DIRECTORY option expects a directory alias, not a filesystem path.

It is wise to write the dumpfile on a filesystem that is not the filesystem holding the database files :
  • reaching 100% of filesystem use will just make the export fail rather than switching the database in read-only mode
  • if one of the filesystems fails, you still have either the database or the dumped data
  • sharing I/Os between disks

Import a schema :

impdp \"/ as sysdba\" SCHEMAS=mySchema DIRECTORY=DATA_PUMP_DIR DUMPFILE=expdp_MYSCHEMA_YYYYMMDD.dmp LOGFILE=impdp_MYSCHEMA_YYYYMMDD.log
mail

Some debug tips

This is a WIP about the checks to perform when something's running wrong with Oracle. Only very basic stuff so far...

Check filesystems (source) :

echo -e '\nDisk usage :'; df -Ph | grep -E "(9.|100)%" || echo '(no result found)'; echo -e '\nInodes :'; df -Phi | grep "100%" || echo '(no result found)'; echo -e '\nRead-only filesystems :'; mount | grep --color=auto '(ro)' || echo '(no result found)'

Find the alert log, trace files, audit file, ... :

As oracle : 
export ORACLE_SID=ORACLE_SID; sqlplus -s / as sysdba << EOSQL
select value || '/alert_$ORACLE_SID.log' as alertLog from v\$parameter where name='background_dump_dest';
select value as traceFile from v\$diag_info where name='Default Trace File';
EOSQL

Check database role : primary / standby :

As oracle :
export ORACLE_SID=ORACLE_SID; echo 'select database_role from v$database;' | sqlplus -s / as sysdba
Should return : 
DATABASE_ROLE
------------------------------------------------
PRIMARY
or : 
DATABASE_ROLE
------------------------------------------------
PHYSICAL STANDBY
Other "standby" modes exist, but the one I expect is PHYSICAL STANDBY (these other modes are not used in my company) Anyway, if this returns an error, this _may_ indicate the Data Guard configuration is broken.

Check primary / standby synchronization :

See this procedure.

Is there a gap in archivelogs ?

On standbyServer :
export ORACLE_SID=ORACLE_SID; echo 'select * from v$archive_gap;' | sqlplus -s / as sysdba
Will return, if there is no gap : 
no rows selected

Are control files still there ?

export ORACLE_SID=ORACLE_SID; grep 'control_files' /home/oracle/admin/$ORACLE_SID/dbs/init$ORACLE_SID.ora | sed -r "s_.*='([^\']*\.ctl)','([^\']*\.ctl)','([^\']*\.ctl)'_\1 \2 \3_g" | xargs ls -lh

-rw-r----- 1 oracle oinstall 14M Oct 29 10:01 /appli/oracle/ORACLE_SID/oractl01/ORACLE_SID_ctrl01.ctl
-rw-r----- 1 oracle oinstall 14M Oct 29 10:01 /appli/oracle/ORACLE_SID/oractl02/ORACLE_SID_ctrl02.ctl
-rw-r----- 1 oracle oinstall 14M Oct 29 10:01 /appli/oracle/ORACLE_SID/oradata01/ORACLE_SID_ctrl03.ctl

Oracle tracks its internal parameters in these files. So this is normal their modification time is always incrementing.

Data Guard parameters :

As root : su - oracle -c "export ORACLE_SID=ORACLE_SID; echo -e 'show parameter dg' | sqlplus / as sysdba" 

NAME				TYPE		VALUE
------------------------------- --------------- ------------------------------
dg_broker_config_file1		string		/logiciels/oracle/oradb/11.2.0/ora/dbs/dr1ORACLE_SID_primaryDatacenter.dat
dg_broker_config_file2		string		/logiciels/oracle/oradb/11.2.0/ora/dbs/dr2ORACLE_SID_primaryDatacenter.dat
dg_broker_start			boolean		FALSE
mail

What if the storage volume dedicated to the oraarch is full ?

I am NOT a qualified Oracle DBA, and information below is EXTREMELY quick'n'dirty and MAY be wrong / incomplete / inaccurate / dangerous for your data. I had to make things work in a testing environment (i.e. where data loss is no big trouble) and actions below worked AFAIK.
Use this at your own risk !
If you're more qualified than I am on this topic and want to share some thoughts, mind the little envelop icon on the upper right corner of this article. I'd be delighted to read good advice and update this article

Situation

Based on my (short) experience with Oracle products, I can tell that, when things start to become weird (i.e. configurations are ok, but stuff still fail), there may be a full disk somewhere. I've already noticed such behavior : So, just in case, search for a full disk. 
/dev/mapper/y6luvg_pco01-lv_oraarch_pco	50G	47G	147M 100% /appli/oracle/ORACLE_SID/oraarch
Ok, now you know.

Solution

Read about this alternate solution, which looks both safer and more efficient than the one below .

As said above, this is highly experimental and should be used at your own risk.

With RMAN (source) :

  1. log on as the oracle user
  2. export ORACLE_SID=ORACLE_SID; rman target /
  3. Delete archivelogs that are older than 30 days :
    delete archivelog until time 'SYSDATE-30';
    using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1995_1.arc thread=1 sequence=1995

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_3088_1.arc thread=1 sequence=3088
List of Archived Log Copies for database with db_unique_name ORACLE_SID_STANDBYDATACENTER
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    1605    A 21-APR-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1605_1.arc

2       1    1606    A 21-APR-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1606_1.arc

462     1    1970    A 25-JUN-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1970_1.arc

463     1    1971    A 25-JUN-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1971_1.arc

Do you really want to delete the above objects (enter YES or NO)?
    And if you say yes : 
    deleted archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1969_1.arc RECID=461 STAMP=884965649
deleted archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1970_1.arc RECID=462 STAMP=884965650
deleted archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1971_1.arc RECID=463 STAMP=884965650
Deleted 3 objects

RMAN-06207: WARNING:	364 objects could not be deleted for DISK channel(s) due
RMAN-06208:		to mismatched status. Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Archivelog      /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1605_1.arc
RMAN-06214: Archivelog      /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1606_1.arc

RMAN-06214: Archivelog      /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1967_1.arc
RMAN-06214: Archivelog      /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1968_1.arc
This deleted only 3 files and the disk usage is still :
df -h /appli/oracle/ORACLE_SID/oraarch 
Filesystem					Size	Used	Avail	Use%	Mounted on
/dev/mapper/y6luvg_pco01-lv_oraarch_pco		50G	43G	297M	99%	/appli/oracle/ORACLE_SID/oraarch
297MB freed is way not enough. We'll have to be a little more evil...

Let's delete files and say "sorry" (source) :

  1. Instead of brutally deleting files older than 30, we'll move them to another filesystem having some space left, just in case something goes wrong :
    ORACLE_SID=ORACLE_SID; backupDir="/exportbase/$ORACLE_SID/oraarch"; mkdir -p "$backupDir"; find /appli/oracle/$ORACLE_SID/oraarch -type f -ctime +30 -exec mv {} "$backupDir" \;
  2. Let's check :
    df -h /appli/oracle/$ORACLE_SID/oraarch
    Filesystem					Size	Used	Avail	Use%	Mounted on
/dev/mapper/y6luvg_pco01-lv_oraarch_pco		50G	43G	4.4G	91%	/appli/oracle/ORACLE_SID/oraarch
    Looks better.
  3. Now, as oracle, let's ask RMAN to forget about those archivelogs :
    export ORACLE_SID=ORACLE_SID; rman target /
  4. crosscheck archivelog all; 
    using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
validation failed for archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1605_1.arc RECID=1 STAMP=877639635
validation failed for archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1606_1.arc RECID=2 STAMP=877639637

validation succeeded for archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_3387_1.arc RECID=803 STAMP=888848753
validation succeeded for archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_3447_1.arc RECID=804 STAMP=888849107
Crosschecked 774 objects
  5. delete noprompt expired archivelog all; 
    released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=258 device type=DISK
List of Archived Log Copies for database with db_unique_name ORACLE_SID_STANDBYDATACENTER
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    1605    X 21-APR-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1605_1.arc

2       1    1606    X 21-APR-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1606_1.arc

498     1    3082    X 27-JUL-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_3082_1.arc

499     1    3083    X 27-JUL-15
        Name: /appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_3083_1.arc

deleted archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1605_1.arc RECID=1 STAMP=877639635
deleted archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_1606_1.arc RECID=2 STAMP=877639637

deleted archived log
archived log file name=/appli/oracle/ORACLE_SID/oraarch/ORACLE_SID_875897971_3083_1.arc RECID=499 STAMP=886148904
Deleted 469 EXPIRED objects

    After that, I've been able to use the database again

  6. Even stronger :
    export ORACLE_SID='ORACLE_SID'; echo -e 'shutdown immediate;\nstartup mount;' | sqlplus -s / as sysdba; echo 'delete noprompt archivelog all;' | rman target /
    Don't forget to re-start/mount/open/whatever the database.

Alternate solution

Alternate solution (source) :

As oracle :
export ORACLE_SID='ORACLE_SID'; echo -e 'shutdown immediate;\nstartup mount;' | sqlplus -s / as sysdba; echo -e 'backup archivelog all not backed up 1 times;\nbackup archivelog all delete input;' | rman target /
Don't forget to re-start/mount/open/whatever the database.

Alternate solution

Alternate-Alternate solution (not tried yet) :

As oracle :
export ORACLE_SID="ORACLE_SID'; echo -e 'DELETE NOPROMPT ARCHIVELOG UNTIL TIME='SYSDATE-2'" | rman target /
Don't forget to re-start/mount/open/whatever the database.
mail

The database startup script 

as root :
cp /home/oracle/app/oracle/product/12.1.0/dbhome_1/bin/db{start,shut} /etc/init.d
chown root:root /etc/init.d/db{start,shut}
update-rc.d dbstart defaults
	insserv: warning: script 'K01dbstart' missing LSB tags and overrides
	insserv: warning: script 'dbstart' missing LSB tags and overrides
	insserv: warning: script 'dbshut' missing LSB tags and overrides
(but returns a success)

USAGE :

> /u01/app/oracle/product/12.1.0/dbhome_1/shutdown.log
> /u01/app/oracle/product/12.1.0/dbhome_1/startup.log


/etc/init.d/dbstart /u01/app/oracle/product/12.1.0/dbhome_1/
/etc/init.d/dbshut /u01/app/oracle/product/12.1.0/dbhome_1/

==> log is full of errors 



Other solution : create /etc/init.d/oracle as follows (+chmod 755)

#!/usr/bin/env bash
######################################### /etc/init.d/oracle ########################################
# chkconfig: 235 80 20
# description: ajout de service auto pour start/stop oracle
########################################## ##########################################################
ORACLE_OWNER=oracle
ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/
lockFile='/var/lock/subsys/oracle'
case $1 in
	start)
		echo -n "Démarrage Oracle DB:"
		su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
		touch "$lockFile"
		;;
	stop)
		echo -n "Arrêt Oracle DB:"
		su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
		rm "$lockFile"
		;;
	*)
		echo "Usage: $0 {start|stop}"
		;;
esac
mail

Playing with Oracle

Inspired by :
I hear and I forget.
I see and I remember.
I do and I understand.
here are my notes on experimenting with Oracle :
  1. Install Oracle
  2. Configure a listener (server-side, $ORACLE_HOME/network/admin/listener.ora)
  3. Client-side configuration :
    • Name resolution directories : $ORACLE_HOME/network/admin/sqlnet.ora 
      NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
      Create it as oracle with : echo "NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)" > $ORACLE_HOME/network/admin/sqlnet.ora
    • Generate $ORACLE_HOME/network/admin/tnsnames.ora
  4. Create a database
  5. Start the listener
  6. Start the database
  7. You can now play with it : sqlplus / as sysdba@MYDB
  8. Statically register the database into the listener (optional, but good practice)
  9. Register the database in /etc/oratab
  10. Create a startup script
  11. ...
mail

NLS_LANG

Setting the NLS_LANG environment parameter is the simplest way to specify locale behavior for Oracle software. It sets the language and territory used by the client application and the database server (to format and parse dates, times, numbers, currencies, etc.). It also indicates the client's character set, which corresponds to the character set for data to be entered or displayed by a client program.
Format :
NLS_LANGUAGE_NLS_TERRITORY.NLS_CHARACTERSET
Current value :
  • SELECT * FROM V$NLS_PARAMETERS;
  • select (
	(select value from v$nls_parameters where parameter='NLS_LANGUAGE')
	|| '_'
	|| (select value from v$nls_parameters where parameter='NLS_TERRITORY')
	|| '.'
	|| (select value from v$nls_parameters where parameter='NLS_CHARACTERSET')
    ) as NLS_LANG
FROM dual;
Examples :
  • export NLS_LANG=FRENCH_FRANCE.UTF8
  • export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
mail

What are the current username and schema ?

Get it with :
select sys_context('USERENV','SESSION_USER') current_user, sys_context('USERENV','SESSION_SCHEMA') current_schema from dual;
the big-bang version :
echo -e "COLUMN CURRENT_USER format a20\n/\nCOLUMN CURRENT_SCHEMA format a20\n/\nselect sys_context('USERENV','SESSION_USER') current_user, sys_context('USERENV','SESSION_SCHEMA') current_schema from dual\n/\n" | sqlplus username/'password'
Which should output :
CURRENT_USER	CURRENT_SCHEMA
--------------- --------------------
USERNAME	(matching schema)
mail

Oracle, passwords and orapw

The password file orapw (source) :

  • is a binary file
  • has a default location on *Nix : $ORACLE_HOME/dbs/orapw$ORACLE_SID
  • stores passwords + privileges of all users having either SYSDBA or SYSOPER privileges
  • is updated when you change a user's password
  • can be copied from the primary to the standby if a password is changed while the data replication is disabled

How to change a user password ?

  • alter user sys identified by "newPassword";
  • Try it :
    sqlplus sys as sysdba
I can remember no special warning regarding the usage of the equal sign = in Oracle passwords. Such passwords work fine when used interactively. BUT they become unusable in one-liners, where it seems the equal sign is interpreted as trying to set a variable :

Not shown here, but I've also tried to single-quote the password : no change, double-quote : no change, escape the = with a \ : no change, quote + escape : no change, ...

export ORACLE_SID=ORACLE_SID; dgmgrl sys/d-17b=3+A_GY@ORACLE_SID_STANDBYDATACENTER
Invalid option.	WTF?!?
Syntax:
	CONNECT <username>/<password>[@<connect identifier>]
export ORACLE_SID=ORACLE_SID; dgmgrl sys/d-17b3+A_GY@ORACLE_SID_STANDBYDATACENTER
ORA-01017: invalid username/password; logon denied	At least the password has been received, even if it's wrong.

Avoid the equal sign = in passwords.

Retrieve the encrypted version of password, so that I can change it, play, then restore it (source):

this is highly experimental and DOES NEED some extra work. Don't rely on this so far

SELECT dbms_metadata.get_ddl('USER', username) || '/' usercreate FROM dba_users WHERE username='SYS'; 
USERCREATE
--------------------------------------------------------------------------------

ALTER USER "SYS" IDENTIFIED BY VALUES 'S:E1B705DFFFAB28F
B511B87C251B62A2276D
alter user SYS identified by PASSWORD;
mail

Is the Oracle Database Vault enabled ?

SELECT * FROM v$option WHERE parameter LIKE '%Oracle Database Vault%';
PARAMETER		VALUE
----------------------- ----------
Oracle Database Vault	TRUE		TRUE = enabled
mail

Where is the alert log ?

As oracle :