Oracle Database commands

Notes Leo Oracle DB

Start sqlplus

Look for orale_home and SID

cat /etc/oratab

#
# A colon, ':', is used as the field terminator.  A new line terminates
# the entry.  Lines beginning with a pound sign, '#', are comments.
#
# Entries are of the form:
#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:
#
ORCL:/u01/app/oracle/product/12.1.0/dbhome_1:Y

Set the env variables:

[oracle@LeoFareco opc]$ . oraenv

ORACLE_SID = [ORCL] ?

The Oracle base remains unchanged with value /u01/app/oracle

Start sqlplus

[oracle@LeoFareco opc]$ sqlplus / as SYSDBA

Avoid the wrapping of the text

SQL> set lines 999;

SQL> set pages 999;

PDB

Create a new PDB from the Seed

SQL> CREATE PLUGGABLE DATABASE PDB2 ADMIN USER oracle IDENTIFIED BY oracle;

Pluggable database created.

List the available PDBs

SQL> show pdbs

OR

SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB1 READ WRITE
PDB2 MOUNTED

Open a PDB in read-write mode

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;
SQL>  select name, open_mode from v$pdbs;
NAME			       OPEN_MODE
------------------------------ ----------
PDB$SEED		       READ ONLY
PDB1			       READ WRITE
PDB2			       READ WRITE

Connect to a pdb

SQL> show con_name

CDB

SQL> show pdbs
PDB$SEED
PDB1
PDB2

SQL>alter session set container=PDB1;

SQL> show con_name
PDB1

Delete a PDB

Connect to the PDB then issue 

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

The from the CDB, issue

DROP PLUGGABLE DATABASE yourpdb INCLUDING DATAFILES;

Schema

Create schema (user)

create user leopold identified by leopassword default tablespace

  users temporary tablespace temp;

 

grant create session, create table, create procedure,

  create sequence, create view, create public synonym,

  drop public synonym to leopold;

 

alter user leopold quota unlimited on users;

Connect to a schema

1. sqlplus / as sysdba

2. conn schemaName/password@pdbName

Service name

Connect to the CDB or PDB with sqlplus, than type

SELECT * FROM dba_services;

Or (for service names accessible with the local naming method)

cd $ORACLE_HOME/network/admin

cat tnsnames.ora

Tablespaces and quotas

Check the default tablespace for a user

SQL> select default_tablespace from dba_users where username='PDBADMIN';
DEFAULT_TABLESPACE
------------------------------
SYSTEM

Check the tablespace on which a user has some quota

SQL> select tablespace_name, username, bytes, max_bytes from dba_ts_quotas where username='PDBADMIN'; 

TABLESPACE_NAME                USERNAME                                                                              BYTES  MAX_BYTES
------------------------------ ----------------------------------------------------------------------  --------- ---------- ----------
pdb_tbs1                                PDBADMIN                                                                                         0         -1

Grant a user unlimited quota on a tablespace

alter user pdbadmin quota unlimited on SYSTEM;

Enterprise Manager Cloud Control

Restart the OMS agent (Enterprise Manager)

cd /u01/app/oracle/middleware/oms/bin

./emctl stop agent
./emctl clearstate agent
./emctl start agent
./emctl upload agent

Enterprise  manager Express

Connect to the sqlplus and type

select dbms_xdb_config.getHttpsPort() from dual;

if the response is 0 it means you have to activate it with:

exec dbms_xdb_config.sethttpsport(5501);

now you can log in via https://hostname:5501/em

if it’s not working, make sure the Net listener is started : $lsnrctl start

If you only want to access the PDB, do the same but locate the session in the pdb

Data Guard

Connect to DGMGRL

$ dgmgrl sys/welcome1@cdb1

Switchover

DGMGRL> SWITCHOVER TO cdb1_stby

Make sure a db is the primary one

SQL> select name, database_role from v$database;

NAME      DATABASE_ROLE
--------- ----------------
CDB1      PRIMARY

Or 

DGMGRL> show configuration;

Configuration - dg_config
  Protection Mode: MaxPerformance
  Members:
  cdb1      - Primary database
    cdb1_stby - Physical standby database
Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS   (status updated 13 seconds ago)

Data Pump

1. Find the directory for data pump
   select * from DBA_DIRECTORIES;

2. Create a new dir
   Very often, the default directory name “DATA_PUMP_DIR” doesn’t work. So you have to create a new directory name for the very same directory path.
   create directory expdir as '/u01/app/oracle/admin/ORCL/dpdump/';

3. Run the Export command
   expdp userid=system/password#@net_service_name schemas=SP1562144166_ODI_REPO,SP1562422621_ODI_REPO directory=EXPDIR dumpfile=export_%u.dmp PARALLEL=4 logfile=export.log FILESIZE=4G COMPRESSION=all

DB password expired

DB Part

1. List the users
   SELECT username, account_status, authentication_type FROM dba_users;

2. Generate the SQL commands to change the password of expired users
   select 'ALTER USER ' || username || ' identified by <password>;'  from dba_users where account_status like 'EXPIRED%' and username != 'XS$NULL';
   Paste the output.

3. Generate the SQL commands to unlock the locked users
   select 'ALTER USER ' || username || ' account unlock;' from dba_users where  account_status like 'LOCKED%' and username != 'XS$NULL';
   Paste the output.

Weblogic part (if any)

https://docs.oracle.com/en/cloud/paas/java-cloud/jscug/change-database-schema-password.html#GUID-991072F3-2D5A-44A9-834F-9053AAA04C4D