Oracle Backup script for Windows

Oracle11g  Backup script with 7 days retention policy for Windows

Step 1 Create a DIRECTORY EXPDPDUMPBACKUP

CREATE DIRECTORY EXPDPDUMPBACKUP AS 'D:\Backup\Daily_EXPDP_Backup';

GRANT READ, WRITE ON DIRECTORY EXPDPDUMPBACKUP TO system;

Step 2 Create a (.bat) file with name backup_script.bat

Here is you need to change the ORACLE_SID,ORACLE_HOME,ORACLE_BASE,USER NAME/PASSWORD
as per your configuration.

@ECHO OFF
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set mydate=%%c-%%b-%%a)
For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b)
set mytime=%mytime: =0%

set ORACLE_SID=orcl
set ORACLE_HOME=D:\oracle\product\11.2.0\dbhome_1
set ORACLE_BASE=D:\oracle\oradata\
set PATH=%ORACLE_HOME%\bin
set BACKUPPATH=D:\Backup\Daily_EXPDP_Backup
forfiles /p "D:\Backup\Daily_EXPDP_Backup" /m * /d -7 /c "cmd /c rd /s /q @path"
cd %BACKUPPATH%
mkdir "D:\Backup\Daily_EXPDP_Backup\%mydate%_%mytime%"


rem %ORACLE_HOME%\BIN\expdp
expdp USER/*****@orcl directory=EXPDPDUMPBACKUP Full=Y dumpfile=backup_%mydate%_%mytime%.dmp logfile=log_%mydate%_%mytime%.log
cd %BACKUPPATH%

move D:\Backup\Daily_EXPDP_Backup\Backup_%mydate%_%mytime%.dmp D:\Backup\Daily_EXPDP_Backup\%mydate%_%mytime%\
move D:\Backup\Daily_EXPDP_Backup\log_%mydate%_%mytime%.log D:\Backup\Daily_EXPDP_Backup\%mydate%_%mytime%\

How to setup Oracle Wallet

An Oracle Wallet is nothing more than a protected logical container (a single file named ewallet.p12)
that is used for the secure external password store. Multiple wallets may be created on a machine; however,
each wallet should be contained in its own directory. Using a wallet for the secure external password store is
not the only use of an Oracle wallet. The wallet can also be used to store encrypted keys needed by the Oracle database in order to access
SSL sites as well as many of the more advanced security options in Oracle like Transparent Data Encryption (TDE) or PKI Credentials.
These advanced options are part of Oracle's Advanced Security Option (ASO) and are only available when using Oracle Enterprise Edition.

Note that creating an Oracle wallet for the secure external password store (and importing keys to access SSL sites) can be done using Oracle Standard Edition.
 It is only when using the advanced features like TDE or PKI credentials that require the Advanced Security Option and Enterprise Edition.


Create  wallet Location where you want to store the wallet creds

mkdir -p /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets

Give the permission to wallet directory
chown oracle:dba /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets

Now create a wallet and give the password
mkstore -wrl "/u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets" -create

wallets pass
passwd oracle123

Now give the RWX permision to wallets
chmod -Rf 777  /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets

Now setup the sqlnet.ora tnsname.ora listener.ora file

===
[root@dba admin]# cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (GLOBAL_DBNAME = oradb )
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0.4.0/db_1)
     (SID_NAME = oradb )
    )
   )

ADR_BASE_LISTENER = /u01/app/oracle

[root@dba admin]# cat sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets)
     )
   )

SQLNET.WALLET_OVERRIDE = TRUE
SSL_CLIENT_AUTHENTICATION = FALSE

ADR_BASE = /u01/app/oracle
NAMES.DIRECTORY_PATH= (TNSNAMES)


[root@dba admin]# cat tnsnames.ora

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

FIN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

ORADB =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oradb)
    )
  )


DBABHI=
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.40)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME =ORADB)
    )
  )
[root@dba admin]#
===

NOTE Here we use fin db_alias for hr user and DBABHI db_alias use for dbabahi user
Now store the credential of user's in wallets.Make sure the user are unlock in database

mkstore -wrl <wallet_location> -createCredential <dbase_alias> <username> <password>

[oracle@dba ~]$ mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets/ -createCredential dbabhi dbabhi dbabhi
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:          

Create credential oracle.security.client.connect_string2

[oracle@dba ~]$ mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets/ -createCredential FIN HR hr
Oracle Secret Store Tool : Version 11.2.0.4.0 - Production
Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:          

Create credential oracle.security.client.connect_string1

Now connet to the database

[oracle@dba ~]$ sqlplus /@dbabhi

SQL*Plus: Release 11.2.0.4.0 Production on Mon Jun 5 16:13:17 2017

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


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

SQL> show user
USER is "DBABHI"
SQL> exit
==

Set up a shell script to access the dbabhi user:

[oracle@dba ~]$ vi dbabhi.sh
#!/bin/bash

LogDirectory='/tmp/logs'
DataDirectory='/tmp/data'
sqlplus  <<EOF  > ${LogDirectory}/query_dbabhi.log
/@dbabhi
set feedback off
set heading off

spool ${DataDirectory}/query_output_dbabhi.txt


select * from test;

show user;

spool off

EOF
==

vi query_output_dbabhi.txt

select * from test;
NAME          MARKS
UDIT            100
SOUMYA     100
THAKUR     90

show user;

dbabhi


==
Administering the Wallet

Listing credentials stored in the wallet:

mkstore -wrl <wallet_location> -listCredential

mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets -listCredential

[oracle@dba ~]$ mkstore -wrl /u01/app/oracle/product/11.2.0.4.0/db_1/network/admin/wallets -listCredential

Oracle Secret Store Tool : Version 11.2.0.4.0 - Production

Copyright (c) 2004, 2013, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:          
 
List credential (index: connect_string username)
2: dbabhi dbabhi
1: FIN HR
==

Modifying credentials stored in the wallet:

mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

Deleting credentials stored in the wallet:

mkstore -wrl <wallet_location> -deleteCredential <db_alias>
Deleting the whole wallet:

rm -rf <wallet_location>

==

Oracle 12C Installation on centos

Oracle 12g Installation on centos

Install oracle 12c using xmanager.
create a database named orcl. Binaries should be installed inside /u01
Datafiles should be under /data01
Redolog files should be stored inside /redoA , /redoB , /redoC .Three different mount point for 3 diff groups.
All group must have 2 members in different mount point.
undo tablespace should be stored in /undotbs 
and temporary tablespace in /temptbs

1) First We have to disable selinux from this file

      This file controls the state of SELinux on the system.

# SELINUX= yum:

#     enforcing - SELinux security policy is enforced.

#     permissive - SELinux prints warnings instead of enforcing.

edit the parameter and save :wq

[root@ab ~]# vi /etc/selinux/config

SELINUX=disable

[root@ab ~]# service iptables stop

[root@ab ~]# service iptables save

Disable the firewall.

Then restart the service

#service network restart

2) Edit host file & edit the following things at last line-

[root@ab ~]#  vi /etc/hosts

 192.168.100.115 ab.kmi.com  abhishek

3) We will configure the yum with .

Login as root and issue the following command.

(for eg... [root@ab ~]# yum install binutils-*)

[root@ab ~]# yum install <packages>

    yum install binutils -y

    yum install compat-libcap1 -y

    yum install compat-libstdc++-33 -y

    yum install compat-libstdc++-33.i686 -y

    yum install gcc -y

    yum install gcc-c++ -y

    yum install glibc -y

    yum install glibc.i686 -y

    yum install glibc-devel -y

    yum install glibc-devel.i686 -y

    yum install ksh -y

    yum install libgcc -y

    yum install libgcc.i686 -y

    yum install libstdc++ -y

    yum install libstdc++.i686 -y

    yum install libstdc++-devel -y

    yum install libstdc++-devel.i686 -y

    yum install libaio -y

    yum install libaio.i686 -y

    yum install libaio-devel -y

    yum install libaio-devel.i686 -y

    yum install libXext -y

    yum install libXext.i686 -y

    yum install libXtst -y

    yum install libXtst.i686 -y

    yum install libX11 -y

After Successful installation of Linux/CentOS6. Update the yum with help of

... [root@ab ~]# yum update

4) First we will add groups by running the following commands

[root@ab ~]# groupadd oper
[root@ab ~]# groupadd dba
[root@ab ~]# groupadd oinstall

5)Then we will add user oracle, with primary group Oninstall & secondary group G dba,oper

[root@ab ~]# useradd -g oinstall -G dba,oper  oracle

6) Then we will assign password to this user. The password assigned to this user is oracle however you can choose your own password.

[root@ab ~]# passwd oracle

7) Now we will make a directory for all the oracle components.

[root@ab ~]# mkdir -p /u01/app/oracle/product/11.2.0/db_1

[root@ab ~]#mkdir - /redoa       #Redolog files should be stored inside /redoA , /redoB , /redoC .

[root@ab ~]#mkdir - /redob

[root@ab ~]#mkdir - /redoc

[root@ab ~]#mkdir - /undotbs_1    #undo tablespace should be stored in /undotbs

[root@ab ~]#mkdir - /temptbs_1   #temporary tablespace in /temptbs

[root@ab ~]#mkdir - /data_1        #Datafiles should be under /data01

8) Give permissions to the above created folder.

[root@ab ~]# chmod -R 777 /u01

[root@ab ~]# chmod -R 777 /redoa

[root@ab ~]# chmod -R 777 /redob

[root@ab ~]# chmod -R 777 /redoc

[root@ab ~]# chmod -R 777 /data_1

[root@ab ~]# chmod -R 777 /temptbs_1

[root@ab ~]# chmod -R 777 /undotbs_1

9) Choose the owner and group for that folder.

[root@ab ~]# chown -R oracle:oinstall /u01

[root@ab ~]# chown -R oracle:oinstall /redoa

[root@ab ~]# chown -R oracle:oinstall /redob

[root@ab ~]# chown -R oracle:oinstall /redoc

[root@ab ~]# chown -R oracle:oinstall / data_1

[root@ab ~]# chown -R oracle:oinstall / temptbs_1

[root@ab ~]# chown -R oracle:oinstall / undotbs_1

10) Correct the Default limit for number of user's processes to prevent.

[root@ab ~]# vi /etc/security/limits.d/90-nproc.conf

# Change number of process

* soft    nproc    1024

# To this

    *  nproc 16384

Prerequisites to installing Oracle 11gR2

11)Open the following file and add the kernel parameters to the last line.

[root@ab ~]# vi /etc/sysctl.conf

fs.file-max = 6815744

    kernel.sem = 250 32000 100 128

    kernel.shmmni = 4096

    kernel.shmall = 1073741824

    kernel.shmmax = 4398046511104

    net.core.rmem_default = 262144

    net.core.rmem_max = 4194304

    net.core.wmem_default = 262144

    net.core.wmem_max = 1048576

    fs.aio-max-nr = 1048576

    net.ipv4.ip_local_port_range = 9000 65500

12)  This command are used  to change the current kernel parameters in the sysctl file

root@ab ~]# /sbin/sysctl -p

13)Update /etc/security/limits.conf#  "#soft" for enforcing the soft limits

                                                                  # "hard" for enforcing hard limits

Scroll to the bottom and above the “# End of file” line, add,and save with :wq

[root@ab ~]#  vi/etc/security/limits.conf

    oracle   soft   nofile    1024

    oracle   hard   nofile    65536

    oracle   soft   nproc    2047

    oracle   hard   nproc    16384

    oracle   soft   stack    10240

    oracle   hard   stack    32768

14) Setting up the bash profile. Add the following (use hostname from the command line to get your hostname and use the correct paths for your install)

 [root@ab ~]# su - oracle

[oracle@ab ~]$hostname

[oracle@ab ~]$vi .bash_profile

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PATH

ORACLE_HOSTNAME=(Set the host name); export ORACLE_HOSTNAME

ORACLE_UNQNAME=(set the uniquename); export ORACLE_UNQNAME

ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE

ORACLE_HOME=$ORACLE_BASE/product/12.2.0/db_1; export ORACLE_HOME

ORACLE_SID=(Set the SID); export ORACLE_SID

PATH=/usr/sbin:$PATH; export PATH

PATH=$ORACLE_HOME/bin:$PATH; export PATH

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH

CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

                        then after save the information and exit.

Now we are ready to install the oracle software.

15) Make a folder in the /u01 folder as "soft" and  copy/unzip the oracle database files in that folder.

[root@ab ~] # /u01/soft

[root@ab ~] #chmod -R 777 /u01/soft

copy/unzip the oracle database files in that folder.

[root@ab ~] # cd /u01/soft

[root@ab /u01/soft~] # unzip linux.x64_11gR2_database_1of2_2.zip

[root@ab /u01/soft~] # unzip linux.x64_11gR2_database_2of2_2.zip

16) We will be install & used X-manager in window system to graphically export Linux screen  in window to install Oracle 11g.

after install a Xmanager then connect with.

window will be open and fill

Session name : oracle

Host : (ip of machine)

Set Protaocal : SSH

User name : root

Authentication

command  :  choose the 1xterm

                    xterm -ls -display $DISPLAY

then save and run.

17) Then the window are open and cd/u01/soft/databasego to the oracle directory.

[root@ab ~] # su - oracle

[oracle@ab ~] #cd /u01/soft/database/

[oracle@ab database]$ ls - ltr

[oracle@ab database]$ ./runInstaller * ( the installation process started) fallow the step one by one.

Final Step

18) When the oracle installation is finished it will ask to run two scripts as the root user.

open a new terminal and run the scripts exactly as they are written one by one. in root user

[root@ab ~]# /u01/app/oraInventory/orainstRoot.sh

[root@ab ~]# /u01/app/oracle/product/13.2.0/db_1/root.sh

after running the scripts click ok and exit the enterprise manager.

now your oracle software installation is finished.

Now we are ready to create  the database with the help of DBCA.

[oracle@ab /]$ dbca

Step 1

Sept to choose advance mode

Setp3 chosse genreal purpose

set 4 set gobal db name

 

step 5

step 6 set password

step 7

step 8 set file location variable

uncheck the user oracle-manage file box

step 8 set redo log destination

step 9

Step 10

step 11click coustamize storage location

set11 continue click on storage file and edit its location where we want to stored it.

edit control file location as per scenarios

edit data file location as per scenarios

edit redo file location as per scenarios

FInal Step

Way  to connect with Database and perform sum action.

[root@ab ~]# su - oracle

[oracle@ab ~]$ export ORACLE_SID=orcl

[oracle@ab ~]$ sqlplus / as sysdba

How to get the status of database.

SQL> select status from v$instance;

STATUS

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

OPEN

How to get the databasename.

SQL> select dbid,name from v$database;

      DBID  NAME

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

3450597470 ORCL

How to get the controlfile location.

SQL> show parameter control_files;

NAME                                 TYPE        VALUE

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

control_files                        string      /u01/app/oracle/oradata/dborcl

                                                 /control01.ctl, /u01/app/oracl

                                                 e/oradata/dborcl/control02.ctl

How to get the redo log location.

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

    GROUP#                   MEMBER

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

         3                         /redoc/dborcl/redo03.log

         2                         /redob/dborcl/redo02.log

         1                        /redoa/dborcl/redo01.log

How to get the redo log location.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                                                TABLESPACE_NAME

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

/u01/app/oracle/oradata/dborcl/users01.dbf               USERS

/undotbs_1/dborcl/undotbs01.dbf                                   UNDOTBS1

/u01/app/oracle/oradata/dborcl/sysaux01.dbf              SYSAUX

/u01/app/oracle/oradata/dborcl/system01.dbf             SYSTEM

/u01/app/oracle/oradata/dborcl/example01.dbf            EXAMPLE

How to get the Tempfile location.

SQL>  select file_name,tablespace_name from dba_temp_files;

FILE_NAME                                                                         TABLESPACE_NAME

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

/temptbs_1/dborcl/temp01.dbf                                               TEMP

...................................................................Finish.......................................................................................

Move datafiles (system,sysaux,users,undo,temp)from one location to another where database shutdown is permitted.

Move datafiles (system,sysaux,users,undo,temp)from one location to another where database
shutdown is permitted.


..First step..........perform some tasks

To know the how many Tablesapces in our database;


SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

To know the datafiles of all Tablesapces.



SQL> select name from v$datafile;

NAME
----------------------------------------
/u01/app/oracle/oradata/dborcl/system01.
dbf

/u01/app/oracle/oradata/dborcl/example01
.dbf

/u01/app/oracle/oradata/dborcl/sysaux01.
dbf

/undotbs_1/dborcl/undotbs01.dbf
/u01/app/oracle/oradata/dborcl/users01.d



........................
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

.....2............................copy the old location datafile to another file location................


[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/system01.dbf /redoa/system01.dbf
[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/example01.dbf /redob/example01.dbf
[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/sysaux01.dbf /redoc/sysaux01.dbf
[oracle@ab ~]$ cp /undotbs_1/dborcl/undotbs01.dbf /temptbs_1/undotbs01.dbf
[oracle@ab ~]$ cp /temptbs_1/dborcl/temp01.dbf /undotbs_1/temp01.dbf
[oracle@ab ~]$ cp /u01/app/oracle/oradata/dborcl/users01.dbf /u01/app/users01.dbf


.....2...............alter the database files to new location. startup the database in mount satge...............




[oracle@ab ~]$ export ORACLE_SID=dborcl
[oracle@ab ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 12 18:46:56 2015

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size                  2294456 bytes
Variable Size             775949640 bytes
Database Buffers          100663296 bytes
Redo Buffers                6303744 bytes
Database mounted.




SQL> alter database rename file '/u01/app/oracle/oradata/dborcl/system01.dbf' to '/redoa/system01.dbf';

Database altered.

SQL>  alter database rename file '/u01/app/oracle/oradata/dborcl/example01.dbf' to '/redob/example01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/dborcl/sysaux01.dbf' to '/redoc/sysaux01.dbf';

Database altered.

SQL> alter database rename file '/undotbs_1/dborcl/undotbs01.dbf' to '/temptbs_1/undotbs01.dbf';

Database altered.

SQL> alter database rename file '/temptbs_1/dborcl/temp01.dbf' to '/undotbs_1/temp01.dbf';

Database altered.

SQL> alter database rename file '/u01/app/oracle/oradata/dborcl/users01.dbf' to '/u01/app/users01.dbf';

Database altered.

SQL>
.........3.......................open the database................................................

SQL> alter database open;

Database altered.


.........4...............now chesk the location of datafiles............................................

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/users01.dbf

UNDOTBS1
/temptbs_1/undotbs01.dbf

SYSAUX
/redoc/sysaux01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/redoa/system01.dbf

EXAMPLE
/redob/example01.dbf

Move datafiles(system,sysaux,users,undo,temp) from one location to another with least downtime of database.

Move datafiles(system,sysaux,users,undo,temp) from one location to another with least downtime of database.



Perform some tasks...................................................

............................how to know thw how many tablesapes in our database

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
EXAMPLE

6 rows selected.

...................................To know the data file location.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/redoa/system01.dbf
/redob/example01.dbf
/redoc/sysaux01.dbf
/temptbs_1/undotbs01.dbf
/u01/app/users01.dbf

...................................To know the data file and associated tablesapce name with location.


SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/users01.dbf

UNDOTBS1
/temptbs_1/undotbs01.dbf

SYSAUX
/redoc/sysaux01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/redoa/system01.dbf

EXAMPLE
/redob/example01.dbf
........................................................................



...............how to change the location of datafile without shutdown the database................................



.........1 offline the tablespce......


SQL> alter tablespace users offline
  2  ;

Tablespace altered.


..........2.Os lavel copy the datafile to another location where we want........................................................

cp /u01/app/users01.dbf /u01/app/oracle/oradata/dborcl/users01.dbf


...................3 alter the datafile...........................

SQL> alter tablespace users rename datafile '/u01/app/users01.dbf' to '/u01/app/oracle/oradata/dborcl/users01.dbf'
  2  ;

Tablespace altered.

........................4 online the tablespace...........................

SQL> alter tablespace users online;

Tablespace altered.


..........................................




..........................................................1 offline the tablespce...

.SQL> alter tablespace example offline;

Tablespace altered.


2.Os lavel copy the datafile to another location where we want

[oracle@ab ~]$ cp /redob/example01.dbf /u01/app/oracle/oradata/dborcl/example01.dbf



...................3 alter the datafile...........................


SQL> alter tablespace example rename datafile '/redob/example01.dbf' to '/u01/app/oracle/oradata/dborcl/example01.dbf';

Tablespace altered.




........................4 online the tablespace...........................

SQL> alter tablespace example online;

Tablespace altered.



.............................................1 offline the tablespce................................

................................

SQL> alter tablespace SYSAUX offline;



2.Os lavel copy the datafile to another location where we want

[oracle@ab ~]$ cp /redoc/sysaux01.dbf /u01/app/oracle/oradata/dborcl/sysaux01.dbf



...................3 alter the datafile...........................


SQL> alter tablespace SYSAUX rename datafile '/redoc/sysaux01.dbf' to '/u01/app/oracle/oradata/dborcl/sysaux01.dbf';

Tablespace altered.


........................4 online the tablespace...........................

SQL> alter tablespace SYSAUX online;

Tablespace altered.
...................................................................................................


.....................new location of the datafile after ........................

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS..................
/u01/app/oracle/oradata/dborcl/users01.dbf

UNDOTBS1
/temptbs_1/undotbs01.dbf

SYSAUX
/u01/app/oracle/oradata/dborcl/sysaux01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/redoa/system01.dbf

EXAMPLE
/u01/app/oracle/oradata/dborcl/example01.dbf
.....................................................................................................


...........................How can rename the datafile of sysem and undotbs...............

.....1...system tablespace cannot be brought offline; shut down if necessary
.....2   Cannot offline the undo tablespace

......3 so shut down the database and then rename the both tablespaces

SQL>  alter tablespace UNDOTBS1 offline;
 alter tablespace UNDOTBS1 offline
*
ERROR at line 1:
ORA-30042: Cannot offline the undo tablespace


SQL> alter tablespace  SYSTEM offline;
alter tablespace  SYSTEM offline
*
ERROR at line 1:
ORA-01541: system tablespace cannot be brought offline; shut down if necessary
..................................................
...1..........................shut the database


SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

.....2....................copy the data file location where we want

[oracle@ab ~]$ cp /redoa/system01.dbf /u01/app/oracle/oradata/dborcl/system01.dbf
[oracle@ab ~]$ cp /temptbs_1/undotbs01.dbf /temptbs_1/dborcl/temp01.dbf

.....3..................startup database in mount
SQL> startup mount      
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size                  2294456 bytes
Variable Size             775949640 bytes
Database Buffers          100663296 bytes
Redo Buffers                6303744 bytes
Database mounted.

....4.......alter the datafile........
SQL> alter database rename file '/redoa/system01.dbf' to '/u01/app/oracle/oradata/dborcl/system01.dbf';

Database altered.

SQL>  alter database rename file '/temptbs_1/undotbs01.dbf' to '/temptbs_1/dborcl/temp01.dbf';

....5.........................open the database................................................................

SQL> alter database open;
Database altered.

...................How can rename the tempfile .......................................................................................

Temporary Tablespace cant not be offline so offline its datafile



SQL>  alter database tempfile '/undotbs_1/temp01.dbf' offline;

Database altered.

........2....From os level copy the tempfile to another location...........


[oracle@ab ~]$ cp /undotbs_1/temp01.dbf /temptbs_1/dborcl/temp02.dbf


.........3............renaming the tempfile.......


SQL> alter database rename file '/undotbs_1/temp01.dbf' to '/temptbs_1/dborcl/temp02.dbf';

Database altered.



.....4.......online the temfile with new location..........

SQL> alter database tempfile '/temptbs_1/dborcl/temp02.dbf' online;

Database altered.



..................................................................................................................

 ..........Now Check the location of all datafiles;

..........................................................

SQL>  select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
USERS
/u01/app/oracle/oradata/dborcl/users01.dbf

UNDOTBS1
/temptbs_1/dborcl/temp01.dbf

SYSAUX
/u01/app/oracle/oradata/dborcl/sysaux01.dbf


TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
SYSTEM
/u01/app/oracle/oradata/dborcl/system01.dbf

EXAMPLE
/u01/app/oracle/oradata/dborcl/example01.dbf

..................................................................................


......................Location 0f temfile...


SQL> select tablespace_name,file_name from dba_temp_files;

TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/temptbs_1/dborcl/temp02.dbf

Multiplexing Redo Log files in three different mount point. Each Redo Log group

Multiplexing Redo log files in three different mount point.Each Redo Log group


Multiplexing of Redolog files
............................................................................................................


Firstly we need to create a mount point where we want to store the redo logs.
mkdir /redoa
mkdir /redob
mkdir /redoc

then gven the permission and owner to the mount points;

chmod -Rf 777 /redoa
chmod -Rf 777 /redob
chmod -Rf 777 /redoc

chown -Rf oracle:oinstall /redoa
chown -Rf oracle:oinstall /redob
chown -Rf oracle:oinstall /redoc

........................connect to database..................



[oracle@ab ~]$ export ORACLE_SID=dborcl
[oracle@ab ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 12 15:17:49 2015

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

Connected to an idle instance.




SQL> startup startup
SQL> startup
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size                  2294456 bytes
Variable Size             620760392 bytes
Database Buffers          255852544 bytes
Redo Buffers                6303744 bytes
Database mounted.
Database opened.

Multiplexing of Redolog files


.................How to see the log membere...............

SQL> column member format a40;                      
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3         ONLINE  /redoc/dborcl/redo03.log                 NO
         0

         2         ONLINE  /redob/dborcl/redo02.log                 NO
         0

         1         ONLINE  /redoa/dborcl/redo1.log                  NO



................................................................................................


......................How to add the log members in loggroup................................

SQL> alter database add logfile member '/redoa/redoa1.log' to group 1;

Database altered.

SQL> alter database add logfile member '/redoa/redoa2.log' to group 1;

Database altered.

SQL> alter database add logfile member '/redoa/redoa3.log' to group 1;

Database altered.

SQL> alter database add logfile member '/redob/redob2.log' to group 2;

Database altered.

           
SQL> alter database add logfile member '/redob/redob1.log' to group 2;

Database altered.

SQL> alter database add logfile member '/redob/redob3.log' to group 2;

Database altered.

SQL> alter database add logfile member '/redoc/redoc1.log' to group 3;

Database altered.

SQL> alter database add logfile member '/redoc/redoc2.log' to group 3;

Database altered.

SQL> alter database add logfile member '/redoc/redoc3.log' to group 3;

Database altered.


................................................................................................


.....................................how to know the members of group........................................


SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3         ONLINE  /redoc/dborcl/redo03.log                 NO
         0

         2         ONLINE  /redob/dborcl/redo02.log                 NO
         0

         1 INVALID ONLINE  /redoa/redoa1.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         1         ONLINE  /redoa/dborcl/redo1.log                  NO
         0

         1 INVALID ONLINE  /redoa/redoa2.log                        NO
         0

         1 INVALID ONLINE  /redoa/redoa3.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         2 INVALID ONLINE  /redob/redob2.log                        NO
         0

         2 INVALID ONLINE  /redob/redob1.log                        NO
         0

         2 INVALID ONLINE  /redob/redob3.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3 INVALID ONLINE  /redoc/redoc1.log                        NO
         0

         3 INVALID ONLINE  /redoc/redoc2.log                        NO
         0

         3 INVALID ONLINE  /redoc/redoc3.log                        NO
         0


.......................................................................................................................


.............................How to know the particulor member of the group...............................................





SQL> select * from v$logfile where group#=1;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         1 INVALID ONLINE  /redoa/redoa1.log                        NO
         0

         1         ONLINE  /redoa/dborcl/redo1.log                  NO
         0

         1 INVALID ONLINE  /redoa/redoa2.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         1 INVALID ONLINE  /redoa/redoa3.log                        NO
         0

........................................................................



SQL> select * from v$logfile where group#=2;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         2         ONLINE  /redob/dborcl/redo02.log                 NO
         0

         2 INVALID ONLINE  /redob/redob2.log                        NO
         0

         2 INVALID ONLINE  /redob/redob1.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         2 INVALID ONLINE  /redob/redob3.log                        NO



SQL>  select * from v$logfile where group#=3;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3         ONLINE  /redoc/dborcl/redo03.log                 NO
         0

         3 INVALID ONLINE  /redoc/redoc1.log                        NO
         0

         3 INVALID ONLINE  /redoc/redoc2.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3 INVALID ONLINE  /redoc/redoc3.log



..........................................................................................



..............................shut down the database....................................

SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options


.............................................................................................

How to renaming the redofiles

............................................................................................


....1...copy redolog file to where we want. in os lavel

[oracle@ab ~]$ cp /redoa/redoa2.log /redob/redoa2.log
[oracle@ab ~]$ cp /redoa/redoa3.log /redoc/redoa3.log
[oracle@ab ~]$ cp /redob/redob2.log /redoa/redob2.log
[oracle@ab ~]$ cp /redob/redob3.log /redoc/redob3.log
[oracle@ab ~]$ cp /redoc/redoc3.log /redoa/redoc3.log
[oracle@ab ~]$ cp /redoc/redoc2.log /redob/redoc2.log
[oracle@ab ~]$ ^C

...2......startup the database in mount....................


[oracle@ab ~]$ export ORACLE_SID=dborcl
[oracle@ab ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Thu Mar 12 15:17:49 2015

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

Connected to an idle instance.




SQL> startup mount
ORACLE instance started.

Total System Global Area  885211136 bytes
Fixed Size                  2294456 bytes
Variable Size             620760392 bytes
Database Buffers          255852544 bytes
Redo Buffers                6303744 bytes
Database mounted.

....3..........renaming the redo logfile


SQL> alter database rename file'/redoa/redoa3.log' to '/redoc/redoa3.log';

Database altered.

SQL> alter database rename file'/redoa/redoa2.log' to '/redob/redoa2.log';

Database altered.

SQL> alter database rename file '/redob/redob2.log' to '/redoa/redob2.log';

Database altered.

SQL> alter database rename file '/redob/redob3.log' to '/redoc/redob3.log';

Database altered.

SQL> alter database rename file '/redoc/redoc3.log' to '/redoa/redoc3.log';

Database altered.

SQL> alter database rename file '/redoc/redoc2.log' to '/redob/redoc2.log';

Database altered.
......4........................open the database...................................

SQL> alter database open
  2  ;

Database altered.

.........................................................................................




............now fire some commands................


*newely aadded logfile status are invalid




SQL> column member format a40;
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3         ONLINE  /redoc/dborcl/redo03.log                 NO
         0

         2         ONLINE  /redob/dborcl/redo02.log                 NO
         0

         1 INVALID ONLINE  /redoa/redoa1.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         1         ONLINE  /redoa/dborcl/redo1.log                  NO
         0

         1 INVALID ONLINE  /redob/redoa2.log                        NO
         0

         1 INVALID ONLINE  /redoc/redoa3.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         2 INVALID ONLINE  /redoa/redob2.log                        NO
         0

         2 INVALID ONLINE  /redob/redob1.log                        NO
         0

         2 INVALID ONLINE  /redoc/redob3.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3 INVALID ONLINE  /redoc/redoc1.log                        NO
         0

         3 INVALID ONLINE  /redob/redoc2.log                        NO
         0

         3 INVALID ONLINE  /redoa/redoc3.log                        NO






.................................................................................................................
.................to online the newely added file fire switch log.......


SQL>alter system switch logfile


System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.


...............................now all added new files status are online................................................

SQL> column member format a40;
SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3         ONLINE  /redoc/dborcl/redo03.log                 NO
         0

         2         ONLINE  /redob/dborcl/redo02.log                 NO
         0

         1         ONLINE  /redoa/redoa1.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         1         ONLINE  /redoa/dborcl/redo1.log                  NO
         0

         1         ONLINE  /redob/redoa2.log                        NO
         0

         1         ONLINE  /redoc/redoa3.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         2         ONLINE  /redoa/redob2.log                        NO
         0

         2         ONLINE  /redob/redob1.log                        NO
         0

         2         ONLINE  /redoc/redob3.log                        NO
         0


    GROUP# STATUS  TYPE    MEMBER                                   IS_
---------- ------- ------- ---------------------------------------- ---
    CON_ID
----------
         3         ONLINE  /redoc/redoc1.log                        NO
         0

         3         ONLINE  /redob/redoc2.log                        NO
         0

         3         ONLINE  /redoa/redoc3.log                        NO
         0


12 rows selected.


...................................................................................................................