Query to find of "Active users" :
SELECT SID, Serial#, UserName, Status, SchemaName, Logon_Time
FROM V$Session
WHERE
Status=‘ACTIVE’ AND
UserName IS NOT NULL ;
SELECT * FROM V$SESSION;
--------------------------------------------------------------------------------
The basic syntax for killing a session is shown below :
SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';
--------------------------------------------------------------------------------
Wednesday, July 10, 2019
Parameters affecting SGA
The following initialization parameters affect the size of the SGA:
• db_block_buffers,
• db_block_size,
• db_cache_size,
• db_keep_cache_size,
• db_recycle_cache_size,
• java_pool_size.
• large_pool_size,
• log_buffer,
• shared_pool_size,
• streams_pool_size,
• db_block_buffers,
• db_block_size,
• db_cache_size,
• db_keep_cache_size,
• db_recycle_cache_size,
• java_pool_size.
• large_pool_size,
• log_buffer,
• shared_pool_size,
• streams_pool_size,
Important Parameters in the database
Important parameters in the database commands :
select name from v$database;
select status from v$instance;
select log_mode from v$database;
show parameter db_recovery_file_dest;
select dest_name,status,destination from V$ARCHIVE_DEST;
show parameter archive_dest
show parameter control_files;
show parameter audit_file_dest;
show parameter spfile;
show parameter pfile;
select name from v$datafile;
select name from v$controlfile;
select name from v$tablespace;
select * from v$log;
show parameter BACKGROUND_DUMP_DEST;
select * from v$version;
Oracle Audit Parameters
Commands to enable auditing :
SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
SQL> shut immediate
SQL> startup
-----------------------------------------------------------------------
Command to enable auditing for a schema :
sql>AUDIT ALL BY student BY ACCESS;
sql>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY student BY ACCESS;
sql>AUDIT EXECUTE PROCEDURE BY student BY ACCESS;
-----------------------------------------------------------------------
Command to check audit trail :
SELECT username, obj_name, sql_text FROM dba_audit_trail
SELECT username,
extended_timestamp,
owner,
obj_name,terminal,
action_name,SQL_TEXT
FROM dba_audit_trail
ORDER BY timestamp;
----------------------------------------------------------------------
Commands to remove audting :
DELETE FROM sys.aud$;
DELETE FROM sys.aud$ WHERE obj$name='';
SELECT FROM sys.aud$
SQL> SHOW PARAMETER AUDIT
SQL> ALTER SYSTEM SET audit_trail=db SCOPE=SPFILE;
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
SQL> ALTER SYSTEM SET audit_trail=db,extended scope=spfile;
SQL> ALTER SYSTEM SET audit_sys_operations=true scope=spfile;
SQL> shut immediate
SQL> startup
-----------------------------------------------------------------------
Command to enable auditing for a schema :
sql>AUDIT ALL BY student BY ACCESS;
sql>AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY student BY ACCESS;
sql>AUDIT EXECUTE PROCEDURE BY student BY ACCESS;
-----------------------------------------------------------------------
Command to check audit trail :
SELECT username, obj_name, sql_text FROM dba_audit_trail
SELECT username,
extended_timestamp,
owner,
obj_name,terminal,
action_name,SQL_TEXT
FROM dba_audit_trail
ORDER BY timestamp;
----------------------------------------------------------------------
Commands to remove audting :
DELETE FROM sys.aud$;
DELETE FROM sys.aud$ WHERE obj$name='';
SELECT FROM sys.aud$
Wednesday, July 3, 2019
Killing a running job in Oracle (scheduled job)
Action :
Find the job:
select * from dba_scheduler_running_jobs;
Kill the job:
exec DBMS_SCHEDULER.STOP_JOB(job_name => ‘owner.job_name’,force => TRUE);
ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles
ORA-01187: cannot read from file because it failed verification tests + ORA-01110 on tempfiles
SQL> select file_name from dba_temp_files;
ERROR:
ORA-01187: cannot read from file because it failed verification tests
ORA-01110: data file 205: '/DATA/database/ifsprod/temp_01.dbf'
Drop the tempfile from the database:
SQL> alter database tempfile '/DATA/database/ifsprod/temp_01.dbf' drop;
Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.
SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;
Following query will help you to alter database for default temporary tablespace. ( i.e. Newly created temp tablespce: ‘TEMP_NEW’ )
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;
RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN
RECOVERY SCENARIO: Complete loss of all database files including SPFILE using RMAN
Database Details
------------------
Database Name=OPSDBA
Machine Name=ITLINUXDEVBLADE07
DBID=1499754868 (select dbid from v$database)
SIMULATING CRASH
------------------------
opsdba:/u01/ORACLE/opsdba>ls -l
total 1948980
drwxr-x--- 2 oracle dba 4096 Feb 12 13:35 arch
-rw-r----- 1 oracle dba 7389184 Feb 12 13:57 control01.ctl
-rw-r----- 1 oracle dba 7389184 Feb 12 13:57 control02.ctl
-rw-r----- 1 oracle dba 7389184 Feb 12 13:57 control03.ctl
-rw-r----- 1 oracle dba 104865792 Feb 12 13:57 drtbs1.dbf
-rw-r----- 1 oracle dba 104865792 Feb 12 13:57 drtbs2.dbf
drwxr-xr-x 2 oracle dba 4096 Feb 12 13:55 recovery
-rw-r----- 1 oracle dba 52429312 Feb 12 13:34 redo01.log
-rw-r----- 1 oracle dba 52429312 Feb 12 13:35 redo02.log
-rw-r----- 1 oracle dba 52429312 Feb 12 13:57 redo03.log
-rw-r----- 1 oracle dba 367009792 Feb 12 13:57 sysaux01.dbf
-rw-r----- 1 oracle dba 513810432 Feb 12 13:57 system01.dbf
-rw-r----- 1 oracle dba 20979712 Jan 30 12:32 temp01.dbf
-rw-r----- 1 oracle dba 5251072 Feb 12 13:57 undonew.dbf
-rw-r----- 1 oracle dba 634396672 Feb 12 13:57 undotbs01.dbf
-rw-r----- 1 oracle dba 31465472 Feb 12 13:57 users01.dbf
-rw-r----- 1 oracle dba 10493952 Feb 12 13:57 users02.dbf
-rw-r----- 1 oracle dba 10493952 Feb 12 13:57 users03.dbf
-rw-r----- 1 oracle dba 10493952 Feb 12 13:57 users04.dbf
-rw-r----- 1 oracle dba 10493952 Feb 12 13:57 users05.dbf
-rw-r----- 1 oracle dba 5251072 Feb 12 13:57 users06.dbf
-rw-r----- 1 oracle dba 5251072 Feb 12 13:57 users07.dbf
opsdba:/u01/ORACLE/opsdba>rm *.dbf
*FILES REMOVED
opsdba:/u01/ORACLE/opsdba>ls -l *.dbf
ls: *.dbf: No such file or directory
opsdba:/opt/oracle/product10gpr2/dbs>mv spfileopsdba.ora spfileopsdba.org
opsdba:/opt/oracle/product10gpr2/dbs>ls -lt spfile* *SPFILE REMOVED
-rw-r----- 1 oracle dba 2560 Feb 12 13:06 spfileopsdba.org
-rw-r----- 1 oracle dba 2560 Jan 23 11:32 spfilerman10d.ora
-rw-r----- 1 oracle dba 4608 Dec 18 09:28 spfileerpmig.ora
-rw-r----- 1 oracle dba 3584 Nov 21 08:22 spfileprimary.ora
-rw-r----- 1 oracle dba 3584 Oct 22 14:32 spfilebozo.ora
-rw-r----- 1 oracle dba 4608 Oct 8 15:24 spfilestreams2.ora
-rw-r----- 1 oracle dba 2560 Oct 1 11:46 spfilevaultdb.ora
THE FOLLOWING STEPS WILL OUTLINE THE RECOVERY PROCESS:
Step 1: RECOVERY OF SPFILE
Create spfile.rcv as:
set dbid= 1499754868
run {
startup nomount force ;
};
opsdba:/u01/ORACLE/opsdba/recovery>rman target / catalog rman10/rman10@rman10p cmdfile=spfile.rcv
Recovery Manager: Release 10.2.0.2.0 - Production on Mon Feb 12 14:02:26 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database (not started)
connected to recovery catalog database
RMAN>
executing command: SET DBID
database name is "OPSDBA" and DBID is 1499754868
Oracle instance started
Total System Global Area 264241152 bytes
Fixed Size 2070416 bytes
Variable Size 113248368 bytes
Database Buffers 142606336 bytes
Redo Buffers 6316032 bytes
Recovery Manager complete.
opsdba:/u01/ORACLE/opsdba/recovery>
Now restore the spfile
set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore spfile ;
release channel ch1 ;
}
Step 2: RESTORE OF CONTROLFILES
Same Steps as spfile with the restore command changed. So the new script is
set dbid=1499754868
run {
allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
restore controlfile ;
release channel ch1 ;
}
Step 3: RESTORE OF DATABASE
Since you have the controlfiles now mount the database
SQL> conn sys as sysdba
Enter password:
Connected.
SQL> alter database mount;
Database altered.
Now get the log sequence number of the database from the catalog database:
select sequence# from rc_backup_redolog where db_name=’OPSDBA’;
RMAN> run {
2> allocate channel ch1 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo.opsdbad.opt)';
3> restore database ;
4> recover database until logseq=6; -- GOT FROM THE ABOVE QUERY
5> release channel ch1 ;
6> }
7>
allocated channel: ch1
channel ch1: sid=156 devtype=SBT_TAPE
channel ch1: Data Protection for Oracle: version 5.2.4.0
Starting restore at 12-FEB-07
channel ch1: starting datafile backupset restore
channel ch1: specifying datafile(s) to restore from backup set
Step 4: alter database open resetlogs;
Linux Basic Commands
1.ls -l for listing the files as well as directories those are kept in
the particular working directory
syntax
[root@mars root]#ls -l
2.ls -la same as 'ls -l'but by this command we can also see the hiden
files.
syntax
[root@mars root]#ls -la
3.ls -li same as 'ls -la' but it will also shows us the inode number of
each and every file
syntax
[root@mars root]#ls -li
4.ls by this command we can see only file name nothing else
syntax
[root@mars root]#ls
5.clear it will clear the screen(short cut ctl+l)
syntax
[root@mars root]#clear
6.exit to end a current session as well current terminal logging
syntax
[root@mars root]exit
7.touch to create a new empty file
syntax
[root@mars root]#touch
8.cd to change the working/present directory
syntax
[root@mars root]#cd /home/apple
where '/home/apple' is the desired directory to be change from
'/root'
9.cat to view the contents of a file and it is also used for creating a
new file with some contents
syntax
[root@mars root]#cat <file name> to view file contents
[root@mars root]#cat > newfilename enter,then you can write something in
the file and then to save the file contents press clt+d then enter
10.mkdir to make a new directory
syntax
[root@mars root]#mkdir newdirname
you can also create a directory at your desired path without
changing your present working directory
syntax
[root@mars root]#mkdir /home/apple/newdirname
11.rm to remove a empty file
syntax
[root@mars root]#rm filename
12.rmdir to remove a empty directory
syntax
[root@mars root]#rmdir directoryname
13.rm [-i/-r/-f] to remove a directory with its subdirectories as well as its
files that is to remove a directory which already contains some files in it
syntax
[root@mars root]#rm -i directory/filename
-i stands for interactively
-r stands for recursively
-f stands for forcefully
14.cp to copy something in a destination file or directory
syntax
[root@mars root]#cp sourcepath destinationpath
example: [root@mars root]#cp /home/apple/webmin.rpm /root/abcd
in this example the webmin.rpm file will be copied in
/root/abcd directory
15.mv to move one file or directory from one place to another place, it
is also used for renaming adirectory or file
syntax
[root@mars root]#mv source destination
[root@mars root]#mv oldfilename newfilename [to change the file name]
16.man to view the mannual page of commands for syntax
syntax
[root@mars root]#man commandname
17.info to view the information about any command
syntax
[root@mars root]#mkdir info
18.--help to view the help doccuments of a command
syntax
[root@mars root]#commandname --help
19.dir to view the subdirectories and filesn under the directory
syntax
[root@mars root]#dir
20.su - to become a super user
syntax
[apple@mars apple]$su -
output wil be
[root@mars root#]
21.who by this command you can see the user name and their ip addresses
who have loged in on your server
syntax
[root@mars root]#who
22.whoami this command shows your current logged in terminal user name
syntax
[root@mars root]#whoami
23.who am i this command shows you the logged in terminal number and user
name and more detailed information
syntax
[root@mars root]#who am i
24.pwd to view the present working directory
syntax
[root@mars root]#pwd
25.rpm -ivh to intall a rpm package
syntax
[root@mars root]#rpm -ivh packagename.rpm
rpm stands for 'redhat package manager'
-i stands for install
-v stands for verbose mode
-h stands for with hash sign(#)
26.rpm -q to querry about any rpm package
syntax
[root@mars root]#rpm -q packagename
27.rpm -e to uninstall a rpm package
synatx
[root@mars root]#rpm -e package
28.find / -name to find any file or directory in linux file system
syntax
[root@mars root]#find / -name filename
29.su username to switch from one user to another users home directory
syntax
[root@mars root]#su apple
output will be
[apple@mars root]#cd
[apple@mars apple]#
30.su - username to switch from one user to another user users home
directory directly
syntax
[root@mars root]#su - apple
31.useradd to create a new user
synatx
[root@mars root]#useradd username
32.passwd to give a password of a user
syntax
[root@mars root]#passwd tarun
output will be
give a password for user tarun:(here you have to type a password for tarun user)
confirm password:(again type the same password)
33.userdel to remove a user from linux
syntax
[root@mars root]#userdel tarun
34.groupadd to add a new group
syntax
[root@mars root]#groupadd groupname
35.gruopdel to delete a group
syntax
[root@mars root]#groupdel groupname
36.chown to change the ownership of a file or directory
syntax
[root@mars root]#chown ownername filename
example:
[root@mars /]#ls -l
output
drwxrw-rw- 2 root root 4096 Mar 11 12:03 abcd
(permission) (own) (group own)(size) (name)
[root@mars root]#chown tarun /abcd
in this example /abcd directory owner will be change to tarun user
effect
[root@mars /]#ls -l
drwxrw-rw- 2 tarun root 4096 Mar 11 12:03 abcd
37.chgrp to change the group ownership of a file or directory
syntax
[root@nettec root]#chgrp newgroupownername filename
example
[root@mars /]#ls -l
drwxrw-rw- 2 tarun root 4096 Mar 11 12:03 abcd
[root@mars root]#chgrp tarun /abcdeffect
[root@mars /]#ls -l
drwxrw-rw- 2 tarun tarun 4096 Mar 11 12:03 abcd
38.chmod to change the permission of a file or directory
drwxrw-rw- 2 root root 4096 Mar 11 12:03 abcd
(permission) (ownr) (grpownr) (size) (name) IN
OCTAL VALUE
d stands for directiry READ=4
r stands for read only permission WRITE=2
w stands for write only permission EXECUTE=1
x stands for execute only permission
drwxrw-rw- FIRST OCTET FOR DENOTING THE DIRECTORY OR FILE OR LINK FILE ETC.
SECOND THREE OCTET FOR USER OR OWNER PERMISSION (rwx OR 7 IN
OCTAL VALUE)
THIRD THREE OCTET FOR GROUP PERMISSION (rw- OR 6 IN OCTAL VALUE)
FORTH THREE OCTET FOR OTHERS PERMISSION (rw- OR 6 IN OCTAL VALUE)
Syntax
[root@nettec root]#chmod value fileordirectoryname
example
[root@mars /]#ls -l
drwxrw-rw- 2 tarun root 4096 Mar 11 12:03 abcd
[root@mars /]#chmod 402 /abcd
[root@mars /]#ls -l
drw-----w- 2 tarun tarun 4096 Mar 11 12:03 abcd
40.usermod to modify the user profile
synatx
[root@mars root]#usermod -parameter groupname username
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
When you start or stop you oracle service in Unix/Linux system and you get the prompt of ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener error.
[oracle@linux1bin]$ echo $ORACLE_SID
qptest
[oracle@linux1 bin]$ . dbshut
ORACLE_HOME_LISTNER is not SET, unable to auto-stop Oracle Net Listener
Usage: -bash ORACLE_HOME
Then you need to edit the “dbstart” & “dbshut” file, the should be located at $ORACLE_HOME\bin
Go through the file and fine line
ORACLE_HOME_LISTNER=$1
and change to
ORACLE_HOME_LISTNER=$ORACLE_HOME
DataPump Import Errors ORA-39001 ORA-39000 ORA-31640 ORA-27037
DataPump Import (IMPDP) Fails With Errors ORA-39001 ORA-39000 ORA-31640 ORA-27037
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.
ERROR SYMPTOMS:
DataPump import fails with the following errors:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/bkups/exports/EXPORT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
The parameters used are:
userid=system/
DIRECTORY=my_dir
DUMPFILE=EXPORT.dmp
LOGFILE=my_logdir:EXPORT.log
CHANGES
DataPump export with parameters:
userid=username/
DIRECTORY=my_dir
DUMPFILE=EXPORT.DMP
LOGFILE=EXPORT.log
content=metadata_only
VERSION=10.2.0
was successful:
Master table "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXPORT.SYS_EXPORT_SCHEMA_01 is:
/spare/clone/EXPORT.DMP
Job "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:41:12
CAUSE:
At first glance, this appears to be an exact match to the note 784566.1 How to Prevent ORA-39000 ORA-31640 ORA-27037 Errors When
Performing Data Pump Export/Import
If you have already read that note, tried the solution and are still getting the errors, then the problem may be with the actual
export.dmp file.
In this case, as you can see from the export par file and import par file the name of the export dump is not the same:
Export: DUMPFILE=EXPORT.DMP
Import: DUMPFILE=EXPORT.dmp
SOLUTION:
Once the import parameter was changed to DUMPFILE=EXPORT.DMP the import completes successfully.
Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.4 [Release 10.1 to 11.2]
Information in this document applies to any platform.
ERROR SYMPTOMS:
DataPump import fails with the following errors:
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/u01/bkups/exports/EXPORT.dmp" for read
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
The parameters used are:
userid=system/
DIRECTORY=my_dir
DUMPFILE=EXPORT.dmp
LOGFILE=my_logdir:EXPORT.log
CHANGES
DataPump export with parameters:
userid=username/
DIRECTORY=my_dir
DUMPFILE=EXPORT.DMP
LOGFILE=EXPORT.log
content=metadata_only
VERSION=10.2.0
was successful:
Master table "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for EXPORT.SYS_EXPORT_SCHEMA_01 is:
/spare/clone/EXPORT.DMP
Job "EXPORT"."SYS_EXPORT_SCHEMA_01" successfully completed at 20:41:12
CAUSE:
At first glance, this appears to be an exact match to the note 784566.1 How to Prevent ORA-39000 ORA-31640 ORA-27037 Errors When
Performing Data Pump Export/Import
If you have already read that note, tried the solution and are still getting the errors, then the problem may be with the actual
export.dmp file.
In this case, as you can see from the export par file and import par file the name of the export dump is not the same:
Export: DUMPFILE=EXPORT.DMP
Import: DUMPFILE=EXPORT.dmp
SOLUTION:
Once the import parameter was changed to DUMPFILE=EXPORT.DMP the import completes successfully.
RMAN Recovery Catalog
First create the RCATDB Database
Now after creating database create the tablespace and the user required for RECOVERY CATALOG :
CREATE TABLESPACE "TBRCAT"
LOGGING
DATAFILE '/u01/app/oracle/oradata/RCATDB/RCAT001.ora' SIZE 30M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 3M SEGMENT SPACE
MANAGEMENT AUTO;
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/RCATDB/RCAT001.ora' AUTOEXTEND
ON NEXT 3M;
ALTER DATABASE
DATAFILE '/u01/app/oracle/oradata/RCATDB/RCAT001.ora' AUTOEXTEND
ON MAXSIZE UNLIMITED;
CREATE USER RCAT PROFILE DEFAULT
IDENTIFIED BY RCAT DEFAULT TABLESPACE TBRCAT
ACCOUNT UNLOCK;
GRANT CREATE SEQUENCE TO RCAT;
GRANT CREATE SESSION TO RCAT;
GRANT CREATE SYNONYM TO RCAT;
GRANT CREATE TABLE TO RCAT;
GRANT CREATE VIEW TO RCAT;
GRANT IMP_FULL_DATABASE TO RCAT;
GRANT EXP_FULL_DATABASE TO RCAT;
GRANT CONNECT TO RCAT;
GRANT RESOURCE TO RCAT;
GRANT recovery_catalog_owner TO RCAT;
- Create Recovery Catalog
$rman catalog RCAT/RCAT
RMAN> create catalog;
$rman catalog RCAT/RCAT
RMAN> create catalog;
Exit;
rman target / catalog RCAT/RCAT@RCATDB
REGISTER DATABASE;
Now - Register the databases which will use this database as recovery catalog
Login to orcl and issue this command :
rman target sys/oracle@orcl catalog RCAT/RCAT@RCATDB;
Save this database :
RMAN> register database;
- Verify that the registration was successful by running REPORT SCHEMA
RMAN> report schema;
Check the oratab file and name of database ,it should have YES Entry in the end :
Cat /etc/oratab
oratst:/u01/app/oracle/product/11.2.0/db_1:Y
My RMAN Configuration :
[oracle@OEL-VM-109 ~]$ rman target sys/oracle@orcl catalog RCAT/RCAT@RCATDB;
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 2 15:38:37 2017
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=146129700)
connected to recovery catalog database
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 690 SYSTEM YES /u01/app/oracle/oradata/orcl/system01.dbf
2 490 SYSAUX NO /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 30 UNDOTBS1 YES /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS NO /u01/app/oracle/oradata/orcl/users01.dbf
5 147 TBEHMS NO /u01/app/oracle/oradata/orcl/EHMS001.ora
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN> show all;
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u99/rman_backup/cf/cf_%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/u99/rman_backup/db_bkp_%t_%U_%T_%p';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_orcl.f'; # default
NOTE : Before taking backup please verify proper permissions given to that rman backup folder that is used in configuration , in my case it’s /u99/rman_backup/cf
Since /u01, /u18 and /u99 are still owned by root, let us give the permission to oracle.
#For oracle user
mkdir -p /u01/app/oracle
chown -R oracle:oinstall /u01 /u01/app/oracle /u18 /u99 /u99/rman_backup /u99/rman_backup_cf
2. Grant proper permissions on directories to oracle user
chmod -R 775 /u01 /u18 /u99 /u99/rman_backup /u99/rman_backup/cf
Backup incremental level 0 database plus archivelog;
Subscribe to:
Posts (Atom)