TEMPFILE ERROR ON ORACLE 10r2/LINUX
Hi folks. Here is one of the errors you can encounter on your job as a Database Java1istrator.
I was running a full export of a database (software) and I ran into this error. I hope sharing this error and the solution will help someone out there.
Environment
Linux 4.9
Oracle 10.2
Database name: software
Error
Linux Error: 2: No such file or directory
Additional information: 3
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6234
----- PL/SQL Call Stack -----
object line object
handle number name
0x34ec54e8 14916 package body SYS.KUPW$WORKER
0x34ec54e8 6293 package body SYS.KUPW$WORKER
0x34ec54e8 2339 package body SYS.KUPW$WORKER
0x34ec54e8 6854 package body SYS.KUPW$WORKER
0x34ec54e8 1259 package body SYS.KUPW$WORKER
0x5803b23c 2 anonymous block
Job "SYSTEM"."SYS_EXPORT_FULL_06" stopped due to fatal error at 18:54:47
[oracle@software ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Dec 26 18:58:12 2012
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Release 10.2.0.3.0 - Production
SQL> desc v$tempfile
Name Null? Type
----------------------------------------- -------- ----------------------------
FILE# NUMBER
CREATION_CHANGE# NUMBER
CREATION_TIME DATE
TS# NUMBER
RFILE# NUMBER
STATUS VARCHAR2(7)
ENABLED VARCHAR2(10)
BYTES NUMBER
BLOCKS NUMBER
CREATE_BYTES NUMBER
BLOCK_SIZE NUMBER
NAME VARCHAR2(513)
SQL> select name, status from v$tempfile;
NAME STATUS
-------------------------------------
/u10/oradata/software/temp01.dbf ONLINE
/u10/oradata/software/java1temp01.dbf ONLINE
/u10/oradata/software/java2temp01.dbf ONLINE
/u10/oradata/software/java3temp01.dbf ONLINE
4 rows selected.
Here comes the errors . From here you have a clue where the problem is and what you need to do to resolve this issue
SQL> desc dba_tempfile;
ERROR:
ORA-04043: object dba_tempfile does not exist
SQL> desc dba_tempfiles;
ERROR:
ORA-04043: object dba_tempfiles does not exist
SQL> select FILE_NAME, TABLESPACE_NAME from dba_temp_files;
ERROR:
ORA-01116: error in opening database file 204
ORA-01110: data file 204: '/u10/oradata/software/java301.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3
no rows selected
Solution
From this error, you can tell that there is a problem with a tempfle named '/u10/oradata/software/java301.dbf'. What we need to do is figure out how to get that tempfile online and alter the user presently using this tempfile to use the default tempfile from TEMP tablespace.
Crosscheck the default and temporary tablespaces in the database. This will give you an idea of where to go to get the issue resolved
SQL> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS
JAVA1
JAVA1TEMP
JAVA2
JAVA2TEMP
JAVA3
JAVA3TEMP
Check for the user(s) presently using the JAVA3TEMP that has issues
SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';
USERNAME
------------------------------
JAVA3
Change the temporary tablespace of user JAVA3 from JAVA3TEMP to TEMP. This will ensure that the user can use the default temporary tablespace
SQL> alter user JAVA3
2 temporary tablespace TEMP;
User altered.
Check to ensure that the user is properly altered to use TEMP instead of JAVA3TEMP
SQL> select username from dba_users
2 where temporary_tablespace='JAVA3TEMP';
no rows selected
Take the problematic tempfile offline
SQL> alter database tempfile '/u10/oradata/software/java3temp01.dbf' offline;
Database altered.
Please drop a comment if you have any questions. Thanks and watch this space for more posts!
Friday, March 8, 2013
Friday, February 22, 2013
Flash Recovery Area
Flash recovery area or Fast recovery area is a space designed by Oracle for keeping backup files. It is a centralized location where RMAN writes backup file (backup sets) to. Here is what you will see in the FRA:
1. A directory containing backup sets
2. A directory containing controlfiles
3. A directory containing archivelog files
To check the size of your recovery area, issue this command:
select name, space_limit, space_used from v$recovery_file_dest
SPACE_USED/1024/1024/1024 SPACE_LIMIT/1024/1024/1024
------------------------- --------------------------
0 3.8203125
or
select space_used/1024/1024/1024 from v$recovery_file_dest;
To check the usage of FRA, issue this command:
select * from v$flash_recovery_area_usage;
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
CONTROL FILE 0 0
0
REDO LOG 0 0
0
ARCHIVED LOG 0 0
0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
BACKUP PIECE 0 0
0
IMAGE COPY 0 0
0
FLASHBACK LOG 0 0
0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
FOREIGN ARCHIVED LOG 0 0
0
increasing size of FRA
alter system set db_recovery_file_dest_size=10G;
NOTE: before increasing your FRA, please verify your server has a space to accommodate the new 10G you want to create.
on Linux/Unix, issue this command:
df -h
SIZE of database
You may need to check the size of your database in order to make a report or for capacity planning. Here are some of the commands you can use.
Segments:
select sum(BYTES/1024/1024/1024) from dba_segments;
Datafile:
select sum(BYTES/1024/1024/1024) from dba_data_files;
Tablespace:
select sum(BYTES/1024/1024/1024) from dba_free_space where TABLESPACE_NAME='KUNLE';
or
select sum(BYTES/1024/1024/1024) from v$datafile where TABLESPACE_NAME='KUNLE';
1. A directory containing backup sets
2. A directory containing controlfiles
3. A directory containing archivelog files
To check the size of your recovery area, issue this command:
select name, space_limit, space_used from v$recovery_file_dest
SPACE_USED/1024/1024/1024 SPACE_LIMIT/1024/1024/1024
------------------------- --------------------------
0 3.8203125
or
select space_used/1024/1024/1024 from v$recovery_file_dest;
To check the usage of FRA, issue this command:
select * from v$flash_recovery_area_usage;
SQL> select * from v$flash_recovery_area_usage;
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
CONTROL FILE 0 0
0
REDO LOG 0 0
0
ARCHIVED LOG 0 0
0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
BACKUP PIECE 0 0
0
IMAGE COPY 0 0
0
FLASHBACK LOG 0 0
0
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE
-------------------- ------------------ -------------------------
FOREIGN ARCHIVED LOG 0 0
0
increasing size of FRA
alter system set db_recovery_file_dest_size=10G;
NOTE: before increasing your FRA, please verify your server has a space to accommodate the new 10G you want to create.
on Linux/Unix, issue this command:
df -h
SIZE of database
You may need to check the size of your database in order to make a report or for capacity planning. Here are some of the commands you can use.
Segments:
select sum(BYTES/1024/1024/1024) from dba_segments;
Datafile:
select sum(BYTES/1024/1024/1024) from dba_data_files;
Tablespace:
select sum(BYTES/1024/1024/1024) from dba_free_space where TABLESPACE_NAME='KUNLE';
or
select sum(BYTES/1024/1024/1024) from v$datafile where TABLESPACE_NAME='KUNLE';
Thursday, February 14, 2013
Oracle user creation and privileges in 10g/11g
Oracle user creation and privileges in 10g/11g
Creating a user and setting privileges for them are common everyday tasks of Oracle DBAs and Developers. You can either do them through the commandline or use client tools, eg. SQL Developer.
creating a default tablespace for the user
Default tablespace is the main tablespace where all the objects of a user reside. A quota has to be set for the user on this tablespace, else oracle will assume the quota is unlimited. Here is the syntax to create a default tablespace for our user:
CREATE TABLESPACE ORACLE_USER
DATAFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USER01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
creating a temporary tablespace for the user
Temporary tablespace is useful for sorting. Here is the syntax to create a temporary tablespace for our user:
CREATE SMALLFILE TEMPORARY TABLESPACE ORACLE_USERTEMP TEMPFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USERtemp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
creating the user
When creating the user, these are important options:
"identified by" is used to specify the password of the user
"profile" the resource limits specified for the user. We will look at profile and roles in subsequent postings
"default tablespace" is used to specify where the objects of the user will be kept
"quota" specifies the percentage of the space in the default tablespace that is assigned to the user
"temporary tablespace" is already explained above
""account unlock" specifies the status of the account. If you specify "account lock", the account will be locked and the user will not be able to connect after creation.
CREATE USER ORACLE_USER PROFILE "DEFAULT" IDENTIFIED BY "XXXXX"
DEFAULT TABLESPACE ORACLE_USER
TEMPORARY TABLESPACE ORACLE_USERTEMP
QUOTA UNLIMITED ON ORACLE_USER ACCOUNT UNLOCK;
granting privileges for the user
This gives the user the privilege to access the database. "Create session" must be granted for the user to be able to connect to the database. Other privileges are self-explanatory. Oracle did a good job by using simple English meaning for the database terms.
GRANT CREATE SESSION TO ORACLE_USER;
GRANT CONNECT TO ORACLE_USER;
GRANT CREATE TRIGGER TO ORACLE_USER;
GRANT CREATE VIEW TO ORACLE_USER;
GRANT CREATE PROCEDURE TO ORACLE_USER;
GRANT CREATE SEQUENCE TO ORACLE_USER;
GRANT CREATE TABLE TO ORACLE_USER;
GRANT CREATE SYNONYM TO ORACLE_USER;
checking privileges granted to user
You can query the view "dba_sys_privs" to check the privileges granted to a user at any point in time. Here is an example:
SQL> select privilege from dba_sys_privs
2 where grantee='ORACLE_USER';
PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE TABLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE PROCEDURE
CREATE SESSION
CREATE VIEW
7 rows selected.
checking the user's info
You can query the view "dba_users" to check the user's info. For example:
SQL> select username, default_tablespace from dba_users where username='ORACLE_USER';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_USER ORACLE_USER
SQL>
dropping a user
You can delete a user by using the syntax:
drop user ORACLE_USER cascade;
This will permanently delete the user and all its objects from the database. It will also purge all the users objects in the recycle bin in Oracle 11g.
Creating a user and setting privileges for them are common everyday tasks of Oracle DBAs and Developers. You can either do them through the commandline or use client tools, eg. SQL Developer.
creating a default tablespace for the user
Default tablespace is the main tablespace where all the objects of a user reside. A quota has to be set for the user on this tablespace, else oracle will assume the quota is unlimited. Here is the syntax to create a default tablespace for our user:
CREATE TABLESPACE ORACLE_USER
DATAFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USER01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
creating a temporary tablespace for the user
Temporary tablespace is useful for sorting. Here is the syntax to create a temporary tablespace for our user:
CREATE SMALLFILE TEMPORARY TABLESPACE ORACLE_USERTEMP TEMPFILE '/home/oracle/app/oracle/oradata/ORACLE_USER/datafile/ORACLE_USERtemp01.dbf' SIZE 5M AUTOEXTEND ON NEXT 2048K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
creating the user
When creating the user, these are important options:
"identified by" is used to specify the password of the user
"profile" the resource limits specified for the user. We will look at profile and roles in subsequent postings
"default tablespace" is used to specify where the objects of the user will be kept
"quota" specifies the percentage of the space in the default tablespace that is assigned to the user
"temporary tablespace" is already explained above
""account unlock" specifies the status of the account. If you specify "account lock", the account will be locked and the user will not be able to connect after creation.
CREATE USER ORACLE_USER PROFILE "DEFAULT" IDENTIFIED BY "XXXXX"
DEFAULT TABLESPACE ORACLE_USER
TEMPORARY TABLESPACE ORACLE_USERTEMP
QUOTA UNLIMITED ON ORACLE_USER ACCOUNT UNLOCK;
granting privileges for the user
This gives the user the privilege to access the database. "Create session" must be granted for the user to be able to connect to the database. Other privileges are self-explanatory. Oracle did a good job by using simple English meaning for the database terms.
GRANT CREATE SESSION TO ORACLE_USER;
GRANT CONNECT TO ORACLE_USER;
GRANT CREATE TRIGGER TO ORACLE_USER;
GRANT CREATE VIEW TO ORACLE_USER;
GRANT CREATE PROCEDURE TO ORACLE_USER;
GRANT CREATE SEQUENCE TO ORACLE_USER;
GRANT CREATE TABLE TO ORACLE_USER;
GRANT CREATE SYNONYM TO ORACLE_USER;
checking privileges granted to user
You can query the view "dba_sys_privs" to check the privileges granted to a user at any point in time. Here is an example:
SQL> select privilege from dba_sys_privs
2 where grantee='ORACLE_USER';
PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE TABLE
CREATE SEQUENCE
CREATE SYNONYM
CREATE PROCEDURE
CREATE SESSION
CREATE VIEW
7 rows selected.
checking the user's info
You can query the view "dba_users" to check the user's info. For example:
SQL> select username, default_tablespace from dba_users where username='ORACLE_USER';
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
ORACLE_USER ORACLE_USER
SQL>
dropping a user
You can delete a user by using the syntax:
drop user ORACLE_USER cascade;
This will permanently delete the user and all its objects from the database. It will also purge all the users objects in the recycle bin in Oracle 11g.
Tuesday, January 15, 2013
Understanding RMAN
Recovery manager (RMAN) is an oracle tool for backing up, restoring and recovering oracle databases. The major task of an oracle dba is to make sure data is available for use anytime and everytime the need arise. Availability and reliability is the key responsibility of a dba.
Connection
Logging into RMAN is very easy. Use this command “RMAN TARGET /” or “rman target my_db”
set ORACLE_SID=orcl
C:\Users\kelebute>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 11:48:33 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1328927802)
RMAN>
or
rman
RMAN> connect target sys/****@orcl
You don't have to specify the "as sysdba" because it is silently implied when you log into RMAN.
To view all the configurations in rman for a particular database, you can use “show all” command.
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\KELEBUTE\PRODUCT\11.2.0\DBHOME_1\
DATABASE\SNCFORCL.ORA'; # default
checking your configuration
v$rman_configuration
This view provides the summary of RMAN configuration. You could also use show all to retrieve RMAN configuration.
SQL> SELECT * FROM V$RMAN_CONFIGURATION;
CONF# NAME VALUE
---------- ------------------------------ ------------------------
1 RETENTION POLICY TO REDUNDANCY 3
2 BACKUP OPTIMIZATION ON
3 DEFAULT DEVICE TYPE TO Disc
4 CONTROLFILE AUTOBACKUP ON
5 DEVICE TYPE DISK PARALLELISM 1
Backing up a database in noarchivelog mode
rman target /
run {
shutdown immediate;
startup mount;
backup database;
alter database open;
shutdown immediate;
}
Backing up a database in archivelog mode
man target /
RMAN> backup database plus archivelog;
Backup with archivelog delete input
backup database plus archivelog delete input;
Deleting archivelogs
delete expired archivelog all;
or
CROSSCHECK ARCHIVELOG ALL;
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-5';
You can change the date as you want.
incremental backup every night Mon - Sat
This is for updates on only the blocks that changed since last backup.
RMAN> backup incremental level 0 database tag="SUNDAY";
RMAN> backup incremental level 3 database tag="MONDAY";
RMAN> backup incremental level 3 database tag="TUESDAY";
RMAN> backup incremental level 3 database tag="WEDNESDAY";
RMAN> backup incremental level 2 database tag="THURSDAY";
RMAN> backup incremental level 3 database tag="FRIDAY";
RMAN> backup incremental level 3 database tag="SATURDAY";
According to Oracle documentation, RMAN is still the best way to backup database and also restore it. We will talk about restoring and recovery databases next time.
Here is the script I presently use. You can adapt to whatever will suit you. Remember to change the ORACLE_SID and location.
script for rman
set ORACLE_SID=ORCL
export ORACLE_SID
RMAN target / log=/home/oracle/rman.log
run{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4G;
crosscheck backup;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-7';
delete noprompt expired backup;
delete noprompt expired archivelog all;
sql 'alter system switch logfile';
backup database plus archivelog delete all input format '/disk1/backup/full_%U.bkp';
delete noprompt obsolete;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}
Thanks for visiting!
Recovery manager (RMAN) is an oracle tool for backing up, restoring and recovering oracle databases. The major task of an oracle dba is to make sure data is available for use anytime and everytime the need arise. Availability and reliability is the key responsibility of a dba.
Connection
Logging into RMAN is very easy. Use this command “RMAN TARGET /” or “rman target my_db”
set ORACLE_SID=orcl
C:\Users\kelebute>rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Dec 21 11:48:33 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1328927802)
RMAN>
or
rman
RMAN> connect target sys/****@orcl
You don't have to specify the "as sysdba" because it is silently implied when you log into RMAN.
To view all the configurations in rman for a particular database, you can use “show all” command.
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name ORCL are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
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 LOA
D TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'C:\APP\KELEBUTE\PRODUCT\11.2.0\DBHOME_1\
DATABASE\SNCFORCL.ORA'; # default
checking your configuration
v$rman_configuration
This view provides the summary of RMAN configuration. You could also use show all to retrieve RMAN configuration.
SQL> SELECT * FROM V$RMAN_CONFIGURATION;
CONF# NAME VALUE
---------- ------------------------------ ------------------------
1 RETENTION POLICY TO REDUNDANCY 3
2 BACKUP OPTIMIZATION ON
3 DEFAULT DEVICE TYPE TO Disc
4 CONTROLFILE AUTOBACKUP ON
5 DEVICE TYPE DISK PARALLELISM 1
Backing up a database in noarchivelog mode
rman target /
run {
shutdown immediate;
startup mount;
backup database;
alter database open;
shutdown immediate;
}
Backing up a database in archivelog mode
man target /
RMAN> backup database plus archivelog;
Backup with archivelog delete input
backup database plus archivelog delete input;
Deleting archivelogs
delete expired archivelog all;
or
CROSSCHECK ARCHIVELOG ALL;
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-5';
You can change the date as you want.
incremental backup every night Mon - Sat
This is for updates on only the blocks that changed since last backup.
RMAN> backup incremental level 0 database tag="SUNDAY";
RMAN> backup incremental level 3 database tag="MONDAY";
RMAN> backup incremental level 3 database tag="TUESDAY";
RMAN> backup incremental level 3 database tag="WEDNESDAY";
RMAN> backup incremental level 2 database tag="THURSDAY";
RMAN> backup incremental level 3 database tag="FRIDAY";
RMAN> backup incremental level 3 database tag="SATURDAY";
According to Oracle documentation, RMAN is still the best way to backup database and also restore it. We will talk about restoring and recovery databases next time.
Here is the script I presently use. You can adapt to whatever will suit you. Remember to change the ORACLE_SID and location.
script for rman
set ORACLE_SID=ORCL
export ORACLE_SID
RMAN target / log=/home/oracle/rman.log
run{
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$ORACLE_BASE/backups/datafiles/%d_%T_s%s_s%p' MAXPIECESIZE 4G;
crosscheck backup;
CROSSCHECK ARCHIVELOG ALL;
delete noprompt ARCHIVELOG UNTIL TIME 'SYSDATE-7';
delete noprompt expired backup;
delete noprompt expired archivelog all;
sql 'alter system switch logfile';
backup database plus archivelog delete all input format '/disk1/backup/full_%U.bkp';
delete noprompt obsolete;
BACKUP VALIDATE DATABASE ARCHIVELOG ALL;
}
Thanks for visiting!
Thursday, January 10, 2013
10 easy steps to configure Datagurad on Windows 7
Hey folks! I am not going to bore you with stories on dataguard; it is 90% practical. If you need definitions or explanations of why you need dataguard, please visit this oracle site
Here is the step by step guide on how to configure dataguard on a Windows 7 environment.
Version: Oracle 11.2.0
Hardware: Windows 7
Prerequisite:
a.) Archivelog mode. Here is the step to put your database in archivelog mode
SQL> shutdown immediate
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
b.) Start your listener
c:\> LSNRCTL START
c.) create TNSNAMES.ORA
use netca to create tnsnames for 'orcl1' and 'orclstby'
Please Note:
1. You have to take a backup of your database before you start, otherwise you will get an error
c:\> set ORACLE_SID=orcl1
c:\> rman target /
RMAN> backup database including archivelog;
2. You need to create ORACLE_SID using oradim utility. Please use this command:
create instance
---------------------
C:\app\kelebute\product\11.2.0\dbhome_1\BIN>oradim -NEW -SID orclstby -SYSPWD oracle -pfile initorclstby.ora
Instance created.
3. Use NetMgr to register the databases ('orcl1' and 'orclstby'):
click Start
Click All Programs
Click Oracle Dbhome_1
Click Configuration and Migration tools
Then click "Net Mgr"
NOTE: NetMgr will not accept ORACLE_SID name that is more than 8 characters.
Make sure you verify your TNSNAMES registration in NetMgr.
4. Create a pfile using this command on primary:
SQL> create pfile='c:\app\kelebute\initorclstby.ora' from spfile;
5. create pfile for standby
C:\Windows\system32>sqlplus sys/oracle@psi as sysdba
SQL> create pfile='c:\APP\KELEBUTE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITPSI2.ORA
' from spfile;
6. Add standby redo log files on primary
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\APP\KELEBUTE\ORADATA\PSI\REDO03.LOG
C:\APP\KELEBUTE\ORADATA\PSI\REDO02.LOG
C:\APP\KELEBUTE\ORADATA\PSI\REDO01.LOG
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO04.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO05.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO06.LOG' size 50m;
Database altered.
7. Set parameters on primary
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(psi,psi2)';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=psi2 async VALID_FOR=(ONLINE_L
OGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=psi2';
SQL> Alter system set standby_file_management=auto;
SQL> Alter system set fal_client =psi;
SQL> Alter system set fal_server=psi2;
8. Take a backup
C:\Windows\system32>rman target psi
RMAN> backup database plus archivelog;
9. Make sure you connect to both SIDs on RMAN
c:\>rman
RMAN> connect target sys/oracle@orcl1
RMAN> connect auxiliary sys/oracle@orclstby
RMAN> @c:\k\dataguard.txt
-------------------
Here is the script
-------------------
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
DB_FILE_NAME_CONVERT=('\ORCL1\','\ORCLSTBY\')
SPFILE
PARAMETER_VALUE_CONVERT '\ORCL1','\ORCLSTBY'
set db_unique_name='orclstby'
SET LOG_FILE_NAME_CONVERT '\ORCL1\','\ORCLSTBY\'
SET SGA_MAX_SIZE '200M'
SET SGA_TARGET '125M'
set control_files='c:\app\kelebute\oradata\orclstby\orclstby01.ctl'
set log_archive_max_processes='5'
set fal_client='orclstby'
set fal_server='orcl1'
set standby_file_management='AUTO'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL1,ORCLSTBY)'
SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL1'
NOFILENAMECHECK;
}
-------------------------------------
10. Put standby in recovery mode
sqlplus sys/oracle@psi2 as sysdba
SQL> Alter database recover managed standby database using current logfile disco
nnect from session;
SQL> Alter database recover managed standby database cancel;
Dataguard is configured!
Hey folks! I am not going to bore you with stories on dataguard; it is 90% practical. If you need definitions or explanations of why you need dataguard, please visit this oracle site
Here is the step by step guide on how to configure dataguard on a Windows 7 environment.
Version: Oracle 11.2.0
Hardware: Windows 7
Prerequisite:
a.) Archivelog mode. Here is the step to put your database in archivelog mode
SQL> shutdown immediate
startup mount;
alter database archivelog;
alter database force logging;
alter database open;
b.) Start your listener
c:\> LSNRCTL START
c.) create TNSNAMES.ORA
use netca to create tnsnames for 'orcl1' and 'orclstby'
Please Note:
1. You have to take a backup of your database before you start, otherwise you will get an error
c:\> set ORACLE_SID=orcl1
c:\> rman target /
RMAN> backup database including archivelog;
2. You need to create ORACLE_SID using oradim utility. Please use this command:
create instance
---------------------
C:\app\kelebute\product\11.2.0\dbhome_1\BIN>oradim -NEW -SID orclstby -SYSPWD oracle -pfile initorclstby.ora
Instance created.
3. Use NetMgr to register the databases ('orcl1' and 'orclstby'):
click Start
Click All Programs
Click Oracle Dbhome_1
Click Configuration and Migration tools
Then click "Net Mgr"
NOTE: NetMgr will not accept ORACLE_SID name that is more than 8 characters.
Make sure you verify your TNSNAMES registration in NetMgr.
4. Create a pfile using this command on primary:
SQL> create pfile='c:\app\kelebute\initorclstby.ora' from spfile;
5. create pfile for standby
C:\Windows\system32>sqlplus sys/oracle@psi as sysdba
SQL> create pfile='c:\APP\KELEBUTE\PRODUCT\11.2.0\DBHOME_1\DATABASE\INITPSI2.ORA
' from spfile;
6. Add standby redo log files on primary
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
C:\APP\KELEBUTE\ORADATA\PSI\REDO03.LOG
C:\APP\KELEBUTE\ORADATA\PSI\REDO02.LOG
C:\APP\KELEBUTE\ORADATA\PSI\REDO01.LOG
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO04.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO05.LOG' size 50m;
Database altered.
SQL> alter database add standby logfile
2 'C:\APP\KELEBUTE\ORADATA\PSI\REDO06.LOG' size 50m;
Database altered.
7. Set parameters on primary
SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(psi,psi2)';
SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=psi2 async VALID_FOR=(ONLINE_L
OGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=psi2';
SQL> Alter system set standby_file_management=auto;
SQL> Alter system set fal_client =psi;
SQL> Alter system set fal_server=psi2;
8. Take a backup
C:\Windows\system32>rman target psi
RMAN> backup database plus archivelog;
9. Make sure you connect to both SIDs on RMAN
c:\>rman
RMAN> connect target sys/oracle@orcl1
RMAN> connect auxiliary sys/oracle@orclstby
RMAN> @c:\k\dataguard.txt
-------------------
Here is the script
-------------------
run {
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE TARGET DATABASE
FOR STANDBY
DB_FILE_NAME_CONVERT=('\ORCL1\','\ORCLSTBY\')
SPFILE
PARAMETER_VALUE_CONVERT '\ORCL1','\ORCLSTBY'
set db_unique_name='orclstby'
SET LOG_FILE_NAME_CONVERT '\ORCL1\','\ORCLSTBY\'
SET SGA_MAX_SIZE '200M'
SET SGA_TARGET '125M'
set control_files='c:\app\kelebute\oradata\orclstby\orclstby01.ctl'
set log_archive_max_processes='5'
set fal_client='orclstby'
set fal_server='orcl1'
set standby_file_management='AUTO'
SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(ORCL1,ORCLSTBY)'
SET LOG_ARCHIVE_DEST_2='SERVICE=ORCL1 NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=ORCL1'
NOFILENAMECHECK;
}
-------------------------------------
10. Put standby in recovery mode
sqlplus sys/oracle@psi2 as sysdba
SQL> Alter database recover managed standby database using current logfile disco
nnect from session;
SQL> Alter database recover managed standby database cancel;
Dataguard is configured!
Subscribe to:
Posts (Atom)