HOW TO RESTORE A DROPPED TABLE

  We can restore a table that we  dropped unfortunately or knowingly by the help of below steps :


[oracle@localhost ~]$ sqlplus '/as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Fri Sep 6 10:38:48 2019

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

Connected to an idle instance.

SQL> startup;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size     2254952 bytes
Variable Size   503318424 bytes
Database Buffers 1627389952 bytes
Redo Buffers     4923392 bytes
Database mounted.
Database opened.


SQL> conn football/messi

Error accessing PRODUCT_USER_PROFILE
Warning:  Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
SQL> select * from tab;

TNAME               TABTYPE CLUSTERID
------------------------------ ------- ----------
BARCELONA                           TABLE
MANCHESTERCITY          TABLE
TOTTENHAM                           TABLE

SQL> drop table BARCELONA;

Table dropped.

SQL> drop table manchestercity;

Table dropped.

SQL> select original_name from dba_recyclebin;

ORIGINAL_NAME
--------------------------------
BARCELONA
MANCHESTERCITY


SQL> flashback table barcelona to before drop;

Flashback complete.

SQL> flashback table manchestercity to before drop;

Flashback complete.

SQL> select * from tab;

TNAME        TABTYPE CLUSTERID
------------------------------ ------- ----------
BARCELONA                      TABLE
MANCHESTERCITY          TABLE
TOTTENHAM                      TABLE


SQL>

HOW TO MOVE TABLE FROM ONE TABLESPACE TO ANOTHER TABLESPACE

           Here the easy steps to move a table from one tablespace to another tablespace !!


SQL> select  tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS
TEMP
KAVIN
FOOTBALL

6 rows selected.

SQL> create table spurs (playername VARCHAR(20),goals INT);

Table created.

SQL> insert into spurs (playername,goals)

  2  values ('Harry kane',187);

1 row created.

SQL> select * from tab;

TNAME        TABTYPE CLUSTERID

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

SPURS        TABLE

SQL> show user;

USER is "FOOTBALL"

SQL> 

SQL> select * from tab;

TNAME        TABTYPE CLUSTERID

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

SPURS        TABLE

SQL> alter table spurs move tablespace kavin;

Table altered.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME        TABLESPACE_NAME

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

SPURS                 KAVIN

SQL> alter table spurs move tablespace football;

Table altered.

SQL> select table_name,tablespace_name from user_tables;

TABLE_NAME        TABLESPACE_NAME

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

SPURS                    FOOTBALL

SQL> 

HOW TO RESIZE REDOLOG FILES

 We cannot resize the redolog files .We must drop the redo log files and recreate .This           is only method to resize the redo log files. 

SQL> startup;

ORACLE instance started.

Total System Global Area 2137886720 bytes

Fixed Size     2254952 bytes

Variable Size   503318424 bytes

Database Buffers 1627389952 bytes

Redo Buffers     4923392 bytes

Database mounted.

Database opened.

SQL> select group#,status,member from v$logfile;

GROUP# STATUS

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

MEMBER

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

3

/apps/oradata/redologs1/redo03.log

2

/apps/oradata/redologs1/redo02.log

1

/apps/oradata/redologs1/redo01.log

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

INACTIVE   1 50

CURRENT 2 50

UNUSED   3 50

[ ( SQL> alter database logfile group 1 '/apps/oradata/redologs1/redo01.log' resize 100m;

alter database logfile group 1 '/apps/oradata/redologs1/redo01.log' resize 100m

                       *

ERROR at line 1:

ORA-02231: missing or invalid option to ALTER DATABASE 

* We cannot easily drop a redolog file by using above sql queries) ]

SQL> alter system switch logfile;

System altered.

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

CURRENT  1 50

ACTIVE     2 50

ACTIVE     3 50

SQL> alter system switch logfile;

System altered.

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

INACTIVE   1 50

CURRENT  2 50

INACTIVE   3 50

Now we can drop the inactive redolog group 1 & 3;

SQL> alter system checkpoint global;

System altered.

( The following set of operations starts when checkpoint occurs: Log records from log buffer (including the last log record) are written to the disk  )

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m;

alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m

*

ERROR at line 1:

ORA-00301: error in adding log file '/apps/oradata/redologs1/redo01.log' - file

cannot be created

ORA-27038: created file already exists

Additional information: 1

*We have remove or delete the old redologs in OS level command :

SQL> exit

Disconnected from Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

[oracle@localhost dbs]$ cd /apps/oradata/redologs1/

[oracle@localhost redologs1]$ ls

redo01.log  redo02.log  redo03.log  redolog3a.log

[oracle@localhost redologs1]$ rm redo01.log

[oracle@localhost redologs1]$ ls

redo02.log  redo03.log  redolog3a.log

[oracle@localhost redologs1]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Sep 25 11:28:35 2019

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

Connected to:

Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

CURRENT   2 50

INACTIVE   3 50

SQL> alter database add logfile group 1 '/apps/oradata/redologs1/redo01.log' size 100m;

Database altered.

SQL> select status,group#,bytes/1024/1024 from v$log;

STATUS      GROUP# BYTES/1024/1024

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

UNUSED        1 100

CURRENT     2 50 

INACTIVE   3 50

   In beginning the size of redolog group 1 is of 50m and by now the group 1 is resized as 100m

                                    The simplest way to resize a redo logs is done !! 

HOW TO INSTALL ORACLE 11G DATABASE SOFTWARE IN LINUX

             HOW TO INSTALL  ORACLE 11G DATABASE SOFTWARE IN LINUX !!


[root@localhost ~]#useradd oracle
[root@localhost ~]#groupadd oinstall
[root@localhost ~]#groupadd dba
[root@localhost ~]#usermod -g oinstall -G dba oracle

[root@localhost apps] mkdir -p oracle/oracle_base/rdbms/product/11.2.0/db_home/

[root@localhost oradata]# mkdir datafiles redologs controlfiles udump bdump cdump
[root@localhost oradata]#ls
controlfiles  datafiles  redologs udump bdump cdump

[root@localhost apps]#  mkdir -p oradump/software


[root@localhost /]# chown -R oracle:oinstall apps

drwxr-xr-x.   5 oracle oinstall  4096 Nov 19  2018 apps
dr-xr-xr-x.   2 root   root      4096 Aug 30 12:28 bin
dr-xr-xr-x.   5 root   root      4096 Nov 20  2018 boot


[root@localhost apps]# cd oradump/software/
[root@localhost software]# ll
total 2487208
-rwxr-xr-x. 1 oracle oinstall 1395582860 Aug 30 12:39 p13390677_112040_Linux-x86-64_1of7.zip
-rwxr-xr-x. 1 oracle oinstall 1151304589 Aug 30 12:42 p13390677_112040_Linux-x86-64_2of7.zip

[root@localhost software]# unzip p13390677_112040_Linux-x86-64_1of7.zip


[root@localhost software]# unzip p13390677_112040_Linux-x86-64_2of7.zip

[root@localhost software]# su - oracle

[oracle@localhost ~]$ cd /apps/oradump/software/
[oracle@localhost software]# ls

database
p13390677_112040_Linux-x86-64_2of7.zip
p13390677_112040_Linux-x86-64_1of7.zip

[oracle@localhost software]$ cd database/
install      response  runInstaller  stage
readme.html  rpm       sshsetup      welcome.html

[oracle@localhost database]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 13238 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 3999 MB    Passed
Checking monitor: must be configured to display at least 256 colors.    Actual 16777216    Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2019-08-30_01-02-45PM. Please wait ...[oracle@localhost database]$

By typing the ( yum install package_name)  utility install the required packages;

[root@localhost software]# yum install elfutils-libelf-devel*Complete!
[root@localhost software]#

 Login as root and install the above packages ;

[root@localhost ~]# /apps/oracle/oraInventory/orainstRoot.sh
[root@localhost ~]# /apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/root.sh


[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_HOME=/apps/oracle/oracle_base/rdbms/product/11.2.0/db_home
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_SID=orcl


[oracle@localhost ~]$ . .bash_profile
[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 30 15:05:14 2019

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

Connected to an idle instance.

SQL> exit

[oracle@localhost ~]$ cd /apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/dbs
[oracle@localhost dbs]$ ls
createdatabase.sql  init.ora  initorcl.ora


[oracle@localhost ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Fri Aug 30 15:15:56 2019

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

Connected to an idle instance.

SQL> startup nomount pfile='/apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/dbs/initorcl.ora'
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size     2254952 bytes
Variable Size   503318424 bytes
Database Buffers 1627389952 bytes
Redo Buffers     4923392 bytes
SQL> @/apps/oracle/oracle_base/rdbms/product/11.2.0/db_home/dbs/createdatabase.sql;

Database created.


SQL> select name,open_mode from v$database;

NAME   OPEN_MODE
--------- --------------------
ORCL   READ WRITE

SQL>




                                         The simplest way to create database is done !!