ADDING DISKS TO DISKGROUP

SQL> l
1* select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk
SQL> /

GROUP_NUMBER DISK_NUMBER MOUNT_S TOTAL_MB FREE_MB NAME FAILGROUP PATH
------------ ----------- ------- ---------- ---------- ------------------------------ ------------------------------ -------------------------
0 0 CLOSED 97152 0 /dev/rasmdata07
0 1 CLOSED 97152 0 /dev/rasmdata06
0 2 CLOSED 97152 0 /dev/rasmdata08
2 2 CACHED 10112 7553 FLASHDG_0002 FLASHDG_0002 /dev/rasmflash03
1 4 CACHED 97280 1197 DATADG_0004 DATADG_0004 /dev/rasmdata05
1 3 CACHED 97280 1155 DATADG_0003 DATADG_0003 /dev/rasmdata04
2 0 CACHED 30720 22961 FLASHDG_0000 FLASHDG_0000 /dev/rasmflash01
1 0 CACHED 97152 1095 DATADG_0000 DATADG_0000 /dev/rasmdata01
2 1 CACHED 10112 7553 FLASHDG_0001 FLASHDG_0001 /dev/rasmflash02
1 2 CACHED 97152 1118 DATADG_0002 DATADG_0002 /dev/rasmdata03
1 1 CACHED 97152 1170 DATADG_0001 DATADG_0001 /dev/rasmdata02

11 rows selected.


SQL> select NAME,STATE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS from V$ASM_DISKGROUP;

NAME STATE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------------------------ ----------- ---------- ---------- ----------------------- -------------- -------------
DATADG MOUNTED 486016 5735 0 5735 0
FLASHDG MOUNTED 50944 38067 0 38067 0


select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

GROUP_NUMBER DISK_NUMBER MOUNT_S TOTAL_MB FREE_MB NAME FAILGROUP PATH
------------ ----------- ------- ---------- ---------- ------------------------------ ------------------------------ -------------------------
1 0 CACHED 97152 1095 DATADG_0000 DATADG_0000 /dev/rasmdata01
1 1 CACHED 97152 1170 DATADG_0001 DATADG_0001 /dev/rasmdata02
1 2 CACHED 97152 1118 DATADG_0002 DATADG_0002 /dev/rasmdata03
1 3 CACHED 97280 1155 DATADG_0003 DATADG_0003 /dev/rasmdata04
1 4 CACHED 97280 1197 DATADG_0004 DATADG_0004 /dev/rasmdata05

2 0 CACHED 30720 22961 FLASHDG_0000 FLASHDG_0000 /dev/rasmflash01
2 1 CACHED 10112 7553 FLASHDG_0001 FLASHDG_0001 /dev/rasmflash02
2 2 CACHED 10112 7553 FLASHDG_0002 FLASHDG_0002 /dev/rasmflash03
0 2 CLOSED 97152 0 /dev/rasmdata08
0 1 CLOSED 97152 0 /dev/rasmdata06
0 0 CLOSED 97152 0 /dev/rasmdata07

11 rows selected.

SQL> !ls -ltr /dev/asmdata08
brw-rw---- 1 oracle dba 34, 11 Dec 28 16:40 /dev/asmdata08


alter diskgroup DATADG/ add disk 'asmdata08';

ALTER DISKGROUP DATADG ADD DISK '/dev/rasmdata08' NAME DATADG_0005;

Since no FAILGROUP clauses are included


SQL> ALTER DISKGROUP DATADG ADD DISK '/dev/rasmdata08' NAME DATADG_0005;

Diskgroup altered.

SQL> select NAME,STATE,TOTAL_MB,FREE_MB,REQUIRED_MIRROR_FREE_MB,USABLE_FILE_MB,OFFLINE_DISKS from V$ASM_DISKGROUP;

NAME STATE TOTAL_MB FREE_MB REQUIRED_MIRROR_FREE_MB USABLE_FILE_MB OFFLINE_DISKS
------------------------------ ----------- ---------- ---------- ----------------------- -------------- -------------
DATADG MOUNTED 583168 102885 0 102885 0
FLASHDG MOUNTED 50944 38067 0 38067 0

SQL> select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,TOTAL_MB,FREE_MB,NAME,FAILGROUP,PATH from v$asm_disk order by 6;

GROUP_NUMBER DISK_NUMBER MOUNT_S TOTAL_MB FREE_MB NAME FAILGROUP PATH
------------ ----------- ------- ---------- ---------- ------------------------------ ------------------------------ -------------------------
1 0 CACHED 97152 1167 DATADG_0000 DATADG_0000 /dev/rasmdata01
1 1 CACHED 97152 1243 DATADG_0001 DATADG_0001 /dev/rasmdata02
1 2 CACHED 97152 1190 DATADG_0002 DATADG_0002 /dev/rasmdata03
1 3 CACHED 97280 1230 DATADG_0003 DATADG_0003 /dev/rasmdata04
1 4 CACHED 97280 1272 DATADG_0004 DATADG_0004 /dev/rasmdata05
1 5 CACHED 97152 96783 DATADG_0005 DATADG_0005 /dev/rasmdata08
2 0 CACHED 30720 22961 FLASHDG_0000 FLASHDG_0000 /dev/rasmflash01
2 1 CACHED 10112 7553 FLASHDG_0001 FLASHDG_0001 /dev/rasmflash02
2 2 CACHED 10112 7553 FLASHDG_0002 FLASHDG_0002 /dev/rasmflash03
0 1 CLOSED 97152 0 /dev/rasmdata06
0 0 CLOSED 97152 0 /dev/rasmdata07

11 rows selected.

Step 1: How to take the export



expdp \"/ as sysdba\" DIRECTORY=dump_ORACLETEST_dir SCHEMAS=SCOTT DUMPFILE=CH00271509.ORACLETEST.SCOTT.%U.dmp FILESIZE=10G PARALLEL=4 JOB_NAME=EXPDP_SCOTT LOGFILE=CH00271509.ORACLETEST.SCOTT.log


%U: This will create the dump files based on the file size.
Parallel: This parameter is based on the number of CPU’S on the server. Ex: CPU’S on the server is 5, the parallel value is no of cpu’s -1 means , the value is 4.[prtconf is the command to find no of cpu’s]
DIRECTORY= We have to create before start of export or import. In the mentioned location files will be located.
SYNTAX TO CREATE DIRECTORY:


CREATE DIRECTORY dump_ORACLETEST_import_dir AS '/export_dump/CH00271509';

grant read,write on directory dump_ORACLETEST_import_dir to SCOTT;

4. SCHEMAS : Name of the schema to be exported.



Step 2: How to take import



impdp \"/ as sysdba\" DIRECTORY=dump_ORACLETEST_import_dir dumpfile=CH00271509.ORCLLIVE.SCOTT.01.dmp,CH00271509.ORCLLIVE.SCOTT.02.dmp,CH00271509.ORCLLIVE.SCOTT.03.dmp,CH00271509.ORCLLIVE.SCOTT.04.dmp LOGFILE=CH00271509.ORACLETEST.SCOTT_import.log PARALLEL=4 JOB_NAME=IMPDP_SCOTT SCHEMAS=SCOTT



Step3: How to monitor export or import



a0.



select b.username,a.sid,b.opname,b.target,round(b.SOFAR*100/b.TOTALWORK,0) || '%' as "%DONE",

b.TIME_REMAINING,to_char(b.start_time,'YYYY/MM/DD HH24:MI:SS') START_TIME

from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid order by 6;



a.

select sid, serial#, sofar, totalwork,

dp.owner_name, dp.state, dp.job_mode

from gv$session_longops sl, gv$datapump_job dp

where sl.opname = dp.job_name and sofar != totalwork;



b.



SQL> select * from dba_datapump_jobs;





OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS

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

SYS IMPDP_SCOTT IMPORT SCHEMA EXECUTING 1 1 3



c.



select

substr(sql_text,instr(sql_text,'into "'),30) table_name,

rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,

trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute

from

sys.v_$sqlarea

where

sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0;



Following commands will be use full during export and import.



Generate the user creation syntax


SELECT dbms_metadata.get_ddl('USER','SCOTT') FROM dual;



Ex : -



CREATE USER "SCOTT" IDENTIFIED BY VALUES '6A063DED69BB5204'

DEFAULT TABLESPACE "SMDATA"

TEMPORARY TABLESPACE "TEMP"

QUOTA UNLIMITED ON SMDATA

QUOTA UNLIMITED ON SMINDX;



GRANT DBA TO SCOTT;

grant connect,resource to SCOTT;



2. How to find Roles and Privilages



SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='SCOTT';

SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE='SCOTT';



3. How to find object status and count?



SELECT OWNER,OBJECT_TYPE,STATUS,COUNT(*) FROM DBA_OBJECTS

WHERE OWNER='SCOTT'

GROUP BY OWNER,OBJECT_TYPE,STATUS;



How to check the flash recovery area usage?


SQL> show parameter db_recovery_file_dest_size



NAME TYPE VALUE

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

db_recovery_file_dest_size big integer 5G



SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;



How to increase the db_recovery_file_dest_size ?


alter system set db_recovery_file_dest_size=10240M scope=both;



How to take the backup if the


rman> connect target /



rman > connect catalog rman/rman@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=*********)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=dbname)))



rman > BACKUP ARCHIVELOG ALL DELETE INPUT;