TO CHECK ALERT LOG FILES IN ORACLE

TO CHECK ALERT LOG FILES IN ORACLE


$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Thu Dec 1 15:38:15 2011
ADR base = "/u01/app"

adrci> show homes
diag/tnslsnr/ip-10-100-255-165/listener
diag/rdbms/uw01/uw01

adrci> set home diag/rdbms/uw01/uw01
adrci> show alert -tail 100
Alternately, do it the old-fashioned way (on Linux or Unix):

$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.2.0 Production on Thu Dec 1 15:43:43 2011

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter background_dump

NAME                     TYPE    VALUE
------------------------------------ ----------- -----------------------------------
background_dump_dest             string  /u01/app/diag/rdbms/uw01/uw01/trace

SQL> exit
Disconnected from Oracle Database 11g...

$ tail -100 /u01/app/diag/rdbms/uw01/uw01/trace/alert_uw01.log

TEAMVIEWER INSTALLATION ON LINUX:

TEAMVIEWER INSTALLATION ON LINUX:

How do I Install Teamviewer 8 on RedHat, CentOS, Fedora
You may download teamviewer package for rpm based Linux distributions at teamviewer_linux.rpm.
Let us start installation. Go to the directory from where you have downloaded Teamviewer package and execute following yum command to install it. It will install missing dependencies.

# wget http://www.teamviewer.com/download/teamviewer_linux.rpm
# yum install teamviewer_linux.rpm

If you get missing public key error, you can download public key and import it using the following command.

# wget http://www.teamviewer.com/link/?url=354858
# rpm --import TeamViewer_Linux_PubKey.asc

After importing the public key, please run the “yum install” command again to install the Teamviewer rpm.

To start Teamviewer application, run the following command from the terminal.

# teamviewer

Steps to migrate the characterset of database

Steps to migrate the characterset of database



You can follow below steps to migrate database character set

Changing character to AL32UTF8 :

Take Full export of database before starting this activity.

1. SHUTDOWN IMMEDIATE;

2. Do a full backup

3. STARTUP MOUNT;

4. ALTER SYSTEM ENABLE RESTRICTED SESSION;

5. ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

6. ALTER SYSTEM SET IAQ_TM_PROCESSES=0;

7. ALTER DATABASE OPEN;

8. ALTER DATABASE CHARACTER SET <new_character_set_name>;
When i was doing it it errored out like below
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;
ALTER DATABASE CHARACTER SET AL32UTF8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

We need to make sure New character set would be superset of old characterset , check below link for checking super set

http://download.oracle.com/docs/cd/B19306_01/server.102/b14225/applocaledata.htm#sthref1958

I found work around after investigating for long time ,

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

The above command will skip the check of character set subset or superset.
9. SHUTDOWN IMMEDIATE;

10. STARTUP;
11.SQL> select value from NLS_DATABASE_PARAMETERS where parameter=’NLS_CHARACTERSET’;

VALUE
——————————————————————————–
AL32UTF8

Now character is successfully migrated.

SQL Developer Not Launching after 10.8 upgrade in MACOSX

SQL Developer Not Launching after 10.8 upgrade in MACOSX


I run SQL Developer to interface with Oracle and after upgrading to OS X 10.8 “Mountain Lion” (ML) SQL Developer would not launch. A little googling and of course I found the issue.
Apparently java is changed, either removed or unlinked when you upgrade. All I had to do to fix this was run this from the terminal.

sudo ln -s /usr/bin/java /bin/java

TO DROP THE USERS/ To check the user design


TO DROP THE USERS;



drop user LA cascade;
 


 To check the user design

SQL> set lines 160
set pages 10000
set long 20000
SELECT dbms_metadata.get_ddl('USER', 'LA') FROM dual;

TO CREATE TABLE
create table test_1 (name varchar2(10));

TO CHECK THE SCHEMAS IMPORTED

TO CHECK THE AVAILABILITY OF SCHEMAS IMPORTED


SQL>conn cp_own/cp;
desc OPLEER



TO CHECK HOW MANY ROWS IN A TABLE


select count(*) from OPLEER ;




SQL> select open_mode from v$database;



OPEN_MODE
----------
READ WRITE

To change oracle/sys user password

To change oracle/sys user password

 Type in root user passwd oracle       it will change oracle passwd.

changing oracle password
$su root
passwd oracle
and type new oraclepassword.

To change SYS passwd

IN SQL YOU CAN USE.
SQL-PASSW
YOU CAN CHANGE SYS PASSWD

.ORAENV

TO CHECK THE USERS ENCRYPTED PASSWORD IN ORACLE

TO CHECK THE USERS ENCRYPTED PASSWORD IN ORACLE




SELECT name,password FROM SYS.USER$ WHERE password is not null;

TO UNLOCK THE PASSWD FOR USER AFETR FAILING TO 3 TIMES

TO UNLOCK THE PASSWD FOR USER AFETR FAILING TO 3 TIMES
SQL> ALTER USER  TESTING identified by test account unlock;

User altered.

Fix-ORA-28000

 Fix-ORA-28000

SQL> alter user scott account unlock;
SQL> grant connect, resource to manjit;

FIX- ORA-12519

FIX- ORA-12519

 1.    Run SQL*Plus and login as SYSTEM. You should know what password you’ve used during the installation of Oracle DB XE.
 2.    Run the command “alter system set processes=150 scope=spfile;” in the SQL*Plus
 3.    VERY IMPORTANT: Restart the database

Create user in oracle and to give the privileges

 Create user in oracle and to give the privileges;




CREATE USER "LA" IDENTIFIED BY LA DEFAULT TABLESPACE "LA"
 TEMPORARY TABLESPACE "TEMP";


User created.


SQL> grant dba to LA;


Grant succeeded.



SQL> alter database default tablespace users;


Database altered.

SQL> create user lor_dev identified by lo_dev;


User created.

SQL> grant connect,resource to lor_dev;


Grant succeeded.
SQL> conn lor_dev/lo_dev
Connected.

SQL> exit



SQL> select username from dba_users;

,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,to know the dba users

To check the total size of the database.

 To check the total size of the database.


sql>select sum(bytes/1024/1024/1024) geg from dba_segments;



       GEG
----------
8.84643555……to check the total size of the data..

OR



select sum(bytes / (1024*1024)) "DB Size in GB" from dba_data_files;

SQL commands to check the tablespace,controlfiles,redologfiles,datafiles.

 SQL commands to check the tablespace,controlfiles,redologfiles,datafiles.



SQL> select *from v$tablespace;--to check the tablespace



SQL>select name from v$controlfile;-to check the controlfile

SQL>select member from v$logfile;-to check the redologfile

SQL>select name from v$datafile;-to check the datafile

To check the tablespace size in ORACLE

To check the tablespace size

:

SQL> SELECT /* + RULE */  df.tablespace_name "Tablespace",
        df.bytes / (1024 * 1024) "Size (MB)",
    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
     Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
     Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
     FROM dba_free_space fs,
     (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
       WHERE fs.tablespace_name (+)  = df.tablespace_name
       GROUP BY df.tablespace_name,df.bytes
    UNION ALL
      SELECT /* + RULE */ df.tablespace_name tspace,
            fs.bytes / (1024 * 1024),
            SUM(df.bytes_free) / (1024 * 1024),
             Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
   Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
        FROM dba_temp_files fs,
    (SELECT tablespace_name,bytes_free,bytes_used
   FROM v$temp_space_header
   GROUP BY tablespace_name,bytes_free,bytes_used) df
       WHERE fs.tablespace_name (+)  = df.tablespace_name
       GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
       ORDER BY 4 DESC;

To drop the tablespace :

To drop the tablespace

:

SQL> 
   alter tablespace "test" offline
;
SQL> /

Tablespace altered.


SQL> drop tablespace "test" including contents and datafiles;



Tablespace dropped

Export & Import Schema command in oracle

 Export & Import Schema command in oracle


exp sys/oracle buffer=200000 file=/home/oracle/LA.dmp compress=n owner=LA log=/home/oracle/LA.log statistics=none

imp sys/oracle123   file=/home/oracle/LA.dmp log=/home/oracle/LA.log fromuser=LA touser=LX ignore=y

TO give the dbms_lock privileges:

TO give the dbms_lock privileges:


GRANT execute ON dbms_lock TO <schema_name>;

GRANT execute ON dbms_lock TO schema_name;

To add temp /data file in oracle

To add  temp/ data file
 in oracle

ALTER TABLESPACE TEMP ADD TEMPFILE '/home/oracle/oracle/product/10.2.0/db_1/oradata/test/temp02.dbf' size 1g;

in linux platform

ALTER TABLESPACE TEMP ADD TEMPFILE 
'C:\Oracle\oradata\TEMP3\Temp02.dbf' size 50M reuse autoextend on next 1M maxsize 500M; in windows platform


In 11g we can shrink the temp tablespace.


To add the datafile:

alter tablespace la add datafile '
/home/oracle/oracle/product/10.2.0/db_1/oradata/test/la4.dbf' size
512m;

TO DECREASE DATAFILES

TO DECREASE DATAFILES:



SQL- desc dba_data_files;



SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='ENCLOSURE';



sql-alter database datafile '/u01/iphone/enclosure01.dbf' resize 512m;