MANUAL RENAMING THE DATABASE

MANUAL RENAMING THE DATABASE

[oracle@demo ~]$ export ORACLE_SID=testdb

[oracle@demo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 19 08:57:42 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select name from v$database;

NAME

---------

TESTDB

SQL> show parameter dump

NAME                                 TYPE        VALUE

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

background_core_dump                 string      partial

background_dump_dest                 string      /u01/app/oracle/diag/rdbms/tes

                                                 tdb/testdb/trace

core_dump_dest                       string      /u01/app/oracle/diag/rdbms/tes

                                                 tdb/testdb/cdump

max_dump_file_size                   string      unlimited

shadow_core_dump                     string      partial

user_dump_dest                       string      /u01/app/oracle/diag/rdbms/tes

                                                 tdb/testdb/trace

SQL>

SQL> alter database backup controlfile to trace;

Database altered.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@demo ~]$

[oracle@demo ~]$ cd /u01/app/oracle/diag/rdbms/testdb/testdb/trace

[oracle@demo trace]$ls -ltr

(pick up the last trace file)

[oracle@demo trace]$ cp testdb_ora_5776.trc ~/control.sql

[oracle@demo trace]$vi control.sql

-->d+g+g

startup nomount

create controlfile set database "TEST"............................

characterset US7SCII

;

->d+G

:wq

[oracle@demo ~]$ cat control.sql

STARTUP NOMOUNT

CREATE CONTROLFILE SET DATABASE "TEST" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 5

    MAXLOGMEMBERS 5

    MAXDATAFILES 100

    MAXINSTANCES 1

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/testdb/redo1.log'  SIZE 10M BLOCKSIZE 512,

  GROUP 2 '/u01/testdb/redo2.log'  SIZE 10M BLOCKSIZE 512,

  GROUP 3 '/u01/testdb/redo3.log'  SIZE 10M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/testdb/system.dbf',

  '/u01/testdb/sysaux.dbf',

  '/u01/testdb/undo1.dbf'

CHARACTER SET AL32UTF8

;

[oracle@demo ~]$

SQL> SELECT NAME,STATUS FROM V_$CONTROLFILE;

NAME

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

STATUS

-------

/u01/app/oracle/testdb/control01.ctl

/u01/app/oracle/fast_recovery_area/testdb/control02.ctl

SQL> SHUT IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

[oracle@demo ~]$ cd /u01/app/oracle/testdb/

[oracle@demo testdb]$ ls

control01.ctl

[oracle@demo testdb]$ mkdir backup

[oracle@demo testdb]$ cp *.ctl backup

[oracle@demo testdb]$ rm *.ctl

[oracle@demo testdb]$ ls

backup

[oracle@demo testdb]$ cd backup/

[oracle@demo backup]$ ls

control01.ctl

[oracle@demo backup]$ cd ..

[oracle@demo testdb]$ cd ..

[oracle@demo oracle]$ cd fast_recovery_area/testdb/

[oracle@demo testdb]$ ls

control02.ctl

[oracle@demo testdb]$ mkdir backup

[oracle@demo testdb]$ cp *.ctl backup

[oracle@demo testdb]$ ls

backup  control02.ctl

[oracle@demo testdb]$ rm *.ctl

[oracle@demo testdb]$ ls

backup

SQL> startup nomount;

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

SQL> @ control.sql

ORA-01081: cannot start already-running ORACLE - shut it down first

CREATE CONTROLFILE SET DATABASE "TEST" NORESETLOGS  ARCHIVELOG

*

ERROR at line 1:

ORA-01503: CREATE CONTROLFILE failed

ORA-01223: RESETLOGS must be specified to set a new database name

SQL> @ control.sql

ORA-01081: cannot start already-running ORACLE - shut it down first

Control file created.

SQL> alter database open resetlogs;

Database altered.

SQL> select name from v$database;

NAME

---------

TEST

MANUAL ENTRY IN ORATAB/ENABLE DBSTART UTILITY TO START DB FROM OS LEVEL

MANUAL ENTRY IN ORATAB/ENABLE DBSTART UTILITY TO START DB FROM OS LEVEL

[oracle@demo ~]$ cat /etc/oratab

#

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

#

prod:/u01/app/oracle/product/11.2.0/dbhome_1:N

[oracle@demo ~]$ cat /etc/oratab

#

# This file is used by ORACLE utilities.  It is created by root.sh

# and updated by either Database Configuration Assistant while creating

# a database or ASM Configuration Assistant while creating ASM instance.

# A colon, ':', is used as the field terminator.  A new line terminates

# the entry.  Lines beginning with a pound sign, '#', are comments.

#

# Entries are of the form:

#   $ORACLE_SID:$ORACLE_HOME:<N|Y>:

#

# The first and second fields are the system identifier and home

# directory of the database respectively.  The third filed indicates

# to the dbstart utility that the database should , "Y", or should not,

# "N", be brought up at system boot time.

#

# Multiple entries with the same $ORACLE_SID are not allowed.

#

#

prod:/u01/app/oracle/product/11.2.0/dbhome_1:Y

testdb:/u01/app/oracle/product/11.2.0/dbhome_1:Y

HOW TO ENABLE THE ARCHIVE MODE

HOW TO ENABLE THE ARCHIVE MODE

SQL> archive log list

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     60

Current log sequence           62

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area  171581440 bytes

Fixed Size                  1343668 bytes

Variable Size             117444428 bytes

Database Buffers           50331648 bytes

Redo Buffers                2461696 bytes

Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     60

Next log sequence to archive   62

Current log sequence           62

MANUAL ORACLE DATABASE CREATION IN ORACLE 11G

Creating Oracle 11g Database manually in 11 steps in Unix-based Operating system
Hi,
This is one of the most basic tasks for Oracle DBA,it also helps us to understand how Oracle Database works.Creating Database in 11g is very simple.In 10g you need to create additional directories bdump,cdump,udump instead of diagnostic dump directory.Below are the steps:


Step 1:Create Directory structure with Oracle ownership and permission as below:

[oracle@localhost ~]$ cd /u03

[oracle@localhost u03]$ mkdir testdb

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u03]$ chmod -R 777 /u03/testdb/*

[oracle@localhost u03]$ chown -R oracle:oinstall /u03/testdb/*


Step 2:Create Parameter file in $ORACLE_HOME/dbs location:

[oracle@localhost testdb]$ cd $ORACLE_HOME/dbs

[oracle@localhost dbs]$ vi init_testdb.ora

db_name='testdb'
memory_target=1G
processes = 150
audit_file_dest='/u03/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u03/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u03/testdb/diag'
dispatchers='(PROTOCOL=TCP) (SERVICE=testdb)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u03/testdb/ora_control1.ctl,/u03/testdb/ora_control2.ctl)
compatible ='11.2.0'


Step 3:Prepare Create Database script :

[oracle@localhost u03]$ cd /u03/testdb/

[oracle@localhost testdb]$ vi createdb_shaik.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u03/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u03/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u03/testdb/redo3.log' SIZE 10M
DATAFILE
'/u03/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u03/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u03/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u03/testdb/temp01.dbf'
SIZE 100M REUSE
CHARACTER SET AL32UTF8
;
:wq

Step 4:Set the Oracle ENVIRONMENT and SID of Database in the Operating System:

[root@localhost testdb]# su - oracle

[oracle@localhost ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=orcl
export TNS_ADMIN=/u01/app/oracle/product/11.2.0/dbhome_1/network/admin

:wq
[oracle@localhost ~]$ . .bash_profile
     This will set the Oracle Environment variables in Unix-based operating system.

[oracle@localhost ~]$export ORACLE_SID=testdb
     This will set the SID of the current Database in Unix-based operating system.


Step 5:Create the Password file.

[oracle@localhost ~]$orapwd file=$ORACLE_HOME/dbs/orapwtestdb password=Oracle entries=10


Step 6:Create server parameter file.

[oracle@localhost dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 28 14:08:02 2012

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

Connected to an idle instance.

SQL>create spfile from pfile='$ORACLE_HOME/dbs/init_testdb.ora';


step 7:Start the Database in nomount State.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
SQL> select status from v$instance;

STATUS
------------
STARTED

Note:Common issue memory_target not supported,refer the below link for resolving:

http://rafioracledba.blogspot.in/2011/06/ora-00845-memorytarget-not-supported-on.html

Step 8:Execute Create Database script created in Step 3

SQL> @/u03/testdb/createdb_shaik.sql

Database created

Step 9:Execute the catalog.sql,catproc.sql and pupbld.sql scripts:
a)catalog.sql =>Creates dictionary tables and views
b)catproc.sql =>Creates PL/SQL procedures,functions and packages necessary.
c)pupbld.sql  =>Creates user profiles.


So our database is created. Now just run the catalog.sql,catproc.sql and pupbld.sql scripts.
WE will find catalog.sql and catproc.sql in $ORACLE_HOME/rdbms/admin path and pupbld.sql in $ORACLE_HOME/sqlplus/admin path.

SQL> @$ORACLE_HOME/rdbms/admin/catalog.sql
SQL> @$ORACLE_HOME/rdbms/admin/catproc.sql
conn system/manager

SQL>@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> alter user system identified by manager;

User altered.

SQL> conn system
Enter password:
Connected.
SQL> @$ORACLE_HOME/sqlplus/admin/pupbld.sql
DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist


  DATE_VALUE FROM PRODUCT_USER_PROFILE
                  *
ERROR at line 3:
ORA-00942: table or view does not exist


DROP TABLE PRODUCT_USER_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


ALTER TABLE SQLPLUS_PRODUCT_PROFILE ADD (LONG_VALUE LONG)
*
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

DROP TABLE PRODUCT_PROFILE
           *
ERROR at line 1:
ORA-00942: table or view does not exist


DROP VIEW PRODUCT_PRIVS
*
ERROR at line 1:
ORA-00942: table or view does not exist



View created.


Grant succeeded.

DROP PUBLIC SYNONYM PRODUCT_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist



Synonym created.

DROP SYNONYM PRODUCT_USER_PROFILE
             *
ERROR at line 1:
ORA-01434: private synonym to be dropped does not exist



Synonym created.

DROP PUBLIC SYNONYM PRODUCT_USER_PROFILE
                    *
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

Synonym created.


Step 10:Verify the Dictionary views created.


SQL> select name from v$database;


Step 11:Change the Database mode from noarchive log to archive log mode
Changing to archive log mode:

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


SQL> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           3
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1341312 bytes
Variable Size             620759168 bytes
Database Buffers          444596224 bytes
Redo Buffers                4636672 bytes
Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> select status from v$instance;

STATUS
------------
MOUNTED

SQL> alter database open;

Database altered.

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   3
Current log sequence           3

This completes the manual Database creation on Unix based Operating system....

Creation of virtual machine for rac-10g(Bundle-1)

VMware Workstation 8

create new virtual machine ---> typical(choose typical) -->next --->(choose) i will install the operating system later -->
next-->(choose) linux and (choose from drop down) Oracle Enterprise linux-->next-->Virtual machine name (write the machine name ex.lnx01)
-->click on Browse and select mycomputer--->d drive-->click on make folder,lnx01-->next-->choose from dropdown Maximum disk size == 60gb and
choose store virtual disk as a single file--->next-->finish

Edit Virtual machine-->click on memory and increse to 3 or 4 gb -->  click on network adopter and choose from the right side
as Bridged:connected directly to the physical network --> Again click on network adopter --> add-->network adopter-->next
-->choose host only : a private network shared with the host --> finish-->clik on processor -->choose Number of cores per processor-- 2 and
number of processor -- 2 --> ok--> click on cd/dvd and choose the use iso image file and browse the iso image of OEL or RHEL  from your drive locaion

Restoring a database in oracle 12c standalone

Restoring a database

Now if you want to restore your logical database you have to create Tablespaces same as like in your source database

Check available table space in your source database using following query

Hide   Copy Code

SELECT * FROM DBA_DATA_FILES

Log in to your created database from SQL plus here is the steps

1.       Go to Start -> All Programs -> Oracle-OraDB12Home -> Application Development -> SQL Plus

2.       Login using SYS and password provided while creating database.

Enter user-name: SYS AS SYSDBA

Password: (Administrative password entered while installing)

Then press Enter…

3.       Run script ("SELECT * FROM DBA_DATA_FILES") to knowthe  available TABLESPACES in new database.
 

4.       Then create remaining TABLESPACES same as like in source server using following queries

Hide   Copy Code

CREATE  TABLESPACE 'tablespace name'  DATAFILE  'path - check the path of listed tablespaces'  SIZE  ? G

5.       If source contains two TABLESPACE files for one TABLESPACE alter the TABLESPACE using following script

Hide   Copy Code

ALTER TABLESPACE 'TABLEspace name' ADD DATAFILE 'path' SIZE ?G;

Once you creates all tablespaces same as like in source server then create user in new server.

Create User using following query

Hide   Copy Code

CREATE USER USERNAME IDENTIFIED BY PASSWORD

GRANT DBA TO USERNAME

If you get ORA-65096 Error

RUN THE FOLLOWING Script

Hide   Copy Code

ALTER SESSION SET "_ORACLE_SCRIPT"=true;

Session will get altered then again run script for creating user and grand user as dba

Creating a database in 12c standalone

Creating a database

Now we will create global database in the installed server.

For that, do the following steps

1.       Go to Start -> All Programs -> Oracle-OraDB12Home -> Configuration and Migration Tools ->

Database Configuration Assistant (right click on it and Run as Administrator)

2.       Select “Create Database” option and click “Next” button.

3.       Enter database name, password and select database character Set. And then click “Next” button.

4.       Loads Database Configuration Summary, click on “Finish” button

5.       Starts creating database file and loads the following window.

6.       Click “finish” button once database is created.

Yes..!! Now the database is created

Oracle Listener

Oracle Net Listener configuration, stored in the listener.ora file, consists of the following elements:

1.       Name of the listener

2.       Protocol addresses that the listener is accepting connection requests on

3.       Database services

4.       Control parameters

Now let us create listener.. Do the following steps for it.

1.  Go to Start -> All Programs -> Oracle-OraDB12Home -> Configuration and Migration Tools -> Net Configuration Assistant.

2. Select first option “Listener configuration” and click “Next” button.

3. Select first option “Add” then click “Next” button.

4.       By default Listener name will be “LISTENER” will be there, just click on “Next” button

5.       Select needed protocols and click “Next”  button

6.       Use the standard port no 1521 or enter your custom port no. then click “Next” button.

7.       If it alerts by asking of configuration another listener select  “No” and click “Next” button

8.       Loads Listener configuration complete message , click “Next”  button

9.       Click “Finish” button

You did it…!!

Oracle 12c in your windows operating system

Steps

I am using Windows 7 - 64Bit operating system.

You can download oracle 12c version from the following link

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html

Main Steps

1.       Decrypt both download files to a single folder

2.      
Double Click on Setup

3.       Loads Oracle Universal Installer

4.       Then, load First Window of Oracle Installation, Enter your email if needed, else leave it as blank and Remove Tick from check box of “I wish to receive security….” and click “Next” button.

5.       The ollowing warning message will display and click on “Yes” button.

6.       Select the second option “Install database software only” from Installation option of loaded window. And click “Next” button.

7. Select the first option “Single Instance database Installation” if you are using single standalone machine. Then click “Next” button.

8.       Select the product languages you needed, by default English will be selected. Click “Next” button

9.       By default Enterprise edition will be selected, just click “Next” button

10.   You can create a user for oracle or just select “Use Windows Built –in Account” option and click “Next” button.

 

11.   Click “Yes” and continue if you get the following message Box.

12.   Click “Next” If the default location has enough space for installation else change the path.

13.   Starts checking the prerequisites

14.   Loads the summary for installation details, Click “Install” button to start installation of the product.

15.   Installation starts and shows the progress of installation.

16.   Once installation finishes successfully click “Close” button.

That’s it you are installed Oracle 12c in your windows operating system.