DBA-Philiac

Open Query Analyzer and connect to your server. Run this script to get the names of the files used for TempDB. First check where the current data and log file of tempdb is. USE TempDB GO EXEC sp_helpfile GO ----------------------------------------------------------- Then run the below scripts USE master GO ALTER DATABASE TempDB MODIFY FILE (NAME = tempdev, FILENAME = 'd:\tempdb.mdf') -- new location of tempdb data file GO ALTER DATABASE TempDB MODIFY FILE (NAME = templog, FILENAME = 'd:\templog.ldf') -- new location of tempdb log file GO ---------------------------------------------------------- Stop the database instance Start the Database and agent sequentially then run this script again check whether the file destination is okay or not. USE TempDB GO EXEC sp_helpfile GO Check whether changed or not.

We can check our DB instance's memory parameters info, their usage, free space and also increase the value if required. Below are some queries which are frequently used by me to take snap of my db instance's memory SGA: ---- sgainfo can be found by SQL> select name, bytes/1024/1024 MB from v$sgainfo; NAME MB -------------------------------- ---------- Fixed SGA Size 2.09999084 Redo Buffers 29.5078125 Buffer Cache Size 27872 Shared Pool Size 4096 Large Pool Size 608 Java Pool Size 160 Streams Pool Size 0 Granule Size 16 Maximum SGA Size 32768 Startup overhead in Shared Pool 240 Startup NUMA Shared Pool memory 320 Free SGA Memory Available 0 At present db instance is using how much sga and how much sga is free f...

Some time I drop tablespace including contents and datafiles but the corresponding OS space is not released. A way to find the cause and solution is like below /sbin/fuser -cu mount_point_where_space_should_release you will then find an output like below mount_point: 4636(oracle) 13531(oracle) 16393(oracle) these are the processid(spid). So you can easily find the process detail (sid, program, schemaname etc) and kill accordingly. select P.SPID ,S.SID ,S.SERIAL#, S.SCHEMANAME, S.PROGRAM from v$process p, v$session s where P.ADDR=S.PADDR and spid in (4636,13531,16393); alter system kill session 'sid,serial#'; Normally you can kill the processes other than background process if they are not necessary. In my experience I found that dbsnmp user locked that session so the space isn't released.

As sys USER: ---------------- exec DBMS_NETWORK_ACL_ADMIN.CREATE_ACL ( acl=> 'EBIS.xml', description => 'ACL for Test Purpose', principal=> 'TESTADM', is_grant=> true, privilege =>'connect' ); exec DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(acl => 'Test.xml', host => '192.168.9.4'); select * from dba_network_acls; select * from dba_network_acl_privileges; select * from user_network_acl_privileges; SELECT DISTINCT aclid, XD.DESCRIPTION FROM xds_acl xd; If you want to unassign an host from an ACL please execute below procedure begin DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL(acl => 'Test.xml', host => '192.168.9.5'); end; / As a TESTADM USER: ------------------- > select sys.utl_http.request('http://192.168.9.5') from dual; ORA-29273: HTTP request failed ORA-06512: at "SYS.UTL_HTTP", line 1722 ORA-24247: network access denied by access control list...

-rename password file -create new password file -go to sqlnet.ora and check out the entry NTS. -Now restarting all the oracle services (otherwise reboot the pc) -check OEM Rep database started or not. -conn to OEM database as OEM_REP user -now run the following script @E:\oracle\ora92\sysman\admin\vduResetSysman.sql -relog into OEM. put password oem_temp. now change with new password.

-- create a tablespace for data CREATE TABLESPACE TBS_PART_TEST DATAFILE 'TBS_PART_TEST01.DBF' SIZE 10M AUTOEXTEND OFF, 'TBS_PART_TEST02.DBF' SIZE 10M AUTOEXTEND OFF LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; -- create a tablespace for index CREATE TABLESPACE TBS_PART_TEST_IND DATAFILE 'TBS_PART_TEST_IND01.DBF' SIZE 10M AUTOEXTEND OFF LOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON; -- creat a user to perform the activities CREATE USER PART IDENTIFIED BY VALUES '9725DA1D2FBD22FC' DEFAULT TABLESPACE TBS_PART_TEST TEMPORARY TABLESPACE TEMP PROFILE DEFAULT ACCOUNT UNLOCK; -- 4 Roles for PART GRANT SELECT_CATALOG_ROLE TO PART; GRANT DBA TO PART; GRANT RESOURCE TO PART; GRANT CONNECT TO PART; ALTER USER PART DEFAULT ROLE ALL; -- 1 System Privilege for PART GRANT UNLIMITED TABLESPACE T...

/dev/shm is nothing but implementation of traditional shared memory concept. It is an efficient means of passing data between programs. One program will create a memory portion, which other processes (if permitted) can access. This will result into speeding up things on Linux. In oracle 11g if you choose automatic memory management then the size of /dev/shm will definitely to be an optimum value. how to increase: To increase or decrease /dev/shm filesystem size 1) Open /etc/fstab with vi or any text editor of your choice, 2) Locate the line of /dev/shm and use the tmpfs size option to specify your expected size, e.g. 512MB: tmpfs /dev/shm tmpfs defaults,size=512m 0 0 e.g. 2GB: tmpfs /dev/shm tmpfs defaults,size=2g 0 0 The /etc/fstab content format is documented in man fstab and the tmpfs filesystem options can be found in man mount 3) To make change effective immediately, run this mount command to remount the /dev/shm filesystem: mount -o remount /dev/sh...

step#1 ------- create a user to take this export and import backup in both source and destination database. after creating the user give them proper privileges source database name:DB & backup taking user: pump_user destination database name:ARCH & backup taking user: test_arch grant DATAPUMP_IMP_FULL_DATABASE to pump_user; grant DATAPUMP_EXP_FULL_DATABASE to pump_user; grant EXP_FULL_DATABASE to pump_user; grant IMP_FULL_DATABASE to pump_user; grant DATAPUMP_IMP_FULL_DATABASE to test_arch; grant DATAPUMP_EXP_FULL_DATABASE to test_arch; grant EXP_FULL_DATABASEto test_arch; grant IMP_FULL_DATABASEto test_arch; step#2 ------- export and import between two databases required to create directory in both source and destination database. SQL> CREATE DIRECTORY dmpdir AS '/opt/oracle'; Directory created. SQL> GRANT read, write ON DIRECTORY dmpdir TO pump_user; Grant succeeded. step#3 ------- for network import you need to create a network link (nothing but a database link...

Create user: oracle Create group: oinstall,dba Add oinstall,dba to user oracle groupadd oinstall groupadd dba useradd oracle -g oinstall -G dba passwd oracle unzip two disks in the same folder like below, o therwise some .ear file missing error may occur: ---------------------------------------- unzip Linux_11gR1_database_1013_1of2.zip unzip Linux_11gR1_database_1013_2of2.zip cd database ./runInstall ---------------------------- append below lines in file:/etc/sysctl.conf # Controls the maximum number of shared memory segments kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 fs.file-max = 6815744 fs.aio-max-nr=1048576 net.ipv4.ip_local_port_range=9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 --------------------------------- append below lines in file:/etc/security/limits.conf * soft nproc 2047 * hard nproc 16384 * soft nofile 1024 * hard nofile 65536 -------...