EasyReliableDBA: 19c Database Commands

check if the database is a multitenant container database.

SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID

--------- --- ----------

CONDB YES 0

Check PDB Database

show PDBS;

Alter session set container=pdb1

show con_name

Check the instance name

SQL> select instance_name,status,con_id from v$instance;

instance_name status cont_d

------------------------------

CDB1 Yes 0

SQL> SHOW PARAMETER enable_pluggable_database

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

enable_pluggable_database boolean TRUE

col con_id format 999

col name format A10

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE

---------- ------------------------------ ----------

2 PDB$SEED READ ONLY

3 PDB12C READ WRITE

4 PDB_PLUG_NOCOPY MOUNTED

5 EXNONCDB MOUNTED

6 PDBTEST READ WRITE

7 PDBTEST1 READ WRITE

SQL > ALTER PLUGGABLE DATABASE ALL OPEN;

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;

To Check Service

col name format A30

select name from v$services;

To Check Tablespace

col tablespace_name format A8

col file_id format 99

col con_id format 9

select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ;

select FILE_NAME, TABLESPACE_NAME, FILE_ID from dba_data_files;

To check Temp Tablespace

col file_name format A60

select FILE_NAME, TABLESPACE_NAME, FILE_ID from cdb_temp_files;

Backup in multitenant Environment

We can use RMAN to backup and recovery for CDBs, individual whole PDBs, or partial PDBs such as entire CDBs, partial tablespace/datafile of specific PDBs.

Back up CDB and applications independently:

– ARCHIVELOG mode at CDB level

New Syntax and Clauses in RMAN

export ORACLE_SID=cdb1

rman TARGET /

rman TARGET sys@sales_pdb

DATABASE keyword operates on all PDBs and root, or a PDB:

RMAN> BACKUP DATABASE;

PLUGGABLE DATABASE clause operates on individual PDBs:

RMAN> BACKUP PLUGGABLE DATABASE hr_pdb, sales_pdb;

RMAN> RECOVER PLUGGABLE DATABASE hr_pdb;

Qualify the tablespace of a PDB with the PDB name:

RMAN> BACKUP TABLESPACE sales_pdb:tbs2;

CDB Backup: Whole CDB Backup

Back up all the PDB’s data files and root files.

RMAN> CONNECT TARGET /

RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

CDB Backup: Partial CDB Backup

Back up the root and/or individual PDBs.

RMAN> CONNECT TARGET /

RMAN> BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb;

RMAN> BACKUP PLUGGABLE DATABASE hr_pdb PLUS ARCHIVELOG;

PDB Backup: Whole PDB Backup

Backup whole PDBs with RMAN:

RMAN> CONNECT TARGET /

RMAN> BACKUP PLUGGABLE DATABASE sales_pdb;

RMAN> BACKUP PLUGGABLE DATABASE sales_pdb, hr_pdb;

PDB Backup: Partial PDB Backup

RMAN>

RMAN>CONNECT TARGET /

RMAN> REPORT SCHEMA;

RMAN> BACKUP TABLESPACE sales_pdb:tbs2;

RMAN> BACKUP TABLESPACE hr_pdb:system,

sales_pdb:sysaux;

RMAN> BACKUP TABLESPACE sysaux, hr_pdb:sysaux;

PDB Backup: User-Managed Hot PDB Backup

Perform a user-managed hot PDB backup.

SQL>

SQL>CONNECT system@sales_pdb

SQL> ALTER DATABASE BEGIN BACKUP;

SQL>!cp sales_pdb/datafiles /backup_dir_sales_pdb

SQL> ALTER DATABASE END BACKUP;

SQL> SELECT NAME, CDB, CON_ID FROM V$DATABASE;

NAME CDB CON_ID

--------- --- ----------

CONDB YES 0

To Create user

CREATE USER c##remote_clone_user IDENTIFIED BY remote_clone_user CONTAINER=ALL;

GRANT CREATE SESSION, CREATE PLUGGABLE DATABASE TO c##remote_clone_user CONTAINER=ALL;

admin password =admin@123

select * from product_component_version;

To bypass this error, run your Command Prompt as Administrator before lunching "DBCA".

Add the following parameter to bypass the error:

dbca -J-Doracle.assistants.dbca.validate.ConfigurationParams=false &

https://DESKTOP-I2SNK8O:5501/em

C:\Windows\system32>set ORACLE_HOME=f:\oracle_database

C:\Windows\system32>set oracle_sid=CDB2

C:\Windows\system32>sqlplus " /as sysdba"

SQL>

SQL> select * from v$memoptimize_write_area;

TOTAL_SIZE USED_SPACE FREE_SPACE NUM_WRITES NUM_WRITERS CON_ID

---------- ---------- ---------- ---------- ----------- ----------

0 0 0 0 0 1

0 0 0 0 0 2

0 0 0 0 0 3

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 CDB_PDB1 READ WRITE NO

SQL>

select name, open_mode from v$pdbs;

SQL>

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED MOUNTED

3 RAIDPROD READ WRITE NO

SQL>

SQL> show parameter db_name

NAME TYPE VALUE

------------------------------------ -------------------------------------------- ------------------------------

db_name string raidpcdb

SQL>

ALTER SESSION SET CONTAINER = RAIDPROD;

---Each container in a CDB stores data in its own data files and handles temporary data in its own temp files.-

List the root's tablespaces, data files, and temp files.

connect / as sysdba

select tablespace_name, con_id from cdb_tablespaces where con_id=1;

select file_name, con_id from cdb_data_files where con_id=1;

select file_name, con_id from cdb_temp_files where con_id=1;

connect system/oracle@localhost:1521/pdb3_bis

create tablespace ldata datafile '/u01/app/oracle/oradata/cdb1/pdb3/ldata01.dbf' SIZE 10M;

select tablespace_name, con_id from cdb_tablespaces order by con_id;

select file_name, con_id from cdb_data_files order by con_id;

select file_name from dba_data_files

Create a common user while you are connected to the root.-

connect / as sysdba

create user c##1 identified by oracle container=all;

select username, common, con_id from cdb_users where username like 'C##%';

Connect as a DBA in a PDB to create a local user

connect system/oracle@localhost:1521/pdb3_bis

create user hr identified by oracle;

select username, common, con_id from cdb_users where username ='HR';

Connect as the local HR user in each PDB

connect hr/oracle@localhost:1521/pdb2

connect hr/oracle@localhost:1521/pdb3_bis

Create a common role

connect / as sysdba

create role c##r1 container=all;

select role, common, con_id from cdb_roles where role='C##R1';

Create a local role in a PDB. (When you are connected to a PDB, you cannot create a common role.)

connect system/oracle@localhost:1521/pdb3_bis

create role hr_manager;

select role, common, con_id from cdb_roles where role='HR_MANAGER';

create role c##r2 container=all;

You should see an error message after executing this statement because you cannot create a common role inside a PDB.

Perform the following steps to reset your environment prior to repeating the activities covered in this OBE or starting another OBE.

Drop the common user and role that you created.

drop user c##1;

drop role c##r1;

Drop the tablespaces that you created in the CDB root.

drop tablespace cdata including contents;

drop tablespace temp_root including contents;

Open pdb1 and replace the database trigger with a trigger that opens only pdb1 at CDB startup.

alter pluggable database pdb1 open;

create or replace trigger Sys.After_Startup after startup on database

begin

execute immediate 'alter pluggable database pdb1 open';

end After_Startup;

How to Tell If the Database Is a CDB

1) select name, cdb, con_id from v$database;

Viewing Information About Containers

1) select name, con_id, db_id from v$containers;

Viewing Information About the PDBs

1) select pdb_id, pdb_name, status from cdb_pdbs;

Finding the Open Mode of a PDB

1) select name, open_mode, restricted, open_time from v$pdbs;

Viewing the History of PDBs

1) select db_name, con_id, pdb_name, operation, cloned_from_pdb_name from cdb_pdb_property;

Viewing all the tablespaces in a CDB

select tablespace_name, con_id from cdb_tablespaces;

How to check for Local Undo feature in 12.2

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A30

SELECT property_name, property_value

FROM database_properties

WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ ------------------------------

LOCAL_UNDO_ENABLED TRUE

Connect to a PDB

The syntax to connect to a PDB varies depending on whether or not there is an entry within the tnsnames.ora file for the PDB.

As the oracle user:

Without an entry to the tnsnames.ora file, the syntax to connect to a PDB named orclpdb1 is as follows:

List all the available services and PDBs within the CDB:

SQL> select name, con_id from v$active_services;

NAME CON_ID

------------------------------------------------

orclpdb2 4

SYS$BACKGROUND 1

SYS$USERS 1

cdbXDB 1

orclpdb1 3

cdb 1

6 rows selected.

CONN <user>/<pwd>@//<host>:<listener port>/<service> {as sysdba};

$ $ORACLE_HOME/bin/sqlplus sys/<password>@localhost:1521/orclpdb1 as sysdba;

SQL*Plus: Release 12.2.0.1.0 Production on Tue Sep 5 20:20:29 2017

Startup and Shutdown

Startup and Shutdown of a multitenant database function the same as on a regular database, however, if connected to pluggable database, only the pluggable database shuts down. If connected to the root container database then the entire instance shuts down. Pluggable databases also have their own commands that can be run from the root container or other pluggable db.

ALTER PLUGGABLE DATABASE <name>OPEN READ WRITE{RESTRICTED}{FORCE};

ALTER PLUGGABLE DATABASE <name> OPEN READ ONLY {RESTRICTED}{FORCE};

ALTER PLUGGABLE DATABASE <name> OPEN UPGRADE {RESTRICTED};

ALTER PLUGGABLE DATABASE <name> CLOSE {IMMEDIATE};

To retain state as startup state of container...

ALTER PLUGGABLE DATABASE <name> SAVE STATE;

To Script to create CDB Database maually

CREATE DATABASE cdb1

DATAFILE '/u01/app/oracle/oradata/cdb1/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL

SYSAUX DATAFILE

'/u01/app/oracle/oradata/cdb1/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED

SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE

'/u01/app/oracle/oradata/cdb1/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED

SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE

'/u01/app/oracle/oradata/cdb1/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1

('/u01/app/oracle/oradata/cdb1/redo01.log') SIZE 50M, GROUP 2 ('/u01/app/oracle/oradata/cdb1/redo02.log') SIZE 50M,

GROUP 3 ('/u01/app/oracle/oradata/cdb1/redo03.log') SIZE

50M

USER SYS IDENTIFIED BY "&&sysPassword" USER SYSTEM

IDENTIFIED BY "&&systemPassword"

ENABLE PLUGGABLE DATABASE …;

Query to check if the database is CDB or non-CDB

Suppose you want to know if the target database is CDB (container database ) or Non-CDB ( Normal Non-container database). You can log in to the DB Box and source the Oracle DB and then find using the below query

sqlplus / as sysdba

SELECT NAME, CDB, CON_ID FROM V$DATABASE;

How to check container database in oracle 12c

Query to check containers in the CDB database. Each PDB and root is a container

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE

---------- -------------------- ------------------------------ ----------

1 CDB$ROOT READ WRITE 0

2 PDB$SEED READ ONLY 891289600

3 SEED READ WRITE 1248961280

How to check container database in oracle 12c

Query to check containers in the CDB database. Each PDB and root is a container

SQL> alter session set container=CDB$ROOT;

Session altered.

SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE

---------- -------------------- ------------------------------ ----------

1 CDB$ROOT READ WRITE 0

2 PDB$SEED READ ONLY 891289600

3 SEED READ WRITE 1248961280

4 TEST READ WRITE 1248961280

5 TEST2 READ WRITE 1248961280

If you are connected to a PDB, it will show that container only

SQL> alter session set container=PDB$SEED;

SQL> select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

CON_ID NAME OPEN_MODE TOTAL_SIZE

---------- -------------------- ------------------------------ ----------

2 PDB$SEED READ ONLY 891289600

Connected to root container

SQL> alter session set container=CDB$ROOT;

Session altered.

select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs;

CON_ID DBID NAME OPEN_MODE

---------- ---------- -------------------- ------------------------------

2 5604597646 PDB$SEED READ ONLY

3 823307170 SEED READ WRITE

4 458722011 TEST READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 SEED READ WRITE NO

4 TEST READ WRITE NO

Connected to a particular PDB

SQL> alter session set container=SEED;

Session altered.

SQL> select CON_ID,DBID,NAME,OPEN_MODE from v$pdbs

2 ;

CON_ID DBID NAME OPEN_MODE

---------- ---------- -------------------- ------------------------------

3 823307170 SEED READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

3 SEED READ WRITE NO

SQL>

Startup and shutdown of PDB

Connected to root container

alter pluggable database test1_tech close;

alter pluggable database test1_tech close immediate;

alter pluggable database test1_tech open;

alter pluggable database test1_tech open read only ;

alter pluggable database test1_tech open force;

If the container is set, we can use the below command

ALTER PLUGGABLE DATABASE CLOSE IMMEDIATE;

ALTER PLUGGABLE DATABASE OPEN READ ONLY;

ALTER PLUGGABLE DATABASE OPEN FORCE;

ALTER PLUGGABLE DATABASE NOLOGGING;

ALTER PLUGGABLE DATABASE ENABLE FORCE LOGGING;

STARTUP OPEN

STARTUP OPEN READ ONLY

STARTUP RESTRICT OPEN READ ONLY

How to add service in CDB

srvctl add service -db CDB01 -service TEST_NEW -pdb TEST

srvctl modify service -db CDB01 -service TEST_NEW -pdb TEST

srvctl remove service -db CDB01 -service TEST_NEW

BEGIN

DBMS_SERVICE.CREATE_SERVICE(

service_name => 'TEST_NEW',

network_name => 'TEST_NEW.com');

END;

BEGIN

DBMS_SERVICE.DELETE_SERVICE(

service_name => 'TEST_NEW');

END;

SQL> select name, con_id from v$active_services order by 1;

NAME CON_ID

-------------------- ----------

CDB01 1

CDB01XDB 1

SYS$BACKGROUND 1

SYS$USERS 1

TEST 4

seed 3

6 rows selected.

Show command with 12c database

show con_name

CON_NAME

------------------------------

CDB$ROOT

show con_id

CON_ID

------------------------------

How to close and open all the PDB in a single command

alter pluggable database all close immediate;

alter pluggable database all open;

How to drop the pluggable database

drop pluggable database pdb3_test including datafiles;

Container storage

With the root container, we have cdb_tablespaces,cdb_data_files shows the tablespace and data files of all the containers in the CDB, and dba_tablespaces,dba_data_files will show the root tablespace and datafiles only

alter session set container=CDB$ROOT;

SQL> select tablespace_name, con_id from cdb_tablespaces;

TABLESPACE_NAME CON_ID

------------------------------------------------------------------------------------------ ----------

SYSTEM 1

SYSAUX 1

UNDOTBS1 1

TEMP 1

USERS 1

SYSTEM 2

SYSAUX 2

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------------------------------------------------------------------ ----------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

When connected to a PDB, cdb_tablespaces,cdb_data_files shows the same information as dba_tablespaces,dba_data_files for the PDB

How to rename the pluggable database

Here is the sql command to rename the pluggable database

alter pluggable database TEST rename global_name to TEST1;

How to check for the Local Undo feature in 12.2

Here is the query to check for the Local Undo feature in Oracle database 12.2. This feature enables local undo tablespace for each of the pdb’s

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A30

SELECT property_name, property_value

FROM database_properties

WHERE property_name = 'LOCAL_UNDO_ENABLED';

PROPERTY_NAME PROPERTY_VALUE

------------------------------ ------------------------------

LOCAL_UNDO_ENABLED TRUE

Viewing the History of PDBs

COLUMN DB_NAME FORMAT A10

COLUMN CON_ID FORMAT 999

COLUMN PDB_NAME FORMAT A15

COLUMN OPERATION FORMAT A16

COLUMN OP_TIMESTAMP FORMAT A10

COLUMN CLONED_FROM_PDB_NAME FORMAT A15

SELECT DB_NAME, CON_ID, PDB_NAME, OPERATION, OP_TIMESTAMP, CLONED_FROM_PDB_NAME

FROM CDB_PDB_HISTORY

WHERE CON_ID > 2

ORDER BY CON_ID;

Performance check

SELECT table_name, inmemory_compression,inmemory_priority "PRIORITY", inmemory_distribute FROM dba_tables;

SELECT display_name, value FROM v$mystat m, v$statname n WHERE m.statistic# = n.statistic# AND display_name IN ( session logicale reads - IM','IM scan rows', 'IM scan rows valid','IM scan blocks cache','IM scan CUs columns accessed' );

SELECT FROM WHERE AND
display_name, value
v$mystat m, v$statname n
m.statistic# = n.statistic#
display_name IN (
'IM scan segments minmax eligible',
'IM scan CUs pruned',
'IM scan CUs optimized read',
'IM scan CUs predicates optimized');

SQL> COL tablespace_name FORMAT A15
SQL> SELECT tablespace_name, DEF_INMEMORY_PRIORITY,
DEF_INMEMORY_COMPRESSION, DEF_INMEMORY_DISTRIBUTE
FROM
DBA_TABLESPACES
WHERE tablespace_name = 'EXAMPLE';

SQL> SELECT name, value FROM V$SYSSTAT WHERE name IN ('db block gets from cache','consistent gets from cache','physical reads cache');

To take full Export of Database

expdp system DUMPFILE=expfull.dmp FULL=y TRANSPORTABLE=ALWAYS LOGFILE=exp.log

To Rename Tablespace Name

Rename USERS GT/u01/app/oracle/oradata/orcl2/users01.dbf to /u01/app/oracle/oradata/orcl2/users_new01.dbf.

SQL> SELECT tablespace_name FROM dba_tablespaces;
SQL> ALTER TABLESPACE users RENAME TO users_new;
Tablespace altered.
SQL> ALTER TABLESPACE users_new OFFLINE;
Tablespace altered.
SQL> EXIT
$
$ mv /u01/app/oracle/oradata/orcl2/users01.dbf
/u01/app/oracle/oradata/orcl2/us

impdp system FULL=Y dumpfile=expfull.dmp
TRANSPORT_DATAFILES='/u01/app/oracle/oradata/orcl2/test01.dbf','
/u01/app/oracle/oradata/orcl2/users01.dbf','/u01/app/oracle/orad
ata/orcl2/example01.dbf' logfile=import.log

To create External Table

creating external table "SYS_SQLLDR_X_EXT_TAB1"
CREATE TABLE "SYS_SQLLDR_X_EXT_TAB1"
(
"ID" NUMBER,
"PROD_NAME" VARCHAR2(10)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'tab1.bad'
LOGFILE 'hr_%p.log_xt'
READSIZE 1048576
FIELDS TERMINATED BY "," LRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
"ID" CHAR(255),
"PROD_NAME" CHAR(255)
)
)
location
(
'tab1.dat'
)
)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table HR.TAB1
INSERT /*+ append parallel(auto) */ INTO HR.TAB1
(
ID,
PROD_NAME
)

To create PDB

col con_id format 999
col name format A10
select con_id, NAME, OPEN_MODE,DBID, CON_UID from V$PDBS;

CREATE PLUGGABLE DATABASE pdb_part2 ADMIN USER pdb_part2_admin
IDENTIFIED BY oracle_4U ROLES=(CONNECT)
CREATE_FILE_DEST='/u01/CDBPART2/pdb_part2/';

alter pluggable database pdb_part2 open;

SQL> col name format A30
SQL> select name from v$services;

To check Tablespace

SQL> col tablespace_name format A8
SQL> col file_id format 99
SQL> col con_id format 9
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id ;
select FILE_NAME, TABLESPACE_NAME, FILE_ID, con_id from cdb_data_files order by con_id, file_id ;

SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID from dba_data_files;

SQL> col file_name format A60
SQL> select FILE_NAME, TABLESPACE_NAME, FILE_ID from cdb_temp_files;

-----------------------
To create hr user

SQL> alter session set container = pdb1;
Session altered.
SQL> create user hrmaster identified by hrmaster;
User created.
SQL> select USERNAME,COMMON,CON_ID from cdb_users order by username;

select * from test.emp

create table hr.test_table (id number(8),name varchar2(50),now date) tablespace users;
insert into hr.test_table values (1,'First test',sysdate);
insert into hr.test_table values (2,'Second test',sysdate);
insert into hr.test_table values (3,'Thirdt test',sysdate);
commit;

select * from sys.ts_pitr_check where (ts1_name in ('TBS_EMP') and ts2_name not in ('TBS_EMP')) or (ts1_name not in ('TBS_EMP') and ts2_name in ('TBS_EMP'));

RECOVER TABLE HR.EMP OF PLUGGABLE DATABASE PDB1
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/u01/ora/db01'
REMAP TABLE 'HR'.'EMP':'EMP_RECVR';

sequence=154
sequence=158

or

RMAN> recover table scott.test1
until time "to_date('08/17/2014 21:01:15','mm/dd/yyyy hh24:mi:ss')"
auxiliary destination '/u01/app/oracle/aux'
remap table scott.test1:test1_temp;

or

RECOVER TABLE HR.EMP OF PLUGGABLE DATABASE PDB1
until time "to_date('02/28/2016 16:24:15','mm/dd/yyyy hh24:mi:ss')"
AUXILIARY DESTINATION '/u01/ora/12cdump'
REMAP TABLE 'HR'.'EMP':'EMP_RECVR';
or

To find file (Unix Command)

server@oc5327222715 Desktop]$ find . -name lab*28* 2>/dev/null
./Training/RAC_TRAINING_DOC/lab_28fab.log
./Training/lab2_28jan
[shahique@oc5327222715 Desktop]$

RMAN> RECOVER TABLE sh.customers OF PLUGGABLE DATABASE testdb1

UNTIL TIME ‘sysdate-10/1440’

AUXILIARY DESTINATION ‘/u01/app/oracle/backup/cdb12c’

DATAPUMP DESTINATION ‘/u01/app/oracle/backup/cdb12c’;

QL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 ORA12GCONTAINER READ WRITE NO

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
cdbtest OPEN

1 row selected.

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT

SQL> select * from cdb_pdbs;

SQL> select instance_name,status from v$instance;

INSTANCE_NAME STATUS
---------------- ------------
cdbtest OPEN

1 row selected.

SQL> show con_name

@?/rdbms/admin/catcdb.sql

==========================

The following error occurs while creating the pluggable database:

ORA-00604: error occurred at recursive SQL level 1
ORA-01917: user or role 'PDB_DBA' does not exist
Cause

This usually arises if CDB is created manually using the traditional scripts such as:

@$ORACLE_HOME/rdbms/admin/catalog.sql;
@$ORACLE_HOME/rdbms/admin/catblock.sql;
@$ORACLE_HOME/rdbms/admin/catproc.sql;
@$ORACLE_HOME/rdbms/admin/catoctk.sql;
@$ORACLE_HOME/rdbms/admin/owminst.plb;
@$ORACLE_HOME/sqlplus/admin/pupbld.sql;
Solution

Please note that the above scripts are meant for non-CDB or traditional database.
For CDB database, only the "catcdb.sql" script is enough. This will internally call all the required scripts in the correct order.

From Oracle Documentation:
http://docs.oracle.com/database/121/ADMIN/cdb_create.htm#ADMIN13529
"When you use the CREATE DATABASE statement to create a CDB, you must complete additional actions before you have an operational CDB. These actions include building views on the data dictionary tables and installing standard PL/SQL packages in the root. You perform these actions by running the supplied "catcdb.sql" script."

The other requirement is to set PERL path properly before executing "catcdb.sql".
By default, PERL is shipped with 12c binaries and located in "$ORACLE_HOME/perl"

Though the CDB can be created manually, Oracle strongly recommends using the Database Configuration Assistant (DBCA) instead of the CREATE DATABASE SQL statement to create a CDB, because using DBCA is a more automated approach, and your CDB is ready to use when DBCA completes.

Reference:
http://docs.oracle.com/database/121/ADMIN/cdb_create.htm#ADMIN13521

Customer did not have perl binaries in his path when he ran catcdb.sql.

The "ORA-01917: user or role 'PDB_DBA' does not exist" error from manually creating the container/pluggable databases is related to the catalog script
catcdb.sql. Before running this script, you need to update the path variable with the location of perl binaries:

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$PATH
After doing this, customer was able to run catcdb.sql and create the pluggable databases successfully.
Solution

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin/:$PATH

Conclusion:
Set your storage environment in the pluggable database will keep your environment clean. Don't forget this after the creating a pluggable database, you will see the problem after you add tablespaces to the pluggable database, and then it can and it will use the global settings from the CDB.
Important is that OMF the PDB_FILE_NAME_CONVERT and FILE_NAME_CONVERT settings overruled , and use the settings OMF of the CDB setting.

First validate the CDB environment. Below you will see that this is and CDB with one seed plugable database.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_cdb

SQL> show spparameter db_create_file_dest;

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb

Case 1; Lets create a plugable database PDB_T0 without altering settings.

SQL> CREATE PLUGGABLE DATABASE PDB_T0 ADMIN USER t0 IDENTIFIED BY t0 roles=(DBA)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 2

Pluggable database created.

Case 2; Lets create a pluggable PDB_T1 with altering the OMF settings.

SQL> alter session set db_create_file_dest='/u02/app/oradata_pdb1' ;

Session altered.

Quick view on what are my settings so i can validate this later on.

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_pdb1

SQL> show spparameter db_create_file_dest

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb

SQL> CREATE PLUGGABLE DATABASE PDB_T1 ADMIN USER t1 IDENTIFIED BY t1 roles=(DBA)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M); 2

Pluggable database created.

Case 3; Lets create a plugable database PDB_T2 without OMF settings.

For creating pluggable database without OMF you need the parameter setting PDB_FILE_NAME_CONVERT or during the creating of the pluggable database FILE_NAME_CONVERT. Those settings are important

SQL> alter session reset db_create_file_dest;

Session altered.

Set the parameter at session level PDB_FILE_NAME_CONVERT

SQL> ALTER SESSION SET PDB_FILE_NAME_CONVERT = '/u02/app/oradata_cdb/CDB1/pdbseed','/u02/app/oradata_pdb2/PDB_T2/';

Session altered.

SQL> !mkdir /u02/app/oradata_pdb2/PDB_T2/

SQL> show parameter db_create_file_dest

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_cdb

SQL> show spparameter db_create_file_dest

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb

SQL> show parameter PDB_FILE_NAME_CONVERT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string /u02/app/oradata_cdb/CDB1/pdbs
eed, /u02/app/oradata_pdb2/PDB
_T2/

SQL> show spparameter PDB_FILE_NAME_CONVERT

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
*

SQL>CREATE PLUGGABLE DATABASE PDB_T2 ADMIN USER t2 IDENTIFIED BY t2 roles=(DBA)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M);

Pluggable database created.

Case 4; Lets create a plugable database PDB_T3 without OMF settings.

SQL> alter session reset db_create_file_dest;

Session altered.

show parameter db_create_file_dest
show spparameter db_create_file_dest
show parameter PDB_FILE_NAME_CONVERT
show spparameter PDB_FILE_NAME_CONVERT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string /u02/app/oradata_cdb, /u02/app
/oradata_pdb2
SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* pdb_file_name_convert string

SQL> !mkdir /u02/app/oradata_pdb3/CDB1/PDB_T3/

Using the FILE_NAME_CONVERT in the pluggable database command.

SQL> CREATE PLUGGABLE DATABASE PDB_T3 ADMIN USER t3 IDENTIFIED BY t3 roles=(DBA)
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
FILE_NAME_CONVERT=('/u02/app/oradata_cdb/CDB1/pdbseed/','/u02/app/oradata_pdb3/CDB1/PDB_T3/');
2 3

Pluggable database created.

Now lets see what we have created in the CDB. If correct we have created 4 pluggable DB's with different setting related to the CDB storage.

SQL> alter pluggable database all open;

Pluggable database altered.

PDBS

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_T0 READ WRITE NO
4 PDB_T1 READ WRITE NO
5 PDB_T2 READ WRITE NO
6 PDB_T3 READ WRITE NO

All the pluggble database are read write and open. Investigation where are the datafiles placed of the pluggable database on the Oracle CDB environment.

SQL> select con_id, file_name from cdb_data_files order by 1

CON_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
1 /u02/app/oradata_cdb/CDB1/system01.dbf
1 /u02/app/oradata_cdb/CDB1/sysaux01.dbf
1 /u02/app/oradata_cdb/CDB1/undotbs01.dbf
1 /u02/app/oradata_cdb/CDB1/users01.dbf
2 /u02/app/oradata_cdb/CDB1/pdbseed/sysaux01.dbf
2 /u02/app/oradata_cdb/CDB1/pdbseed/system01.dbf
3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclbjk0_.dbf
3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclbjk1_.dbf
4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf
4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf
5 /u02/app/oradata_pdb2/PDB_T2/system01.dbf
5 /u02/app/oradata_pdb2/PDB_T2/sysaux01.dbf
6 /u02/app/oradata_pdb3/CDB1/PDB_T3/system01.dbf
6 /u02/app/oradata_pdb3/CDB1/PDB_T3/sysaux01.dbf

As you can see no changes are made on the PDB's related to different storage environments of the CDB. What will happen if new datafiles or tablespaces are added to the pluggable database.
So lets start clean session and database

Test case with adding tablespaces to the pluggable databases

SQL> startup force;

ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size 2287864 bytes
Variable Size 788530952 bytes
Database Buffers 452984832 bytes
Redo Buffers 8859648 bytes
Database mounted.
Database opened.

Investigate the PDB's on the CDB

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB_T0 MOUNTED
4 PDB_T1 MOUNTED
5 PDB_T2 MOUNTED
6 PDB_T3 MOUNTED

Open all the PDB's inside the CDB

SQL> alter pluggable database all open;

Pluggable database altered.

Start with adding tablespace to TBD_T1

SQL> show parameter db_create_file_dest
show spparameter db_create_file_dest
show parameter PDB_FILE_NAME_CONVERT
show spparameter PDB_FILE_NAME_CONVERT

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_cdb

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
pdb_file_name_convert string

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* pdb_file_name_convert string

Create the tablespace

SQL> create tablespace ts_PDB_1 datafile size 10M;

Tablespace created.

Start with adding tablespace to TBD_T2

SQL> ALTER SESSION SET CONTAINER = PDB_T2;

Session altered.

SQL> create tablespace ts_PDB_2 datafile size 10M;

Tablespace created.

Start with adding tablespace to TBD_T3

SQL> ALTER SESSION SET CONTAINER = PDB_T3;

Session altered.

SQL> create tablespace ts_PDB_3 datafile size 10M;

Tablespace created.

Start with adding tablespace to TBD_T0

SQL> create tablespace ts_PDB_0 datafile size 10M;

Tablespace created.

Results adding tablespaces to PDBS
We have now finished the adding tabespaces to the pluggable database. What is now the impact of this. Below you will see where the datafiles are placed for the pluggable database inside the CDB storage environment.

SQL> connect / as sysdba
Connected.

Storage impact inside the CDB

SQL> select con_id, file_name from cdb_data_files order by 1;

CON_ID FILE_NAME
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------
1 /u02/app/oradata_cdb/CDB1/system01.dbf
1 /u02/app/oradata_cdb/CDB1/sysaux01.dbf
1 /u02/app/oradata_cdb/CDB1/undotbs01.dbf
1 /u02/app/oradata_cdb/CDB1/users01.dbf
2 /u02/app/oradata_cdb/CDB1/pdbseed/sysaux01.dbf
2 /u02/app/oradata_cdb/CDB1/pdbseed/system01.dbf
3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclbjk1_.dbf
3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclbjk0_.dbf
3 /u02/app/oradata_cdb/CDB1/ED1B30729CDD1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_0_9bcng0ml_.dbf
4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf
4 /u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf
4 /u02/app/oradata_cdb/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_1_9bcn4moz_.dbf
5 /u02/app/oradata_pdb2/PDB_T2/system01.dbf
5 /u02/app/oradata_pdb2/PDB_T2/sysaux01.dbf
5 /u02/app/oradata_cdb/CDB1/ED1B30729CDF1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_2_9bcn5j61_.dbf
6 /u02/app/oradata_pdb3/CDB1/PDB_T3/system01.dbf
6 /u02/app/oradata_cdb/CDB1/ED1B72D3EBE61FC0E043960A0A0A0D6E/datafile/o1_mf_ts_pdb_3_9bcn6jp1_.dbf
6 /u02/app/oradata_pdb3/CDB1/PDB_T3/sysaux01.dbf

18 rows selected.

See here all the new tablespaces with datafiles are created with the OMF settings of the CDB. No additionele settings where add to the pluggable database. For me it will be a spaghetti environment, where you will lost the logica related to storage.

Now lets specified settings on pluggable database level.

SQL> alter session set container=PDB_T1;

Session altered.

SQL> show parameter db_create_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_cdb

Here we set the OMF setting of the PDB.

SQL> alter system set db_create_file_dest='/u02/app/oradata_pdb1' scope=both;

System altered.

SQL> show parameter db_create_file_dest;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_pdb1

SQL> SQL> show spparameter db_create_file_dest;

SID NAME TYPE VALUE
-------- ----------------------------- ----------- ----------------------------
* db_create_file_dest string /u02/app/oradata_cdb

See how we can get information from the root CDB

connect / as sysdba

select DB_UNIQ_NAME, PDB_UID, NAME, VALUE$ from pdb_spfile$;

DB_UNIQ_NAME PDB_UID NAME VALUE$
------------------------------ ---------- -------------------- ------------------------------
CDB1 1706463289 db_create_file_dest '/u02/app/oradata_pdb1'

Use the PDB_UID to finded your pluggable databse name.

SQL> select * from dba_pdbs where CON_UID=1706463289;

PDB_ID PDB_NAME DBID CON_UID GUID STATUS CREATION_SCN
---------- -------------------- ---------- ---------- -------------------------------- ------------- ------------
4 PDB_T1 1706463289 1706463289 ED1B30729CDE1F13E043960A0A0A08DF NORMAL 1509163

Go back to the PDB_T1

SQL> alter session set container=PDB_T1;

Session altered.

SQL> show parameter db_create_file;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest string /u02/app/oradata_pdb1

Create a new tablespace for this database with the new settings OMF

SQL> create tablespace PDB_A datafile size 10M;

Tablespace created.

Create a new tablespace for this database with the new settings OMF

SQL> create tablespace PDB_A datafile size 10M;

SQL> select file_name from dba_data_files;

FILE_NAME
-------------------------------------------------------------------------------------------------
/u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_sysaux_9bclms9g_.dbf
/u02/app/oradata_cdb/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_ts_pdb_1_9bcn4moz_.dbf
/u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_system_9bclmsb2_.dbf
/u02/app/oradata_pdb1/CDB1/ED1B30729CDE1F13E043960A0A0A08DF/datafile/o1_mf_pdb_A_9bco9kdz_.dbf

-------------------------

In the following example, new PDB database PDBTEST is created from the seed PDB, PDB$SEED.

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB12C READ WRITE
4 PDB_PLUG_NOCOPY MOUNTED
5 EXNONCDB MOUNTED
6 PDBTEST READ WRITE

FILE_NAME_CONVERT clause is used to specify the directory structure for the new PDB

SQL> CREATE PLUGGABLE DATABASE pdbtest1
ADMIN USER pdba IDENTIFIED BY pdba ROLES = (dba)
FILE_NAME_CONVERT = ('/oradata/db12c/pdbseed/','/oradata/db12c/pdbtest1/');
2 3

Pluggable database created.

Open the newly created PDB

SQL> alter pluggable database pdbtest1 open;

Pluggable database altered.

Query v$pdbs to check the status of the newly created PDB

SQL> select con_id,name,open_mode from v$pdbs;

CON_ID NAME OPEN_MODE
---------- ------------------------------ ----------
2 PDB$SEED READ ONLY
3 PDB12C READ WRITE
4 PDB_PLUG_NOCOPY MOUNTED
5 EXNONCDB MOUNTED
6 PDBTEST READ WRITE
7 PDBTEST1 READ WRITE

6 rows selected.

Verify that the datafiles for the newly created PDB are created in the right location

SQL> select con_id,file_name from cdb_data_files order by con_id;

CON_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
1 /oradata/db12c/users01.dbf
1 /oradata/db12c/undotbs01.dbf
1 /oradata/db12c/sysaux01.dbf
1 /oradata/db12c/system01.dbf
2 /oradata/db12c/pdbseed/system01.dbf
2 /oradata/db12c/pdbseed/sysaux01.dbf
3 /oradata/db12c/pdb12c/SAMPLE_SCHEMA_users01.dbf
3 /oradata/db12c/pdb12c/example01.dbf
3 /oradata/db12c/pdb12c/sysaux01.dbf
3 /oradata/db12c/pdb12c/system01.dbf
6 /oradata/db12c/pdbtest/system01.dbf
6 /oradata/db12c/pdbtest/sysaux01.dbf
6 /oradata/db12c/pdbtest/SAMPLE_SCHEMA_users01.dbf
6 /oradata/db12c/pdbtest/example01.dbf
7 /oradata/db12c/pdbtest1/sysaux01.dbf
7 /oradata/db12c/pdbtest1/system01.dbf

Backup

---Back up the root and/or individual PDBs.
RMAN> CONNECT TARGET /
RMAN> BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb;
RMAN> BACKUP PLUGGABLE DATABASE hr_pdb PLUS ARCHIVELOG;

The BACKUP PLUGGABLE DATABASE "CDB$ROOT", sales_pdb command backs up all
SYSAUX
TBS2
SYSAUX
data files of the root container, namely the SYSTEM, SYSAUX and UNDO data files, and then all
data files of the sales_pdb PDB, namely the SYSTEM, SYSAUX, and TBS2 data files.

Backup whole PDBs with RMAN:

RMAN> CONNECT TARGET /
RMAN> BACKUP PLUGGABLE DATABASE sales_pdb;
RMAN> BACKUP PLUGGABLE DATABASE sales_pdb, hr_pdb;

RMAN>
RMAN>
RMAN>
RMAN>
CONNECT TARGET /
REPORT SCHEMA;
BACKUP TABLESPACE sales_pdb:tbs2;
BACKUP TABLESPACE hr_pdb:system,
sales_pdb:sysaux;
RMAN> BACKUP TABLESPACE sysaux, hr_pdb:sysaux;

PDB Backup: User-Managed Hot PDB Backup
Perform a user-managed hot PDB backup.
SQL>
SQL>
SQL>
SQL>
CONNECT system@sales_pdb
ALTER DATABASE BEGIN BACKUP;
!cp sales_pdb/datafiles /backup_dir_sales_pdb
ALTER DATABASE END BACKUP;

The CDB instance can start up with a missing temporary file. If any of the temporary files do
not exist when the CDB instance is started, they are created automatically and the CDB
opens normally. When this happens, a message like the following appears in the alert log
during startup:
• Re-creating the temp file /u01/app/oracle/oradata/CDB1/temp01.dbf
You can decide a manual recreation instead, while connected to root:

SQL> ALTER TABLESPACE temp ADD TEMPFILE
2 '/u01/app/oracle/oradata/CDB1/temp02.dbf' SIZE 20M;
SQL> ALTER TABLESPACE temp DROP TEMPFILE
3 '/u01/app/oracle/oradata/CDB1/temp01.dbf‘;

Media Failure: Control File Loss
Similar to non-CDBs: CDB mounted

RMAN> CONNECT TARGET /
RMAN> STARTUP NOMOUNT;
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> ALTER DATABASE MOUNT;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN RESETLOGS;
RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

Media Failure: Root SYSTEM or UNDO Data File
Similar to non-CDBs: CDB mounted

RMAN> STARTUP MOUNT;
RMAN> RESTORE TABLESPACE undo1;
RMAN> RECOVER TABLESPACE undo1;
RMAN> ALTER DATABASE OPEN;
RMAN> ALTER PLUGGABLE DATABASE ALL OPEN;

SQL> CONNECT system@sales_pdb
SQL> ALTER TABLESPACE tbs2 OFFLINE IMMEDIATE;
RMAN> CONNECT TARGET /
RMAN> RESTORE TABLESPACE sales_pdb:tbs2;
RMAN> RECOVER TABLESPACE sales_pdb:tbs2;
SQL> ALTER TABLESPACE tbs2 ONLINE;

RMAN> run
2> {
3> sql 'alter session set nsl ls_date_format =' "dd/mm/yyyy hh24:mi:ss"';
4> set until time '07/03/2016 13:34:27';
5> allocate channel c1 type disk format '/u01/ora/backkup/df_%d_%s_%p_%T.bus';
6> switch datafile all;
7> restore database;
8> recover database;
9> release channel c1;
10> }

RMAN> run
{
sql 'alter session set nsl ls_date_format =' "dd/mm/yyyy hh24:mi:ss"';
set until time '07/03/2016 13:34:27';
allocate channel c1 type disk format '/u01/ora/backkup/df_%d_%s_%p_%T.bus';
switch datafile all;
restore database;
recover database;
release channel c1;
}

If the dataS missing or corrupted belongs to a PDB, the PDB needs not be closed. The data
T file ransf
G -t
file with the error is taken OFFLINE IMMEDIATE.

RMAN> ALTER PLUGGABLE DATABASE PDB1 CLOSE;
RMAN> RUN {
SET UNTIL SCN = 1851648 ;
RESTORE pluggable DATABASE pdb1;
RECOVER pluggable DATABASE pdb1

A PDB incarnation is a subincarnation of the CDB. For example, if the CDB is incarnation 5,
and a PDB is incarnation 3, then the fully specified incarnation number of the PDB is (5, 3).
The initial incarnation of a PDB is 0. To view the incarnation of a PDB, query the
V$PDB_INCARNATION view.

AUXILIARY DESTINATION='/u01/app/oracle/oradata';
ALTER PLUGGABLE DATABASE pdb1 OPEN RESETLOGS;
}

Every PDB has a default service.

select * from cdb_services or V$SERVICES
;

Service name has to be unique across CDBs. o ui
ฺc
bm nt G
SQL> CONNECT / AS SYSDBA
inฺi de
SQL> CONNECT sys@PDBtest AS SYSDBA 1@u
b0 his St
SQL> CONNECT local_user1@hostname1:1525/PDBHR
d
SQL> CONNECT common_user2@PDBdev
sdc se t
g
SQL> SHOW CON_NAME
m ( e to u
Tea

If you use the service name, you can use the EasyConnect syntax or the alias from the
tnsnames.ora.
Using EasyConnect, you would enter the following connect string:
SQL> CONNECT
username@hostname:portnumber/service_name
Using the tnsnames.ora file, you would enter the following connect string:
SQL> CONNECT username@net_service_name
•
To connect to a desired PDB, use either Easyconnect or the alias from the
tnsnames.ora file, for example as shown in the slide. In our examples, the net service
name in the tnsnames.ora matches the service name.
Two possible ways to switch connection
between containers within a CDB:
• Reconnect:
SQL> CONNECT / AS SYSDBA
SQL> CONNECT local_user1@PDBdev
•
PDBHR
PDBdev
Use ALTER SESSION statement:
SQL>
SQL>
SQL>
SQL>
CONNECT sys@PDBtest AS SYSDBA
ALTER SESSION SET CONTAINER=PDBHR;
SHOW CON_NAME
ALTER SESSION SET CONTAINER=CDB$ROOT;

SQL> CONNECT sys@CDB1 AS SYSDBA
SQL> STARTUP NOMOUNT
SQL> SELECT name, open_mode
FROM
v$pdbs;
SQL> CONNECT sys@CDB1 AS SYSDBA
SQL> STARTUP MOUNT
SQL> ALTER DATABASE cdb1 MOUNT;
SQL> SELECT name,open_mode
2 FROM
v$pdbs;
NAME
----------------
PDB$SEED
PDB1
PDB2
OPEN_MODE
----------
MOUNTED
MOUNTED
MOUNTED

SQL> CONNECT sys@CDB1 AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN;
SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
SQL> SELECT name,open_mode
2 FROM
v$pdbs;
NAME
----------------
PDB$SEED
PDB1
PDB2
OPEN_MODE
----------
READ ONLY
READ WRITE
READ WRITE

SQL> CONNECT / AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE pdb1
CLOSE IMMEDIATE;
SQL> ALTER PLUGGABLE DATABASE
ALL EXCEPT pdb1 CLOSE;
SQL> ALTER PLUGGABLE DATABASE
ALL CLOSE;
SQL> CONNECT sys@pdb1 AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE CLOSE;
Or
SQL> SHUTDOWN IMMEDIATE;

Automatically keep PDBs state after CDB STARTUP

:
SQL> ALTER PLUGGABLE DATABASE pdb1 SAVE STATE;

Restricted read-write:

SQL> ALTER PLUGGABLE DATABASE pdb1 OPEN RESTRICTED;
SQL> SELECT name, open_mode, restricted FROM v$pdbs;
NAME
----------------------
PDB1
PDB2
OPEN_MODE
----------
READ WRITE
READ WRITE
RES
---
YES
NO

ALTER PLUGGABLE DATABASE ALL OPEN READ Only

SQL> ALTER PLUGGABLE DATABASE ALL CLOSE

SQL> ALTER PLUGGABLE DATABase all open;

SQL> CONNECT sys@pdb1 AS SYSDBA
SQL> ALTER PLUGGABLE DATABASE DATAFILE '/u03/pdb1_01.dbf‘
ONLINE;

SQL> SELECT G.NAME DISK_GROUP, C.*
2 FROM V$ASM_CLIENT C, V$ASM_DISKGROUP G
3 WHERE C.GROUP_NUMBER = G.GROUP_NUMBER
4 AND G.NAME = 'DATA';

SQL> SELECT GROUP_NUMBER, PASS, STATE FROM V$ASM_OPERATION;

$ crsctl stop resource ora.DATA2.dg

CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'host01'
CRS-2673: Attempting to stop 'ora.DATA2.dg' on 'host02'
CRS-2677: Stop of 'ora.DATA2.dg' on 'host01' succeeded
CRS-2677: Stop of 'ora.DATA2.dg' on 'host02' succeeded
$ srvctl start diskgroup -diskgroup DATA2

$ crsctl stat res ora.DATA2.dg
NAME=ora.DATA2.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE
, ONLINE
STATE=ONLINE on host01, ONLINE on host02
e
an
s
) ha eฺ
$ srvctl status diskgroup -diskgroup DATA2

The ASMCMD lsattr –lm –G command will show the same information for a disk group.
ASMCMD> lsattr -lm -G DATA

CREATE DISKGROUP mydgroup1 NORMAL REDUNDANCY
FAILGROUP mycontroller1 DISK
'/devices/diska1',
'/devices/diska2',
'/devices/diska3',
'/devices/diska4'
FAILGROUP mycontroller2 DISK
'/devices/diskb1',
'/devices/diskb2',
'/devices/diskb3',
'/devices/diskb4'
ATTRIBUTE 'compatible.asm'='11.2','compatible.rdbms'='11.2',
'sector_size'='4096';

SQL> SELECT NAME, TYPE, TOTAL_MB, FREE_MB, REQUIRED_MIRROR_FREE_MB RMFM, USABLE_FILE_MB FROM V$ASM_DISKGROUP;
NAME
TYPE
TOTAL_MB FREE_MB RMFM USABLE_FILE_MB
----
------ -------- ------- ----- --------------
DATA
NORMAL
9998
4248 1449
1399
ACFS
EXTERN
9996
3706
0
3706
FRA
EXTERN
7497
7356
0
7356

The asmcmd lsdg command is a preformatted view of the data from V$ASM_DISKGROUP.

$ asmcmd lsdg

SQL> SELECT G.NAME DISK_GROUP, D.NAME, D.STATE, D.TOTAL_MB,
D.FREE_MB
FROM V$ASM_DISK D, V$ASM_DISKGROUP G
WHERE D.GROUP_NUMBER = G.GROUP_NUMBER
AND G.NAME = 'DATA';

The ALTER DISKGROUP command enables you to remove
disks or failure groups from an existing disk group:
SQL> ALTER DISKGROUP FRA DROP DISK FRA_DISK1, FRA_DISK4;

The ASMCMD lsop command shows ASM operations:
ASMCMD> lsop -G DATA

SQL> SELECT G.NAME DISK_GROUP, O.*
2 FROM V$ASM_OPERATION O, V$ASM_DISKGROUP G
3 WHERE O.GROUP_NUMBER = G.GROUP_NUMBER;

===========================FLEX CLUSTER==============

SQL>

select distinct i.instance_name asm_instance_name,
c.instance_name client_instance_name, c.db_name, c.status
from gv$instance i, gv$asm_client c
where i.inst_id=c.inst_id;

SQL> SELECT SID, SERIAL#, INST_ID
2 FROM GV$SESSION WHERE USERNAME='JMW';
SID
SERIAL#
INST_ID
---------- ---------- ----------
140
3340
2
SQL> ALTER SYSTEM KILL SESSION '140,3340,@2';
e
System altered.
s
ran
n-t
SQL>

• ALTER SYSTEM CHECKPOINT LOCAL affects the current instance.
• ALTER SYSTEM CHECKPOINT or ALTER SYSTEM CHECKPOINT GLOBAL affect all
instances in the cluster database.
• ALTER SYSTEM SWITCH LOGFILE affects only the current instance.

To force a global log switch, use the ALTER SYSTEM ARCHIVE LOG CURRENT
statement.
- The INSTANCE option of ALTER SYSTEM ARCHIVE LOG enables you to archive
each online redo log file for a specific instance
• The INSTANCE option of ALTER SYSTEM ARCHIVE LOG enables you to archive each
online redo log file for a specific instance.

======================================================

If you need to close all PDBs in all the CDB instances but keep the root opened for
maintenance purposes, use the following command:

ALTER PLUGGABLE DATABASE ALL CLOSE INSTANCES = ALL;

When the PDB is closed on all the CDB instances, the data files of the PDB are closed.
To display the PDB open mode, use the V$PDBS view OPEN_MODE column in each of the
CDB instances. It may display MOUNTED in some of the instances and READ WRITE in other
instances

To determine the location of the OCR:

$ ocrcheck -config
Oracle Cluster Registry con

Use the cluvfy utility or the ocrcheck command to
check the integrity of the OCR:

$ cluvfy comp ocr –n all -verbose
# ocrcheck

Status of Oracle Cluster Registry is as follows :
Version
:
4Total space (kbytes)
:409568
Used space (kbytes)
:3320
Available space (kbytes) :
406248ID
:92167993
Device/File Name
:+DATA
Device/File integrity check succeeded
...Cluster registry integrity check succeeded
Logical corruption check succeeded

To check n integrity of the voting disks, examine ocssd.log. Errors with the voting disks
a the in the log. The following is a snippet of the output that indicates what an error may

iqulike:
happear
ha look
s
$ grep voting ocssd.log

To determine the list of interfaces available to the cluster:

$ oifcfg iflist –p –n
eth0 192.0.2.0 UNKNOWN 255.255.255.0
eth1 192.168.1.0 PRIVATE 255.255.255.0
eth1 169.254.0.0 UNKNOWN 255.255.0.0
eth2 192.168.1.0 PRIVATE 255.255.255.0

To determine the Virtual IP (VIP) host name, VIP address,
VIP subnet mask, and VIP interface name:

$ srvctl config nodeapps -a
Network 1 exists
Subnet IPv4: 192.0.2.0/255.255.255.0/eth0, dhcp
Subnet IPv6:
VIP exists: network number 1, hosting node host01
VIP IPv4 Address: -/host01-vip/192.0.2.237
VIP IPv6 Address:
VIP exists: network number 1, hosting node host02
VIP IPv4 Address: -/host02-vip/192.0.2.231
VIP IPv6 Address:

1. Stop all services running on the node whose VIP address
you want to change:

$ srvctl stop service -d orcl -s sales,oltp -n host01
2. Confirm the current IP address for the VIP address:
$ srvctl config vip -n host01
VIP exists: network number 1, hosting node host01
VIP IPv4 Address: -/host01-vip/192.0.2.237
VIP IPv6 Address:

3. Stop the VIP address:
$ srvctl stop vip -n host01

4. Verify that the VIP address g no longer running by
executing the ifconfig -a command.

5. To change power limit

SQL> ALTER SYSTEM set ASM_POWER_LIMIT=1 SCOPE=BOTH SID='+ASM2';
SQL> ALTER SYSTEM set ASM_POWER_LIMIT=5 SCOPE=BOTH SID='+ASM1';
System altered.
SQL>2
column NAME format A16
column VALUE format A16
select inst_id, name, value from GV$PARAMETER
where name like 'asm_power_limit';

Restart the ASM instance on host01.

srvctl start asm -n host01

[grid@host02 ~]$ . oraenv
ORACLE_SID = [grid] ? +ASM3
The Oracle base has been set to /u01/app/grid
[grid@host02 ~]$ asmcmd lsct data
DB_Name
+ASM
orcl
orcl
Status
Software_Version Compatible_version Instance_Name
CONNECTED
12.1.0.2.0
12.1.0.2.0
+ASM3
CONNECTED
12.1.0.2.0
12.1.0.2.0
orcl_1
CONNECTED
12.1.0.2.0
12.1.0.2.0
orcl_3
Disk_Group
DATA
DATA
DATA
[grid@host02 ~]$ asmcmd lsct fra
DB_Name
Status
Software_Version Compatible_version Instance_Name Disk_Group

To Apply Datapatch

Yes, due to permission issue with failed to start from SRVCTL. I reviewed the patch logs, applied successfully on both DB and GRID home and it failed on datapatch. Please apply datapatch (changes will be applied into database registry) manually on failed instance (EASYDB).

/u01/oracle/NFT/product/12.1.0.2/OPatch/datapatch

We found Doc ID 1989922.1 and corrected the permissions as suggested. We did:

easyserver[/u02/oracle/12.1.0/grid/lib]$ ls -l libclntsh.so
-rwx------ 1 oracrs dba 61812395 May 17 15:44 libclntsh.so
easyserver[/u02/oracle/12.1.0/grid/lib]$ ls -l libclntshcore.so
-rwx------ 1 oracrs dba 4187247 May 17 15:44 libclntshcore.so
easyserver[/u02/oracle/12.1.0/grid/lib]$ ls -l libocci.so
-rwx------ 1 oracrs dba 4996222 May 17 15:44 libocci.so
easyserver[/u02/oracle/12.1.0/grid/lib]$ ls -l libttsh12.so
-rwx------ 1 oracrs dba 83299908 May 17 15:44 libttsh12.so

chmod 755 libclntsh.so libclntshcore.so libocci.so libttsh12.so

This appears to have fixed the issue and we can now start the instances with srvctl.

I would still like to know if, from the log files, the PSU appears to have been correctly applied. I noticed some warnings regarding SQL scripts associated with the patches that could not be run due to the instance being down. Do I need to run these manually?

Supported Note from Oracle

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
IBM AIX on POWER Systems (64-bit)
Symptoms

Can´t start any database on the cluster using the srvctl command.

Executing the command "crsctl status res -t" and the status shows UNKNOWN.
ora.racdb.db
1 ONLINE UNKNOWN racnd01
2 ONLINE UNKNOWN racnd02
ora.racdbs.db
1 ONLINE UNKNOWN racnd01
2 ONLINE UNKNOWN racnd02

Tried to remove, add and enable the database and it still fails to start and the status remains as UNKNOWN.

./srvctl stop instance -d racdb -i racdb1
./srvctl remove instance -d racdb -i racdb1
./srvctl add instance -d racdb -i racdb1 -n racnd01
./srvctl enable instance -d racdb -i racdb1

./srvctl start instance -d racdb -i racdb1

PRCR-1013 : Failed to start resource ora.racdb.db
PRCR-1064 : Failed to start resource ora.racdb.db on node racnd01
CRS-2680: Clean of 'ora.racdb.db' on 'racnd01' failed
CRS-5802: Unable to start the agent process

Startup all the databases from sqlplus in all nodes work fine, but srvctl could not start the instance.
Changes

Applied patch on GI home.
Cause

The library $ORACLE_HOME/lib/libttsh11.sh has incorrect permission.

To verify the permissions of the binaries of the GI home following cluvfy was executed as a grid owner
$ cluvfy comp software -n all -verbose
Verifying software

Check: Software

Component: crs

Node Name: wpydbqa02
/u01/app/11.2.0/grid/lib/libttsh11.so..."Permissions" did not match reference
Permissions of file "/u01/app/11.2.0/grid/lib/libttsh11.so" did not match the expected value. [Expected = "0755" ; Found = "0700"]
Node Name: wpydbqa01
/u01/app/11.2.0/grid/lib/libttsh11.so..."Permissions" did not match reference
Permissions of file "/u01/app/11.2.0/grid/lib/libttsh11.so" did not match the expected value. [Expected = "0755" ; Found = "0700"]

1092 files verified

Software check failed

Verification of software was unsuccessful on all the specified nodes.

Solution

As per the cluvfy check recommendations change the permissions of the binaries / library files.

$ chmod 755 /u01/app/11.2.0/grid/lib/libttsh11.so

Post the change, the database could be started using srvctl and the status also changed as desired.
[oracle@racnd01]/u01/app/oracle/product/11.2.0/dbhome_1/bin>srvctl status database -d racdb
Instance racdb1 is running on node racnd01
Instance racdb2 is running on node racnd02

crsctl stat res -t|more
crsctl stat res -t

--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
......
ora.racdb.db
1 ONLINE ONLINE racnd01 Open
2 ONLINE ONLINE racnd02 Open
......

asmcmd lsdg

asmcmd lsdsk -G AFDDGo

ASMCMD> ls -lL
State Type Rebal Name
MOUNTED EXTERN N DATA1/
MOUNTED EXTERN N FUBPRD_DATA1/
MOUNTED EXTERN N FUBPRD_FRA1/
MOUNTED EXTERN N FUOPRD_DATA1/
MOUNTED EXTERN N FUOPRD_FRA1/
MOUNTED EXTERN N FUSPROD_FRA1/
MOUNTED EXTERN N VOTING/
ASMCMD>

ASMCMD> cd FSPROD
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type Redund Striped Time Sys Name
N ARCHIVELOGS/
Y CONTROLFILE/
Y DATAFILE/
Y ONLINELOG/
Y PARAMETERFILE/
Y TEMPFILE/
N spfileFSPROD.ora => +DATA1/FSPROD/PARAMETERFILE/spfile.267.756430703

ASMCMD>

ASMCMD> cd DATAFILE
ASMCMD> ls -ltr
WARNING:option 'r' is deprecated for 'ls'
please use 'reverse'

Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_IAS_ORASDPM.273.756495935
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_MDS.270.756495909
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.1894.855409095
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.2527.806671527
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.269.756495901
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.4933.823546121
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.5084.855409271
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.5237.863731467
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.677.761069493
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_ODI_USER.776.767521547
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y PROD_SOAINFRA.4952.881865403
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y SYSAUX.257.756430473
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y SYSAUX.620.760708231
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y SYSTEM.256.756430471
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y SYSTEM.4827.767483327
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y SYSTEM.621.760708255
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y UNDOTBS1.258.756430473
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y UNDOTBS1.5011.822167755
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y UNDOTBS2.264.756430665
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y UNDOTBS2.5020.822167887
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y USERS.259.756430473
DATAFILE UNPROT COARSE MAY 19 01:00:00 Y USERS.4989.894710281
ASMCMD>

To check connected Node

grid@host01 olsnodes -a
ahost02 Hub
host05 Leaf
host04 Leaf
[grid@host01 ~]$

To check Archive usage in RAC Database

SELECT inst_id , VALUE / (1024 * 1024 * 1024) GB FROM gv$parameter WHERE name = 'db_recovery_file_dest_size'

column USED_GB format 999,990.999

column USED% format 990.99

column RECLAIM_GB format 999,990.999

column RECLAIMABLE% format 990.99

column LIMIT_GB format 999,990.999

select frau.file_type as type,

frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",

frau.percent_space_used "USED%",

frau.percent_space_reclaimable "RECLAIMABLE%",

frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",

frau.number_of_files "FILES#"

from v$flash_recovery_area_usage frau,

v$recovery_file_dest rfd

order by file_type;

TYPE USED_GB USED% RECLAIMABLE% RECLAIM_GB FILES#

----------------------- ------------ ------- ------------ ------------ ----------

ARCHIVED LOG 1,998.001 19.99 0.00 0.000 320

AUXILIARY DATAFILE COPY 0.000 0.00 0.00 0.000 0

BACKUP PIECE 0.000 0.00 0.00 0.000 0

CONTROL FILE 0.000 0.00 0.00 0.000 0

FLASHBACK LOG 0.000 0.00 0.00 0.000 0

FOREIGN ARCHIVED LOG 0.000 0.00 0.00 0.000 0

IMAGE COPY 0.000 0.00 0.00 0.000 0

REDO LOG 0.000 0.00 0.00 0.000 0

select name,

space_limit/1024/1024/1024 "LIMIT_GB",

space_used/1024/1024/1024 "USED_GB",

space_used/space_limit*100 "USED%",

space_reclaimable/1024/1024/1024 "RECLAIM_GB",

number_of_files "FILE#"

from v$recovery_file_dest

SQL>

column USED_GB format 999,990.999

column USED% format 990.99

column RECLAIM_GB format 999,990.999

column RECLAIMABLE% format 990.99

column LIMIT_GB format 999,990.999

select frau.file_type as type,

frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",

frau.percent_space_used "USED%",

frau.percent_space_reclaimable "RECLAIMABLE%",

frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",

frau.number_of_files "FILES#"

from v$flash_recovery_area_usage frau,

v$recovery_file_dest rfd

order by file_type;

SQL> SQL> SQL> 2 3 4 5 6 7 8 9

TYPE USED_GB USED% RECLAIMABLE% RECLAIM_GB FILES#

----------------------- ------------ ------- ------------ ------------ ----------

ARCHIVED LOG 1,953.023 19.54 0.00 0.000 312

AUXILIARY DATAFILE COPY 0.000 0.00 0.00 0.000 0

BACKUP PIECE 0.000 0.00 0.00 0.000 0

CONTROL FILE 0.000 0.00 0.00 0.000 0

FLASHBACK LOG 0.000 0.00 0.00 0.000 0

FOREIGN ARCHIVED LOG 0.000 0.00 0.00 0.000 0

IMAGE COPY 0.000 0.00 0.00 0.000 0

REDO LOG 0.000 0.00 0.00 0.000 0

8 rows selected.

SQL>

SQL> select name,

space_limit/1024/1024/1024 "LIMIT_GB",

space_used/1024/1024/1024 "USED_GB",

space_used/space_limit*100 "USED%",

space_reclaimable/1024/1024/1024 "RECLAIM_GB",

number_of_files "FILE#"

from v$recovery_file_dest 2 3 4 5 6 7

8 ;

NAME

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

LIMIT_GB USED_GB USED% RECLAIM_GB FILE#

------------ ------------ ------- ------------ ----------

+RECO

9,995.000 1,952.849 19.54 0.000 312

select trunc(l.first_time) arch_date, l.inst_id, l.status, n.destination, round(sum( blocks * block_size ) / ( 1024 * 1024 * 1024 ) ) total_file_size_gb

, count(*) file_count

from gv$archived_log l, gv$archive_dest n

where l.inst_id = n.inst_id

and l.dest_id = n.dest_id

and n.destination = 'USE_DB_RECOVERY_FILE_DEST'

group by trunc(l.first_time),l.inst_id,l.status,n.destination

order by trunc(l.first_time) ,l.inst_id,l.status,n.destination

The status can have several values:

A - Available

D - Deleted

U - Unavailable

X - Expired

So, based on this column we can calculate how much archivelog is created and how much is still available in the FRA.

select trunc(l.first_time) arch_date

, round(sum(decode(l.status,'D',blocks * block_size,0)/(1024 * 1024 * 1024))) deleted_gb

, round(sum(decode(l.status,'A',blocks * block_size,0)/(1024 * 1024 * 1024))) available_gb

, round(sum(decode(l.status,'U',blocks * block_size,0)/(1024 * 1024 * 1024))) unavailable_gb

, round(sum(decode(l.status,'X',blocks * block_size,0)/(1024 * 1024 * 1024))) expired_gb

, round(sum(blocks * block_size) /(1024 * 1024 * 1024)) total_size_gb

from gv$archived_log l,gv$archive_dest n

where l.inst_id = n.inst_id

and l.dest_id = n.dest_id

and n.destination = 'USE_DB_RECOVERY_FILE_DEST'

group by trunc(l.first_time)

order by trunc(l.first_time)

SELECT inst_id

, to_char(oldest_flashback_time,'dd-mm-yyyy-hh24:mi') oldest_flashback_time

, retention_target

, round((sysdate - oldest_flashback_time ) * (60 * 24)) actual_retention_possible

, round(flashback_size / (1024 * 1024 * 1024)) flashback_size_gb

, round(estimated_flashback_size / (1024 * 1024 * 1024)) estimated_flashback_size_gb

FROM gv$flashback_database_log

511646 MB Space Left in TableSpace uncpcdb_2_UNCPROD_REPDATA

AND 7 % Left in TableSpace uncpcdb_2_UNCPROD_REPDATA

511646 MB Space Left in TableSpace uncpcdb_2_UNCPROD_REPDATA

AND 7 % Left in TableSpace uncpcdb_2_UNCPROD_REPDATA

: mprl334.prod.mobily.lan

SQL>

SQL> select name,open_mode from V$database;

NAME OPEN_MODE

--------- --------------------

UNCPCDB READ WRITE

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 UNCPROD READ WRITE NO

SQL>

alter session set container=UNCPROD

select group_number,name,state,type,total_mb,free_mb,usable_file_mb from v$asm_diskgroup

To check Tablespace usage

col "TABLESPACE" for a50

col "Totalspace(MB)" for 9999999

col "Used Space(MB)" for 9999999

set linesize 200

select t.tablespace,

t.totalspace as " Totalspace(MB)",

round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",

nvl(fs.freespace,0) as "Freespace(MB)",

round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",

round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"

from

(select round(sum(d.bytes)/(1024*1024)) as totalspace,

d.tablespace_name tablespace

from dba_data_files d group by d.tablespace_name) t,

(select round(sum(f.bytes)/(1024*1024)) as freespace,

f.tablespace_name tablespace

from dba_free_space f group by f.tablespace_name) fs

where t.tablespace=fs.tablespace (+) and fs.tablespace='&TSPACE'

order by t.tablespace;

col "TABLESPACE" for a50

col "Totalspace(MB)" for 9999999

col "Used Space(MB)" for 9999999

set linesize 200

select t.tablespace,

t.totalspace as " Totalspace(MB)",

round((t.totalspace-nvl(fs.freespace,0)),2) as "Used Space(MB)",

nvl(fs.freespace,0) as "Freespace(MB)",

round(((t.totalspace-nvl(fs.freespace,0))/t.totalspace)*100,2) as "%Used",

round((nvl(fs.freespace,0)/t.totalspace)*100,2) as "% Free"

from

(select round(sum(d.bytes)/(1024*1024)) as totalspace,

d.tablespace_name tablespace

from dba_data_files d group by d.tablespace_name) t,

(select round(sum(f.bytes)/(1024*1024)) as freespace,

f.tablespace_name tablespace

from dba_free_space f group by f.tablespace_name) fs

where t.tablespace=fs.tablespace (+)

order by t.tablespace;

TABLESPACE Totalspace(MB) Used Space(MB) Freespace(MB) %Used % Free

-------------------------------------------------- --------------- -------------- ------------- ---------- ----------

AUDIT_TS 5888 5342 546 90.73 9.27

NBADATA32 9513701 9509931 3770 99.96 .04

REPDATA 8671759 8634742 37017 99.57 .43

SYSAUX 81528 31586 49942 38.74 61.26

SYSTEM 6754 6748 6 99.91 .09

TOOLS 2560 2212 348 86.41 13.59

UNDOTBS1 147015 4362 142653 2.97 97.03

UNDOTBS2 219900 41560 178340 18.9 81.1

USERDATA 2197463 2141805 55658 97.47 2.53

USERS 54475 52692 1783 96.73 3.27

To check asm diskgroup usage

set num 9

select name,free_mb,total_mb from v$asm_diskgroup;

To add space in Tablespace

set num 5

COL file_name FORMAT A90

select file_name,bytes/1024/1024 mb,AUTOEXTENSIBLE from dba_data_files where AUTOEXTENSIBLE='YES';

set num 5

COL file_name FORMAT A90

select file_name,bytes/1024/1024 mb,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&TSPAC' ;

select file_name,bytes/1024/1024 mb from dba_data_files where tablespace_name='&TSPAC' ;

select file_name from dba_data_files where tablespace_name='&TSPAC' ;

ALTER TABLESPACE CTL_INDEX01 add DATAFILE '/oradata/ctlsits/data01/CTL_INDEX01__03.dbf' size 8000m;

alter database datafile '+DATA1/UNCPCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1041.1088802799' resize 20480m

alter database datafile '+DATA1/UNCPCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1042.1088802857' resize 20480m

approx 50GB space added .current utilization is as below

TABLESPACE Totalspace(MB) Used Space(MB) Freespace(MB) %Used % Free

-------------------------------------------------- --------------- -------------- ------------- ---------- ----------

REPDATA 8706967 8634742 72225 99.17 .83

SQL

SQL>

SQL> alter database datafile '+DATA1/UNCPCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1041.1088802799' resize 20480m

2 ;

Database altered.

SQL> alter database datafile '+DATA1/UNCPCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1042.1088802857' resize 20480m

2 ;

Database altered.

alter database datafile '+DATA1/UNCPCDB/A99EA8DA94C3B3AFE053A4C1400A49DC/DATAFILE/repdata.1043.1088804069' resize 25480m

ALTER TABLESPACE OCSDATA1 ADD DATAFILE '+OCS_DATA2' size 5G autoextend on next 1024M;

ALTER TABLESPACE GP_CDF_DATA2 ADD DATAFILE '+IN_DATA2' size 12G autoextend on next 1024M;

SQL> alter database datafile '+OCS_DATA2/OCSPRODN/DATAFILE/ocsdata1.1425.1095871299' resize 18g;

Database altered.

SQL>

alter tablespace SYSAUX add datafile'+DATA' size 5G autoextend on next 1024M;

select GROUP_NUMBER,DISK_NUMBER,TOTAL_MB,FREE_MB,path from V$ASM_DISK;

SQL>

SQL> select name,free_mb,total_mb from v$asm_diskgroup;

NAME FREE_MB TOTAL_MB

------------------------------ ------- --------

CRS_DATA 11959 12264

MGMT 35870 61368

OCS_DATA1 ##### #####

OCS_DATA2 ##### #####

OCS_RECO ##### #####

OCS_REDO1 73085 #####

OCS_REDO2 73085 #####

IN_DATA1 ##### #####

IN_DATA2 ##### #####

IN_DATA3 ##### #####

IN_RECO ##### #####

NAME FREE_MB TOTAL_MB

------------------------------ ------- --------

IN_REDO1 9903 59808

IN_REDO2 9903 59808

13 rows selected.

SQL>

column free_mb format 9,999990.99

column TOTAL_MB format 999,990.99

select inst_id, name, total_mb, free_mb, round((free_mb/total_mb)*100,2) pct_free from gv$asm_diskgroup where total_mb != 0 order by inst_id, name;

If archive log location (FRA) is 100% full and not able to take any RMAN backup ,So you need to increase db_recovery_file_dest_size and later revert back to original

1) chek FRA size

SQL> show parameter db_recovery_file_dest;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +EM_FRA

db_recovery_file_dest_size big integer 102390M

2) Increase FRA Size as below

SQL> alter system set db_recovery_file_dest_size=130G scope=both;

System altered.

SQL>

alter system set db_recovery_file_dest_size=96G scope=both;

oem00nft@port1001ovl ~]$

[oem00nft@port1001ovl ~]$ rman

Recovery Manager: Release 12.1.0.2.0 - Production on Mon Sep 9 12:49:07 2019

RMAN> connect target

connected to target database: OEM00NFT (DBID=318876817)

RMAN>

L> show parameter db_recovery_file_dest;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string +EM_FRA

db_recovery_file_dest_size big integer 100G

SQL>

alter system set db_recovery_file_dest_size=100G scope=both;

alter system set db_recovery_file_dest_size=120G scope=both;

acrs 9231 1 0 Apr14 ? 00:15:47 asm_pmon_+ASM1

oem00nft 15053 1 0 Apr28 ? 00:11:23 ora_pmon_oem00nft1

oem00nft 17682 17470 0 11:44 pts/0 00:00:00 grep pmon

[oem00nft@port1001ovl ~]$ exit

logout

Connection to port1001ovl closed.

[oem00nft@port1002ovl ~]$

[oem00nft@port1001ovl ~]$ sqlplus a/a@svttest

radbs@mstl002:/oracle>

oradbs@mstl002:/oracle> ps -ef|grep pmon

oradbs 12178 1 0 Jan06 ? 00:04:23 ora_pmon_svmtest

oradbs 20230 1 0 Jan06 ? 00:03:09 ora_pmon_svttest

oradbs 1832121 1829494 0 13:24 pts/1 00:00:00 grep --color=auto pmon

oradbs@mstl002:/oracle>

Delete expired archivelogs using RMAN

Below commands will helpful for deleting expired archivelog files through RMAN :

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

Now check the output with below command it should not return any list

RMAN> list expired archivelog all;

using target database control file instead of recovery catalog

specification does not match any archived log in the repository

RMAN>

column USED_GB format 999,990.999

column USED% format 990.99

column RECLAIM_GB format 999,990.999

column RECLAIMABLE% format 990.99

column LIMIT_GB format 999,990.999

select frau.file_type as type,

frau.percent_space_used/100 * rfd.space_limit /1024/1024/1024 "USED_GB",

frau.percent_space_used "USED%",

frau.percent_space_reclaimable "RECLAIMABLE%",

frau.percent_space_reclaimable/100 * rfd.space_limit /1024/1024/1024 "RECLAIM_GB",

frau.number_of_files "FILES#"

from v$flash_recovery_area_usage frau,

v$recovery_file_dest rfd

order by file_type;

select name,

space_limit/1024/1024/1024 "LIMIT_GB",

space_used/1024/1024/1024 "USED_GB",

space_used/space_limit*100 "USED%",

space_reclaimable/1024/1024/1024 "RECLAIM_GB",

number_of_files "FILE#"

from v$recovery_file_dest

Segments with highest growth (Top n):

-------------------------------

Below is a query which can be used to query segments with highest growth. This will also report the present size of the segment which is very useful in identifying the growth percecntage.

col "OBJECT_NAME" for a50

column SUBOBJECT_NAME for a50

column Growth in MB format 990.99

SELECT o.OWNER , o.OBJECT_NAME , o.SUBOBJECT_NAME , o.OBJECT_TYPE ,

t.NAME "Tablespace Name", s.growth/(1024*1024) "Growth in MB",

(SELECT sum(bytes)/(1024*1024)

FROM dba_segments

WHERE segment_name=o.object_name) "Total Size(MB)"

FROM DBA_OBJECTS o,

( SELECT TS#,OBJ#,

SUM(SPACE_USED_DELTA) growth

FROM DBA_HIST_SEG_STAT

GROUP BY TS#,OBJ#

HAVING SUM(SPACE_USED_DELTA) > 0

ORDER BY 2 DESC ) s,

v$tablespace t

WHERE s.OBJ# = o.OBJECT_ID

AND s.TS#=t.TS#

AND rownum < 51

ORDER BY 6 DESC

select * from V$FLASH_RECOVERY_AREA_USAGE;

archived log file name=/oradata/svmtcdb/archive/fast_recovery_area/SVTTEST/archivelog/2021_12_03/o1_mf_1_2305_jtmszp8y_.arc RECID=2280 STAMP=1090327718

validation succeeded for archived log

Get archive generated per day

You can include where clause in the below script for dest_id based on your local archive configuration ( where completion_time>=sysdate-14) and dest_id=&value) and when prompted enter the dest_id value

SQL> select to_char(COMPLETION_TIME,'DD/MON/YYYY') Day,

sum(blocks*block_size)/1048576/1024 "Size(GB)",

count(sequence#) "Total Archives"

from (select distinct sequence#,

thread#,

COMPLETION_TIME,

blocks,

block_size

from v$archived_log

where completion_time>=sysdate-14)

group by to_char(COMPLETION_TIME,'DD/MON/YYYY')

order by 1;

==================archive log generation per day================

spool /tmp/Redo_generation_check.html

set markup html on

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sysdate from dual;

select max(bytes_redo) as max_redo_hour_bytes

from (select to_char(first_time,'YYYYMMDD HH24') as day_hour,sum(blocks*block_size) as bytes_redo

from v$archived_log

group by to_char(first_time,'YYYYMMDD HH24'));

col day for a8;

select

to_char(first_time,'YY-MM-DD') day,

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'999') "00",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'999') "01",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'999') "02",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'999') "03",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'999') "04",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'999') "05",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'999') "06",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'999') "07",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'999') "08",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'999') "09",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'999') "10",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'999') "11",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'999') "12",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'999') "13",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'999') "14",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'999') "15",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'999') "16",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'999') "17",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'999') "18",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'999') "19",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'999') "20",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'999') "21",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'999') "22",

to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'999') "23",

COUNT(*)

from v$log_history

group by to_char(first_time,'YY-MM-DD')

order by day ;

select * from v$log;

set markup html off

Spool off

select to_char(first_time,'dd/mm/yyyy') "FIRST_DATE", count(*), sum(round(blocks*block_size/1024/1024)) "SIZE IN MB"

from v$archived_log WHERE TRUNC(first_time) > sysdate-30

group by to_char(first_time,'dd/mm/yyyy')

order by 1 asc;

===============Space addition in RAC Envoriment============

Note - I would not allow anyone to touch and resize disks in use. I would request new disks/LUNs (same size as the existing ones used),

and then add these to the ASM diskgroup needing more space and do a rebalance.

If the diskgroup is normal or high redundancy, then a new LUN needs to be added to each failgroup.

(CRQ000002739274) Space Request for EASYPROD

Please provide 1800gb*8 shared luns for sprs029 and sprs030 CRQ000002739213 , we have updated the WWN’s details in the CRQ

1) send mail for approval

Could you please approve the Space Request for EASYPROD for next 5 months give below?

14400 GB

/dev/oracle/asmdsk_1800gb_116 to /dev/oracle/asmdsk_1800gb_123 - 1800 GB x 8 Disks

IN_DATA1 - 3 x 1800 GB

IN_DATA2 - 2 x 1800 GB

IN_DATA3 - 3 x 1800 GB

Please let us know how much space we will request for the below two DG.

IN_DATA2 : /dev/oracle/asmdsk_1800gb

IN_DATA3 : /dev/oracle/asmdsk_1800gb

Based on that we will raise the CRQ for Unix team.

1) ASM 11.2. RAC configuration on ASMLIB.

2) ASM (v$asm_view) is reporting both paths ("ORCL:*", "/dev/oracleasm/disks/*") to access the same disks:

SQL> select label, path from v$asm_disk where path like '%IN_DATA1%';

SQL> show parameter asm_diskstring

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

asm_diskstring string /dev/oracle/*

SQL>

ASM alert.log (on both ASM instances "+ASM1 & +ASM2") shows the correct discovery path and using the correct disks:

select a.name disk_group_name,

b.name disk_file_name,

b.path disk_file_path,

b.label disk_name

from v$asm_diskgroup a , v$asm_disk b

where a.GROUP_NUMBER = b.GROUP_NUMBER

order by 2;

SELECT NVL(a.name, '[CANDIDATEdisk_group_name

, b.path disk_file_path, b.name disk_file_name , b.failgroup disk_file_fail_group

FROM v$asm_diskgroup a RIGHT OUTER JOIN v$asm_disk b USING (group_number)

ORDER BY a.name;

1. How can I check whether the LUNs within the dik group have the same sizes?

2. I am thinking I need to do the following, please let me know if I am incorrect

A). +ASM> /etc/init.d/oracleasm listdisks to make sure the disk is available from all the nodes

B). As a rootscan for the new disk

/etc/init.d/oracleasm scandisks

C). alter diskgroup DATA01 add disk ‘NEW DISK';

D). alter diskgroup data01 rebalance power 1; -- ASM_POWER_LIMIT parameter value is is set to 1

You can also use asmcmd program from grid home, but you can just see disk sizes you have already added a diskgroup.

For this

1. set oracle_home to grid home

2. asmcmd

3. lsdsk -k

ALTER DISKGROUP disk_group_1 ADD DISK '/devices/disk3' REBALANCE POWER 5

=========

Follow the below steps to add a disk to ASM diskgroup in Oracle 19c.

1. Create ASM disk.

2. Check the ASM disks.

3. Add disk to ASM diskgroup.

4. Check the rebalance status.

5. Check the newly added disk in ASM Diskgroup

1. Create ASM disk.

Get the Lun name from storage team.

Lun Name – /dev/sda1

Create the new ASM Disk,

$sudo oracleasm createdisk ORA_DATA_01 /dev/sda1

2. Check the ASM Disks,

Check the newly added disk,

$sudo oracleasm listdisks

Now make the disk available to ASM.

[iyunv@dbvisit31 ~]# /etc/init.d/oracleasm listdisks

3. Create the ASM Diskgroup

$sqlplus / as sysasm

SQL> alter diskgroup ORA_DATA add disk ‘/dev/oracleasm/disks/ORA_DATA_011’ NAME ORA_DATA_01 rebalance power 100;

4. Check the rebalance status.

$sqlplus / as sysasm

SQL> select * from v$asm_operation.

If no rows returned, then the rebalance is compeleted.

5. Check the newly added disk in ASM Diskgroup

set lines 999;

col diskgroup for a15

col diskname for a15

col path for a35

select a.name DiskGroup,b.name DiskName, b.total_mb, (b.total_mb-b.free_mb) Used_MB, b.free_mb,b.path,b.header_status

from v$asm_disk b, v$asm_diskgroup a

where a.group_number (+) =b.group_number

order by b.group_number,b.name;

DISKGROUP DISKNAME TOTAL_MB USED_MB FREE_MB PATH HEADER_STATU

--------------- --------------- ---------- ---------- ---------- ----------------------------------- ------------

CRS_DATA CRS_DATA_0003 4088 122 3966 /dev/oracle/asmdsk_ibmfs_4gb_c02 MEMBER

CRS_DATA CRS_DATA_0004 4088 91 3997 /dev/oracle/asmdsk_ibmfs_4gb_c03 MEMBER

CRS_DATA CRS_DATA_0005 4088 92 3996 /dev/oracle/asmdsk_ibmfs_4gb_c01 MEMBER

IN_DATA1 IN_DATA1_0000 1843104 1826776 16328 /dev/oracle/asmdsk_1800gb_001 MEMBER

IN_DATA1 IN_DATA1_0001 1843104 1826752 16352 /dev/oracle/asmdsk_1800gb_002 MEMBER

IN_DATA1 IN_DATA1_0002 1843104 1826772 16332 /dev/oracle/asmdsk_1800gb_003 MEMBER

IN_DATA1 IN_DATA1_0003 1843104 1826820 16284 /dev/oracle/asmdsk_1800gb_004 MEMBER

IN_DATA1 IN_DATA1_0004 1843104 1826792 16312 /dev/oracle/asmdsk_1800gb_005 MEMBER

IN_DATA1 IN_DATA1_0005 1843104 1826796 16308 /dev/oracle/asmdsk_1800gb_006 MEMBER

IN_DATA1 IN_DATA1_0006 1843104 1826764 16340 /dev/oracle/asmdsk_1800gb_007 MEMBER

IN_DATA1 IN_DATA1_0007 1843104 1826788 16316 /dev/oracle/asmdsk_1800gb_008 MEMBER

select GROUP_NUMBER, NAME,TOTAL_MB, FREE_MB, USABLE_FILE_MB from V$ASM_DISKGROUP;

GROUP_NUMBER NAME TOTAL_MB FREE_MB USABLE_FILE_MB

------------ ------------------------------ ---------- ---------- --------------

1 CRS_DATA 12264 11959 11959

2 IN_DATA1 70037952 620212 620212

3 IN_DATA2 71881056 309612 309612

4 IN_DATA3 70037952 352396 352396

5 IN_RECO 818656 817896 817896

6 IN_REDO1 59808 9903 9903

7 IN_REDO2 59808 9903 9903

8 MGMT 61368 35636 35636

9 OCS_DATA1 45871840 3735024 3735024

10 OCS_DATA2 45871840 3797828 3797828

11 OCS_RECO 1638304 1518988 1518988

GROUP_NUMBER NAME TOTAL_MB FREE_MB USABLE_FILE_MB

------------ ------------------------------ ---------- ---------- --------------

12 OCS_REDO1 122592 73085 73085

13 OCS_REDO2 122592 73085 73085

select substr(name,1,10) name,substr(path,1,20) path, REDUNDANCY, TOTAL_MB, os_mb, free_mb from V$ASM_DISK;

NAME PATH REDUNDA TOTAL_MB OS_MB FREE_MB

---------- ----------------------------------- ------- ---------- ---------- ----------

OCS_REDO1_ /dev/oracle/asmdsk_i UNKNOWN 10216 10216 6090

IN_RECO_00 /dev/oracle/asmdsk_4 UNKNOWN 409328 409328 408952

OCS_REDO1_ /dev/oracle/asmdsk_i UNKNOWN 10216 10216 6091

IN_DATA3_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 9284

IN_RECO_00 /dev/oracle/asmdsk_4 UNKNOWN 409328 409328 408944

IN_DATA2_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 7960

IN_DATA2_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 7968

IN_DATA3_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 9284

IN_DATA1_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 16244

IN_DATA3_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 9252

IN_DATA3_0 /dev/oracle/asmdsk_1 UNKNOWN 1843104 1843104 9260

This assumes that you've already partitioned the presented disk(s) (and will be using /dev/sd[whatever]N),

and that you're using asmlib. There will be a kernel module loaded if you are:

[root@oel61 disks]# lsmod | grep oracle

oracleasm 53865 1

[root@oel61 disks]#

As root, scan for candidate disks:

[root@oel61 ~]# /etc/init.d/oracleasm scandisks

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@oel61 ~]#

Then, check to see if the disk has already been "discovered" by ASM:

oracle@oel61 ~]$ asmcmd -p

ASMCMD [+] > lsdsk

Path

/dev/oracleasm/disks/DISK1

/dev/oracleasm/disks/DISK2

/dev/oracleasm/disks/DISK3

ASMCMD [+] >

If not, we need to stamp the device:

[root@oel61 ~]# /etc/init.d/oracleasm createdisk NEWFRA /dev/sdc1

Marking disk "NEWFRA" as an ASM disk: [ OK ]

[root@oel61 ~]#

Scan for candidate disks again, then list - the new device should be there:

[root@oel61 ~]# /etc/init.d/oracleasm scandisks

Scanning the system for Oracle ASMLib disks: [ OK ]

[root@oel61 ~]#

# /etc/init.d/oracleasm listdisks

DISK1

DISK2

DISK3

NEWFRA

Or use asmcmd:

oracle@oel61 ~]$ asmcmd -p

ASMCMD [+] > lsdsk

Path

/dev/oracleasm/disks/DISK1

/dev/oracleasm/disks/DISK2

/dev/oracleasm/disks/DISK3

/dev/oracleasm/disks/NEWFRA

ASMCMD [+] >

Now do a scandisks on the other node and check that everything is as it is on the first node (should be fine if you're using the same /dev device names).

Now the disk is ready to be added to the group.

List the groups:

[oracle@oel61 ~]$ export ORACLE_SID="+ASM"

[oracle@oel61 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.2.0 Production on Thu Jan 31 15:35:27 2013

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

With the Automatic Storage Management option

SQL> select name

2 from V$ASM_DISKGROUP;

NAME

------------------------------

DATA

FRA

SQL>

Add disk to the group:

SQL> ALTER DISKGROUP FRA ADD DISK '/dev/oracleasm/disks/NEWFRA';

Hint: If you're adding more than one disk it's good practice to add an option:

REBALANCE POWER 0 WAIT

When you add a new disk Oracle will start new background rebalancing process. If you add another disk it can be confused.

So it is safer to add disks with rebalance power of 0. And then add the last one using default rebalance power.

The below ASM devices are created on BRMDB.

eecsaruh6hoa121

crwxrw-r-x 1 oradbs oinstall 19,195 Feb 16 17:05 asmdsk_ibmfs_1900gb_109

crwxrw-r-x 1 oradbs oinstall 19,196 Feb 16 17:05 asmdsk_ibmfs_1900gb_110

crwxrw-r-x 1 oradbs oinstall 19,197 Feb 16 17:05 asmdsk_ibmfs_1900gb_111

eecsaruh6hoa122

crwxrw-r-x 1 oradbs oinstall 19,183 Feb 16 17:05 asmdsk_ibmfs_1900gb_109

crwxrw-r-x 1 oradbs oinstall 19,184 Feb 16 17:05 asmdsk_ibmfs_1900gb_110

crwxrw-r-x 1 oradbs oinstall 19,185 Feb 16 17:05 asmdsk_ibmfs_1900gb_111

Check rebalance status

disk added in brmprod and new brmdr,,, rebalance in progress

Basic/Initial Performance investigation at RAC Database level

1) check total session instancewise

SQL> select count(1),inst_id from gv$session group by inst_id;

COUNT(1) INST_ID

---------- ----------

338 2

370 1

SQL> select count(1),inst_id ,status from gv$session group by inst_id,status;

2) To check database/instance startup time

select INSTANCE_NAME, to_char(STARTUP_TIME,'DD/MM/YYYY HH24:MI:SS') "STARTUP_TIME" from v$instance;

INSTANCE_NAME

----------------

STARTUP_TIME

----------------------------------------------------------------------------

raidpcdb2

22/02/2022 03:52:40

3) To check blocking session

SELECT DECODE(request,0,'Holder: ','Waiter: ') ||

sid sess, id1, id2, lmode, request, type

FROM V$LOCK

WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request > 0)

ORDER BY id1, request;

4) To check Long running query

select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete" from v$session_longops where sofar <> totalwork;

select inst_id,sql_hash_value,sql_id, sid,serial#,to_char(start_time,'dd-mon:hh24:mi') start_time,

opname,target,totalwork,sofar,(elapsed_Seconds/60) elamin,username,

time_remaining tre

from gv$session_longops

where totalwork <> SOFAR

order by 7,tre, start_time,sid

5) check alert log

oradbs@mprs078:…rdbms/raidpcdb/raidpcdb1/trace$ ls -ltr alert*

-rwxrwxr-x 1 oradbs oinstall 182323095 Feb 3 2020 alert_raidpcdb001.log

-rw-r--r-- 1 oradbs oinstall 11187731 Feb 3 2020 alert_raidpcdb1.log.tar.gz

-rw-r--r-- 1 oradbs oinstall 5175811 Feb 16 10:51 alert_raidpcdb1_trace.log

-rwxr-xr-x 1 oradbs asmadmin 879826892 Feb 22 15:06 alert_raidpcdb1.log

oradbs@mprs078:…rdbms/raidpcdb/raidpcdb1/trace$ pwd

/oracle/raidpcdb/diag/rdbms/raidpcdb/raidpcdb1/trace

oradbs@mprs078:…rdbms/raidpcdb/raidpcdb1/trace$

cdb1/trace$ tail -3000 alert_raidpcdb1.log|more

Real-Time Example for user creation and password reset

----------------

In this example, We have creating easyuser and providing select privilege

1) check if user exist

select username,account_status,profile from dba_users where username like 'easyuser';

2) easyuser don't exist , so we will take metadata for similiar type of user

set long 9999999

set pagesize 0

set linesize 120

select DBMS_METADATA.GET_DDL('USER','EASYUSER2') from dual;

create user easyuser identified by <PASSWORD> default tablespace users temporary tablespace temp;

ALTER USER BZ2805 quota unlimited on users;

GRANT CREATE SESSION to easyuser;

Grant succeeded.

SQL> GRANT CONNECT to easyuser;

select username,account_status,profile from dba_users where username like 'easyuser';

grant READ_ROLE to easyuser

Dynamic query to generate privilege

select 'GRANT SELECT ON '||OWNER||'.'||object_name ||' TO R_ODS_DAT;'

from dba_objects where owner in ('INTERNSCHEMA','EASYSCHEMA') and object_type='TABLE';

To reset password

select

username, last_login,

password from

dba_users where username in ('EASYUSER','EASYUSER2')

col name format a20

col password format a60

select name,password from sys.user$ where name in ('EASYUSER','EASYUSER2')

alter user EASYUSER identified by Infra123

select username,account_status,profile from dba_users where username like 'easyuser';

To check privilege

SQL> select * from dba_role_privs where grantee='easyuser';

no rows selected

SQL> select * from dba_sys_privs where grantee='easyuser';

no rows selected

SQL>

select * from dba_tab_privs where grantee='easyuser';

EasyReliableDBA

Wednesday, 30 August 2017

19c Database Commands

Basic/Initial Performance investigation at RAC Database level

1 comment:

Search This Blog