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};
OR
$ $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
------------------------------
1
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
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
-------- ----------------------------- ----------- ----------------------------
*
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>
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;
or
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
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
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;
or
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
or
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
Copyright (c) 1982, 2010, Oracle. All rights reserved.
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';
