How to change DBNAME in Oracle 11g ?

Oracle version :-  11.2.0.1.0
Database Sid : prim

Step 1.
Find out current dbname:-
[oracle@server1 ~]$ sqlplus  / as sysdba

SQL> show parameter db_name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      prim



Step 2.
Shutdown & start database in mount stage.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.

Step 3.Changing dbname .

We are changing the dbname from prim to stand using NID utility.

[oracle@server1 ~]$ nid target=sys/password dbname=stand

DBNEWID: Release 11.2.0.1.0 - Production on Sun Dec 28 00:40:20 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/prim/control01.ctl
    /u01/app/oracle/flash_recovery_area/prim/control02.ctl

Change database ID and database name PRIM to STAND? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4130009889 to 1595074421
Changing database name from PRIM to STAND
    Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed, wrote new name
    Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed, wrote new name
    Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed, wrote new name
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed, wrote new name
    Instance shut down

Database name changed to STAND.
Modify parameter file and generate a new password file before restarting.
Database ID for database STAND changed to 1595074421.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.

Step 4. Modify parameter file and generate a new password file and restart the dbase.
[oracle@server1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwstand password=redhat force=y

Now create a new pfile from the existing spfile.
SQL> create pfile from spfile;

File created.


[oracle@server1 ]$cd $ORACLE_HOME/dbs
[oracle@server1 dbs]$ cp initprim.ora initstand.ora
change *.db_name='prim' to *.db_name='stand' in init file.

Step 5. Open the database with Resetlogs option.

[oracle@server1 dbs]$ export ORACLE_SID=stand
[oracle@server1 dbs]$ sqlplus  / as sysdba
SQL> startup nomount from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             339740664 bytes
Database Buffers           67108864 bytes
Redo Buffers                4308992 bytes


SQL> ALTER SYSTEM SET DB_NAME=STAND SCOPE=SPFILE;

System altered.


SQL> SHUT IMMEDIATE;
ORA-01507: database not mounted


ORACLE instance shut down.

SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstand.ora' ;
File created.

SQL> STARTUP mount;

ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             339740664 bytes
Database Buffers           67108864 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
stand

SQL> show parameter db_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_name                              string      STAND

How to change DBID in Oracle 11g ?

Oracle version :-  11.2.0.1.0
Database Sid : prim


While we clone the database, the DB ID remains same as like the source database, so if we need to change it to the different DB ID, then use the follwing
method.Also when we change DBID of the database all previous backups will become unusable and we must open the database with RESETLOGS option.
Once you change the DBID make sure you take database backup immediately.

Step 1.
Find out current dbid :-
SQL> select dbid from v$database;

      DBID
----------
4130009889

Step 2.
Shutdown & start database in mount stage.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL>

Step 3.Now change the dbid using NID Utility.
[oracle@server1 ~]$ nid target=/

DBNEWID: Release 11.2.0.1.0 - Production on Thu Nov 19 09:11:21 2015

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to database PRIM (DBID=4130009889)

Connected to server version 11.2.0

Control Files in database:
    /u01/app/oracle/oradata/prim/control01.ctl
    /u01/app/oracle/flash_recovery_area/prim/control02.ctl

Change database ID of database PRIM? (Y/[N]) => y

Proceeding with operation
Changing database ID from 4130009889 to 4188450681
    Control File /u01/app/oracle/oradata/prim/control01.ctl - modified
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - modified
    Datafile /u01/app/oracle/oradata/prim/system01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/sysaux01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/undotbs01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/users01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/example01.db - dbid changed
    Datafile /u01/app/oracle/oradata/prim/temp01.db - dbid changed
    Control File /u01/app/oracle/oradata/prim/control01.ctl - dbid changed
    Control File /u01/app/oracle/flash_recovery_area/prim/control02.ctl - dbid changed
    Instance shut down

Database ID for database PRIM changed to 4188450681.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database ID.
DBNEWID - Completed succesfully.

Step 4.Start the database in mount stage and open it using RESETLOGS option.
[oracle@server1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 19 09:13:39 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             318769144 bytes
Database Buffers           88080384 bytes
Redo Buffers                4308992 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select dbid from v$database;

      DBID
----------
4188450681

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
prim

So we see the dbname has been changed into new one. Done..

P.S.After changing the DBID make sure you take database backup immediately.Since all the previous backups are nolonger can be used.

How to find out assigned roles and privileges given to an user ?

SQL> COL "USER,HIS ROLES AND PRIVILEGES" FORMAT a100
set linesize 300 pages 1000
SELECT
LPAD(' ', 5*level) || granted_role "USER,HIS ROLES AND PRIVILEGES"
FROM
(
  SELECT NULL grantee, username granted_role
  FROM dba_users
  WHERE username LIKE UPPER('SOUMYA')
  UNION
  SELECT grantee,granted_role
  FROM dba_role_privs
  UNION
  SELECT grantee,privilege
  FROM dba_sys_privs
)
START WITH grantee IS NULL
CONNECT BY grantee = PRIOR granted_role;


USER,HIS ROLES AND PRIVILEGES
----------------------------------------------------------------------------------------------------
     SOUMYA
          CONNECT
               CREATE SESSION
          RESOURCE
               CREATE CLUSTER
               CREATE INDEXTYPE
               CREATE OPERATOR
               CREATE PROCEDURE
               CREATE SEQUENCE
               CREATE TABLE
               CREATE TRIGGER
               CREATE TYPE

12 rows selected.

How to find out character set in Oracle 11g ?

How to findout characterset in Oracle 11g ?

SQL> SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

VALUE$
--------------------------------------------------------------------------------
WE8MSWIN1252

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected.

Sample tnsnames.ora file for Linux

Oracle Version: oracle 11g
Oracle SID: orcl
OS Version:- RHEL 6
Hostname:- server1.example.com
Server IP: 192.168.72.120


$ vi $ORACLE_HOME/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /i01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
  (DESCRIPTION =
         (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
         (CONNECT_DATA =
           (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
  )

Sample listener.ora file for Linux

Oracle Version: oracle 11g
Oracle SID: orcl
OS Version:- RHEL 6
Hostname:- server1.example.com
Server IP: 192.168.72.120


$ vi /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora

# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = orcl)
    )
  )

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server1.example.com)(PORT = 1521))
  )

ADR_BASE_LISTENER = /u01/app/oracle

How To Open The Standby Database When The Primary Is Lost in oracle11g ?

Scenario:- So this scenario shows how to open your standby database in read/write mode when you dont have any access(Lost) on primary database.
Here i'm trying to make a test case to demonstrate the following scenario.Currenly both primary and standby database are in sync.

Oracle version :-  11.2.0.1.0
Primary Database : prim
Standby Database : stand

At primary database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26


At standby database:-
SQL> select thread#,max(sequence#) from v$archived_log group by thread#;

   THREAD# MAX(SEQUENCE#)
---------- --------------
         1             26

So at this situation we totally power off the primary database server to test the scenario.

At primary:-
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit

[root@server1 ~]# poweroff

Now at standby database:-
Open the database in mount stage.
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL:> startup mount
ORACLE instance started.

Total System Global Area  413372416 bytes
Fixed Size                  2213896 bytes
Variable Size             310380536 bytes
Database Buffers           96468992 bytes
Redo Buffers                4308992 bytes
Database mounted.

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PHYSICAL STANDBY


Finish the Recovery process in standby database:-
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

Once done, now activate the standby database:-
SQL> ALTER DATABASE ACTIVATE PHYSICAL STANDBY DATABASE;

Database altered.

Check the status:-
SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
MOUNTED              MAXIMUM PERFORMANCE  PRIMARY


Now open the database in read/write mode.
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> select open_mode from v$database;

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

SQL> SELECT OPEN_MODE,PROTECTION_MODE,DATABASE_ROLE FROM V$DATABASE;

OPEN_MODE            PROTECTION_MODE      DATABASE_ROLE
-------------------- -------------------- ----------------
READ WRITE           MAXIMUM PERFORMANCE  PRIMARY


So, finally the previous standby database is now a new primary database with read/write mode in open stage.

How to reset Mysql Root password

How to reset Mysql Root password:-

[root@server1 ~]# mysql -u root -predhat

To change root password.
mysql> update user set Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

If root password is not available or its lost but we need to reset it, follow this.

[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@server1 ~]# mysqld_safe --skip-grant-tables &
[1] 7670
[root@server1 ~]# 140222 06:06:03 mysqld_safe Logging to '/var/lib/mysql/server1.soumya.com.err'.
140222 06:06:03 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql


Now the server is running with the –skip-grant-tables flag you can connect to it without a password and reset root password:

[root@server1 ~]# mysql --user=root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> update user set Password=PASSWORD('newpassword') WHERE User='root';
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

Now restart the mysql service and we are good to go..
[root@server1 ~]# service mysql stop
Shutting down MySQL..[  OK  ]
[root@server1 ~]# service mysql start

[root@server1 ~]# mysql -u root -pnewpassword
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.16 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> 

How to find out locked & expired users in oracle 11g

SQL> select username, account_status from dba_users where lock_date is not null;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
FLOWS_FILES                    EXPIRED & LOCKED
MDSYS                          EXPIRED & LOCKED
ORDSYS                         EXPIRED & LOCKED
EXFSYS                         EXPIRED & LOCKED
SCOTT                          EXPIRED & LOCKED
WMSYS                          EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
APPQOSSYS                      EXPIRED & LOCKED
XS$NULL                        EXPIRED & LOCKED
APEX_030200                    EXPIRED & LOCKED
OWBSYS_AUDIT                   EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
BI                             EXPIRED & LOCKED
PM                             EXPIRED & LOCKED
MDDATA                         EXPIRED & LOCKED
IX                             EXPIRED & LOCKED
ORDDATA                        EXPIRED & LOCKED
CTXSYS                         EXPIRED & LOCKED
ANONYMOUS                      EXPIRED & LOCKED
SH                             EXPIRED & LOCKED
OUTLN                          EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
OE                             EXPIRED & LOCKED

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
APEX_PUBLIC_USER               EXPIRED & LOCKED
HR                             EXPIRED & LOCKED
XDB                            EXPIRED & LOCKED
SPATIAL_CSW_ADMIN_USR          EXPIRED & LOCKED
SPATIAL_WFS_ADMIN_USR          EXPIRED & LOCKED
ORDPLUGINS                     EXPIRED & LOCKED
OWBSYS                         EXPIRED & LOCKED
SI_INFORMTN_SCHEMA             EXPIRED & LOCKED
OLAPSYS                        EXPIRED & LOCKED