Online database info

Recovering Dropped tablespace using Flashback Database

Oracle Version :11g
OS: Rhel 6.4

#Turn on flashback and archivelog from 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>alter database archivelog;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> alter database open;

Database altered.

#Now create a tablespace to test the scenario
SQL> create tablespace test datafile '/u01/app/oracle/oradata/prim/testtbs.dbf' size 20m;
Tablespace created.

SQL> create user test identified by test;
User created.

SQL> grant connect , reesource to test;

SQL> alter user test default tablespace test;
User altered.

SQL> conn test/test
Connected.

SQL> create table t1 (id number);

SQL> insert into t1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> select * from t1;
ID
----------
1

SQL> connect sqlplus as sysdba
Enter password:
Connected.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
1075681

#Now drop the tablespace:-
SQL> drop tablespace test including contents and datafiles;
Tablespace dropped.

SQL> shutdown immediate
SQL> startup mount

SQL> flashback database to scn 1075681;
Flashback complete.

SQL> alter database open resetlogs;
Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/UNNAMED00006' as '/u01/app/oracle/oradata/prim/testtbs.dbf';
Database altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf
/u01/app/oracle/oradata/prim/testtbs.dbf

SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
EXAMPLE
TEST

#Login to test schema and verify the data
SQL> conn test/test
Connected.
SQL> select * from t1;

ID
----------
1

Cold Backup Steps and restoring it into destination server

Scenario:-Create a new oracle database on your local system(192.168.72.169) from existing database 'prim' on ip (192.168.72.172) using oracle cold backup

Source database ip :- 192.168.72.169
Destination database ip:- 192.168.72.172
Database version:- Oracle 11g
Os version:- Rhel 6

IN SOURCE DATABASE :

# To take a cold backup first I have to get location of pfile,datafiles,controlfiles,redolog files from
source database. As I have got location like below:-

[oracle@server1 ~]$ sqlplus / as sysdba

SQL>select count(*) from dba_objects;

COUNT(*)
----------
72476

SQL> select open_mode from v$database;

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

SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db_1/dbs/spfileprim.ora

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/system01.dbf
/u01/app/oracle/oradata/prim/sysaux01.dbf
/u01/app/oracle/oradata/prim/undotbs01.dbf
/u01/app/oracle/oradata/prim/users01.dbf
/u01/app/oracle/oradata/prim/example01.dbf

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl

SQL> select group#, member from v$logfile;
---------- ----------------------------------------------------------------------
3 /u01/app/oracle/oradata/prim/redo03.log
2 /u01/app/oracle/oradata/prim/redo02.log
1 /u01/app/oracle/oradata/prim/redo01.log

IN DESTINATION DATABASE(192.168.72.172) :

# Install Oracle Binaries only. Don't Create Database on Destination Server.

# Create folder in target database same as source database and give ownership to oracle user and oinstall group.
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/oradata/prim
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/flash_recovery_area/prim
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/admin/prim/adump
[oracle@server1 ~]$ mkdir -p /u01/app/oracle/product/11.2.0/db_1/dbs
[oracle@server1 ~]$ chown -Rf oracle:oinstall /u01

IN SOURCE DATABASE :
# Shut Down the database.
SQL> shut immediate;

Copy controlfiles, datafiles, logfiles and pfile from source to destination in the same location as oracle user :-

# scp -r /u01/app/oracle/oradata/prim/* oracle@192.168.72.172:/u01/app/oracle/oradata/prim
# scp /u01/app/oracle/flash_recovery_area/prim/control02.ctl oracle@192.168.72.172:/u01/app/oracle/flash_recovery_area/prim/
# scp /u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora oracle@192.168.72.172:/u01/app/oracle/product/11.2.0/db_1/dbs/

IN DESTINATION DATABASE :

# Set Oracle SID
export ORACLE_SID=prim
# Login as sysdba
sqlplus / as sysdba
# Start the database in nomount stage using pfile.
SQL> STARTUP nomount PFILE =/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora
# Enter in mount stage :-
SQL> alter database mount;
# Open database:-
SQL> alter database open;
# Create spfile from pfile
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
# Restart the database to use default spfile.
SQL> shut immediate;
SQL> startup;

SQL>select count(*) from dba_objects;

COUNT(*)
----------
72476

IN SOURCE DATABASE :
# Start the source Database
SQL> startup;

Hence we can say the database has been successfully restored from source database using cold backup.

Restrict User access to database within certain limit of time

We need to create a trigger for this purpose.

[oracle@server1 ~]$ sqlplus / as sysdba

SQL> create user sam identified by sam;

User created.

SQL> grant connect , resource to sam;

Grant succeeded.

SQL> conn sam/sam
Connected.
SQL> exit

[oracle@server1 ~]$ sqlplus / as sysdba
SQL> CREATE OR REPLACE TRIGGER limit_connection
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'SAM' THEN
IF to_number(TO_CHAR (SYSDATE, 'hh24')) BETWEEN 20 AND 22
THEN
RAISE_APPLICATION_ERROR(-20998,' Dear user '||USER||'! You can''t login between 20hrs to 22hrs');
END IF;
END IF;
END limit_connection;
/

Trigger created.

SQL> select to_char(sysdate,'hh24') from dual;

TO
--
21

SQL> conn sam/sam
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-20998: Dear user SAM! You can't login between 20hrs to 22hrs
ORA-06512: at line 5
Warning: You are no longer connected to ORACLE.

Done...

How to Multiplex Redo Log Files in Oracle 11g

Step 1:- List all the current redo log file in database
SQL> Select * from v$logfile;

GROUP# TYPE MEMBER
---------- ------- ------- --------------------------------------
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log

Step-2: Add redo log file members for different location

ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redolog/redo01.log' TO GROUP 1;
Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redolog/redo02.log' TO GROUP 2;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE MEMBER '/home/oracle/redolog/redo03.log' TO GROUP 3;

Database altered.

SQL> alter system switch logfile;

SQL> select GROUP#,SEQUENCE#,(BYTES/1024/1024)MB,MEMBERS from v$log;

GROUP# SEQUENCE# (BYTES/1024/1024)MB MEMBERS
---------- ---------- ----------------- ----------
1 7 50 2
2 5 50 2
3 6 50 2

SQL> Select * from v$logfile;

GROUP# STATUS TYPE MEMBER IS_
---------- ------- ------- -------------------------------------------------- ---
3 ONLINE /u01/app/oracle/oradata/prim/redo03.log NO
2 ONLINE /u01/app/oracle/oradata/prim/redo02.log NO
1 ONLINE /u01/app/oracle/oradata/prim/redo01.log NO
1 ONLINE /home/oracle/redolog/redo01.log NO
2 ONLINE /home/oracle/redolog/redo02.log NO
3 ONLINE /home/oracle/redolog/redo03.log

Done..

How to Multiplex Control Files in Oracle 11g

Step 1:-Backup your current control file with a trace option
SQL> ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

Database altered.

Step-2: List all the controlfiles

SQL> select name from v$controlfile;

NAME
---------------------------------------------------------
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl

Step-3 Shutdown the database
SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4:-Add one more controlfile
[oracle@server1 prim]$cp /u01/app/oracle/oradata/prim/control01.ctl /home/oracle/Multiplexed_control_files/control03.ctl

Step-5: Create PFILE using SPFILE
[oracle@server1 dbs]$ sqlplus / as sysdba
SQL> create pfile from spfile;

File created.

[oracle@server1 prim]$ cd /u01/app/oracle/product/11.2.0/db_1/dbs/
##Add the newly created control file.
[oracle@server1 dbs]$ vi initprim.ora
*.control_files='/u01/app/oracle/oradata/prim/control01.ctl','/u01/app/oracle/flash_recovery_area/prim/control02.ctl','/home/oracle/Multiplexed_control_files/control03.ctl'

:wq

Step 5:-Create SPFILE using PFILE
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initprim.ora';
File created.

Step 6:- Start the database
SQL> startup
ORACLE instance started.

Total System Global Area 413372416 bytes
Fixed Size 2213896 bytes
Variable Size 327157752 bytes
Database Buffers 79691776 bytes
Redo Buffers 4308992 bytes
Database mounted.
Database opened.

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/prim/control01.ctl
/u01/app/oracle/flash_recovery_area/prim/control02.ctl
/home/oracle/Multiplexed_control_files/control03.ctl

Done...

What's View and Synonym?

What is a view?

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

A view is based on a table or another view and acts as a window through which data on tables can be viewed or changed. A view does not contain data. The definition of the view is stored in the data dictionary. You can see definition of view in user_view data dictionary table.
A view is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables.

What is a synonym and what types it has?

A synonym is an alias for a schema object. Synonyms can provide a level of security by masking the name and owner of an object and by providing location transparency for remote objects of a distributed database. Also, they are convenient to use and reduce the complexity of SQL statements for database users.

Synonyms allow underlying objects to be renamed or moved, where only the synonym needs to be redefined and applications based on the synonym continue to function without modification.

You can create both public and private synonyms. A public synonym is owned by the special user group named PUBLIC and is accessible to every user in a database. A private synonym is contained in the schema of a specific user and available only to the user and the user’s grantees.

Create oracle 11g database manually on linux

Make sure oracle binaries are installed.We will proceed with database installation if binaries are already installed.

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

[oracle@localhost ~]$ cd /u01

[oracle@localhost u01]$ mkdir testdb

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

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

[oracle@localhost u01] cd testdb

[oracle@localhost testdb]$ mkdir adump diag flash_recovery_area

[oracle@localhost u01]$ chmod -Rf 777 /u01/testdb/*

[oracle@localhost u01]$ chown -Rf oracle:oinstall /u01/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='/u01/testdb/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/testdb/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/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 = (/u01/testdb/ora_control1.ctl,/u01/testdb/ora_control2.ctl)
compatible ='11.2.0'

Step 3:Prepare Create Database script :

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

[oracle@localhost testdb]$ vi createdb_test.sql

CREATE DATABASE testdb
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
LOGFILE
GROUP 1 '/u01/testdb/redo1.log' SIZE 10M,
GROUP 2 '/u01/testdb/redo2.log' SIZE 10M,
GROUP 3 '/u01/testdb/redo3.log' SIZE 10M
DATAFILE
'/u01/testdb/system.dbf' size 200M REUSE
sysaux datafile '/u01/testdb/sysaux.dbf' size 100m
undo tablespace UNDOTBS1
datafile '/u01/testdb/undo1.dbf' size 100m
DEFAULT TEMPORARY TABLESPACE temp1
TEMPFILE '/u01/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=testdb
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

Step 8:Execute Create Database script created in Step 3

SQL> @/u01/testdb/createdb_test.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;
NAME
---------
TESTDB

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....

Enjoy learning...

Please share your ideas and opinions about this topic.

If you like this post, then please share with others.
Please subscribe on email for every updates on mail.