RMAN was always puzzle for me until I implemented this on my test env. Since I never worked and used RMAN in my 7 year career as APPS DBA, sometime ago I decided to do some test demo to understand the RMAN backup and recovery tool. After I completed and tested all backup scenerio now I feel its really simple to use and implement, I would like to share some of RMAN test cases which I implemented on my server.
We need to create a catalog first on RMAN Database before start taking backup of target instance, perform the following steps
Step 1) Make a tnsentry of RMAN instance into the target instance
Step 2) Create RMAND tablespace into RMAN Source Instance
CREATE TABLESPACE rmand DATAFILE '/d001/oracle/rmandO1.dbf' SIZE 1024M:
Create RMAN Schema
SQL> CREATE USER rman IDENTIFIED BY rman DEFAULT TABLESPACE rmand TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON rmand;
Grant the following roles to rman user
SQL> GRANT create session TO rman;
SQL> GRANT resource TO rman;
SQL> GRANT recovery_catalog_owner TO rman;
SQL> GRANT execute ON dbms_stats TO rman;
Step 3)
Connect to rman instance and target instance together from target instance server
$ rman target / catalog rman/rman@gepsmv
RMAN> create catalog tablespace rmand;
Note : This will create the rman catalog
RMAN> register database;
Note : Register the target database
You are ready to take a backup from this stage
If your database is in no archive mode, use the following syntax to take cold backup
Test 1)
RMAN> STARTUP FORCE DBA;
RMAN> SHUTDOWN IMMEDIATE;
RMAN> STARTUP MOUNT;
RMAN> run {
allocate channel c1 type disk;
backup database format '/d001/backup/db_t%t_s%s_p%p' FILESPERSET 4;
release channel c1;
}
Note : backup location here is /d001/backup, fileperset 4 means 4 datafiles make one backup set
RMAN> list backupset of database;
Test 2) Take a backup when archive is enable
you can turn on the archive mode using following steps
SQL> shutdown immediate;
startup mount exclusive;
alter database archivelog;
alter database open;
alter system switch logfile;
make the entry in the Init.ora of the following parameter
log_archive_dest = '/d001/arch'
log_archive_start = true
Backup in Archive Mode
Full Backup ->
rman target / catalog rman/rman@gepsmv
run {
allocate channel c1 type disk;
backup database format '/d001/backup/db_t%t_s%s_p%p' FILESPERSET 4;
release channel c1;
}
Archive Backup ->
run {
allocate channel dev1 type disk;
backup
format '/d001/backup/db_t%t_s%s_p%p'
(archivelog all);
release channel dev1;
}
Need to start recovery after deleting datafile mvd02.dbf from running database
Recovery test1 ->
mv mvd02.dbf mvd02.dbf.old
rman target / catalog rman/rman@gepsmv
RUN
{
allocate channel dev1 type disk;
SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';
restore datafile 7;
recover datafile 7;
SQL 'ALTER DATABASE DATAFILE 7 ONLINE';
release channel dev1;
}
Note : Datafile 7 is file number of mvd02.dbf, you can check it from v$datafile
Recovery Test 2 ->
Tablespace Recovery (Assuming datafiles belong to mvd tablespace has been damaged)
move the files mvd01.dbf & mvd02.dbf while db is running.
RUN
{
allocate channel dev1 type disk;
SQL 'ALTER TABLESPACE MVD OFFLINE immediate';
RESTORE TABLESPACE MVD;
recover TABLESPACE MVD;
SQL 'ALTER TABLESPACE MVD ONLINE';
release channel dev1;
}
Recovery Test3 ->
System File Recovery
rman target / catalog rman/rman@gepsmv
RUN
{
allocate channel dev1 type disk;
SQL 'ALTER DATABASE DATAFILE 1 OFFLINE';
restore datafile 1;
recover datafile 1;
SQL 'ALTER DATABASE DATAFILE 1 ONLINE';
release channel dev1;
Recover Test 4 ->
Complete Restore
rman target / catalog rman/rman@gepsmv
run {
allocate channel dev1 type disk;
restore controlfile to '/d001/oracle/gepsmv/oradata' ;
replicate controlfile from '/d001/oracle/gepsmv/oradata' ;
restore database;
sql "alter database mount";
recover database;
sql "alter database open resetlogs";
release channel dev1;
}
Test 3) Archive Backup with Increment Level
rman target / catalog rman/rman@gepsmv
Increment Level 0 (Base level which is full backup)
# - Take an incremental level 0 backup of the database. A level 0 backup is
# a complete backup of the entire file which can be used as the basis
# for a subsequent incremental backup.
# - Backup all archivelogs that have not already been backed up.
rman target / catalog rman/rman@gepsmv
run {
allocate channel dev1 type disk;
BACKUP INCREMENTAL LEVEL 0 DATABASE FILESPERSET 4 format '/d001/backup/db_t%t_s%s_p%p';
BACKUP ARCHIVELOG ALL;
release channel dev1;
}
rman target / catalog rman/rman@gepsm
run {
allocate channel dev1 type disk;
BACKUP INCREMENTAL LEVEL 1 DATABASE format '/d001/backup/db_t%t_s%s_p%p';
BACKUP ARCHIVELOG ALL;
release channel dev1;
}
Note : Incremental Level 1 is the only changed data after last full backup of incrmental 0
Wednesday, August 27, 2008
Tuesday, August 26, 2008
ORA-00932: inconsistent datatypes error
This seems to be Oracle 10g Bug , as per this bug number "4381035" if you fetch less field then what is contained in the SELECT list for CURSOR produces this error.
e.g.
PL/SQL used
.
CREATE OR REPLACE procedure TestCursor is
TYPE t_CurType is REF CURSOR;
c1 t_curtype;
v_SQLString VARCHAR2(50);
v_Deptno Number;
v_Dname VARCHAR2(20);
v_Loc VARCHAR2(10);
begin
v_SQLstring := 'select * from dept';
open c1 for v_SQLstring;
dbms_output.put_line('Before Fetch');
fetch c1 into v_Deptno;
dbms_output.put_line('v_Deptno = 'v_Deptno);
close c1;
end;
In the above example the Cursor uses "SELECT * FROM DEPT", but the FETCH command only fetches one of the fields when the "SELECT *..." actually returns 3 fields.
Diagnostic Analysis:
=====================
8i Database - Works
9i Database - Works
10g Database - Fails with the reported error
.
If the FETCH is modified to fetch all the columns (FETCH c1 into v_Deptno,
v_Dname, V_Loc;) then it works in 10g Database.
Workaround:
=============
Ensure the number of fields being Fetched matches the number of fields within
the Select list.
This is not a feasible workaround for the customer as they have too many
procedures and each procedure make a number of references to tables where a
"SELECT *..." is being used but only Fetching some of the fields.
Solution
To implement the solution, please execute the following steps:1. Provide the same number of define variables as columns in the SELECT statement.or2. Apply the patchset 10.2.0.4 and use the following event: alter session set events='10946 trace name context forever, level 4096';
or
3. Upgrade to 11.1 and use the following event:: alter session set events='10946 trace name context forever, level 4096';
e.g.
PL/SQL used
.
CREATE OR REPLACE procedure TestCursor is
TYPE t_CurType is REF CURSOR;
c1 t_curtype;
v_SQLString VARCHAR2(50);
v_Deptno Number;
v_Dname VARCHAR2(20);
v_Loc VARCHAR2(10);
begin
v_SQLstring := 'select * from dept';
open c1 for v_SQLstring;
dbms_output.put_line('Before Fetch');
fetch c1 into v_Deptno;
dbms_output.put_line('v_Deptno = 'v_Deptno);
close c1;
end;
In the above example the Cursor uses "SELECT * FROM DEPT", but the FETCH command only fetches one of the fields when the "SELECT *..." actually returns 3 fields.
Diagnostic Analysis:
=====================
8i Database - Works
9i Database - Works
10g Database - Fails with the reported error
.
If the FETCH is modified to fetch all the columns (FETCH c1 into v_Deptno,
v_Dname, V_Loc;) then it works in 10g Database.
Workaround:
=============
Ensure the number of fields being Fetched matches the number of fields within
the Select list.
This is not a feasible workaround for the customer as they have too many
procedures and each procedure make a number of references to tables where a
"SELECT *..." is being used but only Fetching some of the fields.
Solution
To implement the solution, please execute the following steps:1. Provide the same number of define variables as columns in the SELECT statement.or2. Apply the patchset 10.2.0.4 and use the following event: alter session set events='10946 trace name context forever, level 4096';
or
3. Upgrade to 11.1 and use the following event:: alter session set events='10946 trace name context forever, level 4096';
AppsLogin Servlet Work
How does the AppsLogin servlet work?
The AppsLogin servlet supersedes older Release 11i login mechanisms and certain product-specific login pages. The AppsLogin servlet detects the login mode by reading the "Applications SSO Type" profile option for the current Oracle E-Business Suite Release 11i instance and transparently redirects to the appropriate login page.
Scenario 1: AppsLogin running on a Release 11i instance configured for Self-Service Web Applications (SSWA) will authenticate the user via the AppsLocalLogin.jsp page, and then redirect to the configured home page specified in the Self Service Personal Home Page mode profile option.
Scenario 2: AppsLogin running on a Release 11i instance configured for Single Sign-On will redirect to the Single Sign-On 10g login page for user authentication, and then redirect to the configured home page specified in the Self Service Personal Home Page mode profile option.
Scenario 3: AppsLogin running on a Release 11i instance configured for Single Sign-On and Portal will redirect to Single Sign-On 10g login page for user authentication, and then redirect to the default Portal home page.
The AppsLogin servlet may be installed for Release 11i environments running 9iAS 1.0.2.2.2 , or Release 11i environments integrated with Oracle Application Server 10g and Single Sign-On 10g.
The AppsLogin servlet supersedes older Release 11i login mechanisms and certain product-specific login pages. The AppsLogin servlet detects the login mode by reading the "Applications SSO Type" profile option for the current Oracle E-Business Suite Release 11i instance and transparently redirects to the appropriate login page.
Scenario 1: AppsLogin running on a Release 11i instance configured for Self-Service Web Applications (SSWA) will authenticate the user via the AppsLocalLogin.jsp page, and then redirect to the configured home page specified in the Self Service Personal Home Page mode profile option.
Scenario 2: AppsLogin running on a Release 11i instance configured for Single Sign-On will redirect to the Single Sign-On 10g login page for user authentication, and then redirect to the configured home page specified in the Self Service Personal Home Page mode profile option.
Scenario 3: AppsLogin running on a Release 11i instance configured for Single Sign-On and Portal will redirect to Single Sign-On 10g login page for user authentication, and then redirect to the default Portal home page.
The AppsLogin servlet may be installed for Release 11i environments running 9iAS 1.0.2.2.2 , or Release 11i environments integrated with Oracle Application Server 10g and Single Sign-On 10g.
SFTP Vs FTP
What is SFTP?
From the unix man page:
"sftp is an interactive file transfer program, similar to ftp, which performs all operations over an encrypted ssh transport".
The Abbreviation SFTP nowadays invariably means Secure File Transfer Protocol, however there is another protocol with the same abbreviation (Simple File Transfer Protocol). All occurrences of the term SFTP on this website (www.radinks.com) refers to Secure File Transfer Protocol
Why should I use Secure FTP instead of FTP?
Why is SFTP better than FTP?
Despite the similarity in name the two protocols are completely different.
In FTP all data is passed back and forth between the client and server without the use of encryption. This makes it possible for an eavesdropper to listen in and retrieve your confidential information including login details. With SFTP all the data is encrypted before it is sent across the network.
SFTP comes with a cost to speed of transfer, as the encryption slows the whole thing down, but that's the price of security !
Operationally the applications that support SFTP function like those that use FTP.
From the unix man page:
"sftp is an interactive file transfer program, similar to ftp, which performs all operations over an encrypted ssh transport".
The Abbreviation SFTP nowadays invariably means Secure File Transfer Protocol, however there is another protocol with the same abbreviation (Simple File Transfer Protocol). All occurrences of the term SFTP on this website (www.radinks.com) refers to Secure File Transfer Protocol
Why should I use Secure FTP instead of FTP?
Why is SFTP better than FTP?
Despite the similarity in name the two protocols are completely different.
In FTP all data is passed back and forth between the client and server without the use of encryption. This makes it possible for an eavesdropper to listen in and retrieve your confidential information including login details. With SFTP all the data is encrypted before it is sent across the network.
SFTP comes with a cost to speed of transfer, as the encryption slows the whole thing down, but that's the price of security !
Operationally the applications that support SFTP function like those that use FTP.
11.5.10.2 certification with Solaris 10
E-Business Suite 11.5.10.2 is certified with Solaris 10 as per Oracle:
Certify - Certification Matrix: E-Business Suite 11i and Earlier on Solaris Operating System (SPARC)
Application Tier Certifications
OS
Product
Server
Status
Addtl. Info.
Components
Other
Issues
9
11.5.10.2 (11i)
8.0.6
Certified
Yes
Yes
Yes
None
8
11.5.10.2 (11i)
8.0.6
Certified
Yes
Yes
Yes
None
10
11.5.10.2 (11i)
8.0.6
Certified
Yes
Yes
Yes
None
Database Tier Certifications
OS
Product
Server
Status
Addtl. Info.
Components
Other
Issues
9
11.5.10.2 (11i)
10gR2 64-bit
Certified
Yes
Yes
Yes
None
8
11.5.10.2 (11i)
10gR2 64-bit
Certified
Yes
Yes
Yes
None
10
11.5.10.2 (11i)
10gR2 64-bit
Certified
Yes
Yes
Yes
None
9
11.5.10.2 (11i)
10g 64-bit
Certified
Yes
Yes
Yes
Yes
8
11.5.10.2 (11i)
10g 64-bit
Certified
Yes
Yes
Yes
Yes
10
11.5.10.2 (11i)
10g 64-bit
Certified
Yes
Yes
Yes
Yes
Certify - Certification Matrix: E-Business Suite 11i and Earlier on Solaris Operating System (SPARC)
Application Tier Certifications
OS
Product
Server
Status
Addtl. Info.
Components
Other
Issues
9
11.5.10.2 (11i)
8.0.6
Certified
Yes
Yes
Yes
None
8
11.5.10.2 (11i)
8.0.6
Certified
Yes
Yes
Yes
None
10
11.5.10.2 (11i)
8.0.6
Certified
Yes
Yes
Yes
None
Database Tier Certifications
OS
Product
Server
Status
Addtl. Info.
Components
Other
Issues
9
11.5.10.2 (11i)
10gR2 64-bit
Certified
Yes
Yes
Yes
None
8
11.5.10.2 (11i)
10gR2 64-bit
Certified
Yes
Yes
Yes
None
10
11.5.10.2 (11i)
10gR2 64-bit
Certified
Yes
Yes
Yes
None
9
11.5.10.2 (11i)
10g 64-bit
Certified
Yes
Yes
Yes
Yes
8
11.5.10.2 (11i)
10g 64-bit
Certified
Yes
Yes
Yes
Yes
10
11.5.10.2 (11i)
10g 64-bit
Certified
Yes
Yes
Yes
Yes
Friday, August 8, 2008
ORA-01092: ORACLE instance terminated.
Issue : ORA-01092: ORACLE instance terminated. Disconnection forced errors will occur and the error ORA-39700: database must be opened with UPGRADE option will be in the alert log.
Resolution : Run the following steps to fix the above issue
Shut down the database:
SQL> SHUTDOWN
Start the listener
$ lsnrctl start
$ sqlplus "/ as sysdba"
sql>start upgrade
For some reason if its not starting with upgrade option, the instance get crashed due to this issue
Process LGWR died, see its trace fileUSER: terminating instance due to error 443Instance terminated by USER, pid = 14136 or BW3 started with pid=8, OS id=15094Thu Aug 7 21:52:44 2008Process startup failed, error stack:Thu Aug 7 21:52:45 2008
Kill all the existing runing process for that oracle unix account and try starting up the database with upgrade option
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.SQL> @?/rdbms/admin/utlrp.sql
For more detail you can also check readme of 5337014. I faced this issue recently and it got fix with the above steps. I hope this will help you.
Resolution : Run the following steps to fix the above issue
Shut down the database:
SQL> SHUTDOWN
Start the listener
$ lsnrctl start
$ sqlplus "/ as sysdba"
sql>start upgrade
For some reason if its not starting with upgrade option, the instance get crashed due to this issue
Process LGWR died, see its trace fileUSER: terminating instance due to error 443Instance terminated by USER, pid = 14136 or BW3 started with pid=8, OS id=15094Thu Aug 7 21:52:44 2008Process startup failed, error stack:Thu Aug 7 21:52:45 2008
Kill all the existing runing process for that oracle unix account and try starting up the database with upgrade option
SQL> STARTUP UPGRADE
SQL> SPOOL patch.log
SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time. This step is optional but recommended.SQL> @?/rdbms/admin/utlrp.sql
For more detail you can also check readme of 5337014. I faced this issue recently and it got fix with the above steps. I hope this will help you.
About this blog
Hello to all.
I am starting this blog to share my knowledge and experiences with Oracle Apps and 170 Systems.
I am starting this blog to share my knowledge and experiences with Oracle Apps and 170 Systems.
Subscribe to:
Posts (Atom)