Oracle Data Pump is faster and more flexible alternative to the “exp” and “imp” backup system used in previous Oracle versions. impdp and expdp commands use Oracle Directory for processing of backups. Create Directory CONN SYS AS SYSDBA CREATE OR REPLACE DIRECTORY dirname AS ‘/u01/backup/’; GRANT READ, WRITE ON DIRECTORY dirname TO backupuser; Tables Export…
Category: Databases
Random Character Generator in Oracle
Oracle dbms_random package can generate random characters in character, numeric or alphanumeric formats. dbms_random.value(Parameter, Value) U – Upper case L – Lower case A – Alphanumeric X – Alphanumeric with upper case alphabets. P – Printable characters only. Create Random Characters SQL> select dbms_random.random value from dual; Value : 531879542 SQL> select dbms_random.value from dual;…
Oracle Data File Occupancy in Oracle
Size of Oracle Data Files can be max 32 GB. If you are not chech data files sizes, The system can give an error when size of data files arrive max extent. You can solve this problem with adding a new data file in tablespace. But this state is not a good. You have to…
Oracle ASM Disks Occupancy in Oracle
If you want to show Oracle ASM Disks occupancy, you can use dictionary views. You can use below command for result. ASM Disk Occupancy Query SQL> select name, 100-trunc(100*free_mb/total_mb) occupancy, trunc((total_mb-free_mb)/1024) used, total_mb/1024 total from v$asm_diskgroup;
Oracle Data Guard Timeliness Status in Oracle
If you use Oracle Data Guard, you have to check timeliness status every time. The below query will give to timeliness status. The differences between results have to be 1-2 when you use real time apply Data Guard. Otherwise, your Data Guard is not real time and has a problem. Timeliness Status Query SQL> SELECT…
ACL Error Solution in Oracle 11g
Oracle 11g have been used ACL procedure to send smtp e-mail. Default configuration can not send e-mail. You have to create below procedures for complete smtp configuration. I used CONNUSER as ACL connection user and 100.10.10.11 IP address as mail server IP and NOTIFYUSER as user for notification for configuration. You can change this users…
Show Data Files Size in Oracle
If you want to show tablespace and datafile size, you can use below query. Also, you can add to procedure with schedule job for notification as mail. SELECT a.tablespace_name, a.file_name, a.bytes allocated_bytes, b.free_bytes FROM dba_data_files a, (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b WHERE a.file_id=b.file_id ORDER BY a.tablespace_name;
Create Schedule Job For Execute Procedure in SQL Server 2008
If you want to run procedure or any query as scheduling, you can use job in SQL Server Database. For this process, you can use below operations. Right Click On SQL Server Agent > New > Job
Copy Backup File from ASM to Local Disk
If you want to migrate backup or archivelog files from asm disks to local disk, you can use below command. But you have to create a directory to migrate backup and this directory owner have to be grid user. Copy File Command [grid ~]# asmcmd ASMCMD> cd FRA/backups ASMCMD> cp mynewbackupname.bck /localdiskpath/mybackup.bck
Oracle 11g Session Timeout Problem
Oracle 11g has a problem that is session timeout problem. You can add a row to sqlnet.ora file and listener.ora file to resolve session timeout problem. sqlnet.ora Note : Add this line to below of sqlnet.ora file. INBOUND_CONNECT_TIMEOUT=3600 listener.ora Note : Add this line to below of listener.ora file. INBOUND_CONNECT_TIMEOUT_LISTENER=3600