Delete Archivelog Using RMAN in Oracle

If your storage is about full, you must either delete old backup and archivelog or move to tape. If you want to delete archivelog from FRA(ASM Storage-Flash Revovery Area) or filesystem for win space, you can use below commands. You can delete archivelog safely, because archivelog deleting does not harm to database.

Archivelog List Commands

RMAN>list archivelog all;
RMAN>list copy of archivelog until time ‘SYSDATE-10′;
RMAN>list copy of archivelog from time ‘SYSDATE-10′
RMAN>list copy of archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>list copy of archivelog from sequence 1000;
RMAN>list copy of archivelog until sequence 1500;
RMAN>list copy of archivelog from sequence 1000 until sequence 1500;

Archivelog Delete Commands

RMAN>delete archivelog all;
RMAN>delete archivelog until time ‘SYSDATE-10′;
RMAN>delete archivelog from time ‘SYSDATE-10′
RMAN>delete archivelog from time ‘SYSDATE-10′ until time ‘SYSDATE-2′;
RMAN>delete archivelog from sequence 1000;
RMAN>delete archivelog until sequence 1500;
RMAN>delete archivelog from sequence 1000 until sequence 1500;

Note : Also, you can use noprompt statement for do not yes-no question.
RMAN>delete noprompt archivelog until time ‘SYSDATE-10′;

RMAN>list expired archivelog all;

RMAN>crosscheck archivelog all;

RMAN>delete noprompt expired archivelog all;

RMAN>delete expired archivelog all;

RMAN>list expired archivelog all;

Update IBM AIX

Once the software is downloaded, it can be installed using smitty: $ smitty update_all or using the "update_all" command: $ update_all -d /usr/sys/inst.images/installp/ppc -p The "-p" in the "update_all" command signifies "pretend" mode. Remove it after the pretend runs cleanly.

Oracle Data Pump (IMPDP) via Network link

I use Oracle's datapump via network link methodology whenever I need to use data pump instead of taking export, copying over dumpfiles, and then doing import.  The Data pump via network link method is much simpler.


- Create user on SOURCE db to use for network link in TARGET database.
CREATE USER ARTISDBA IDENTIFIED BY "Password123$";
GRANT DBA TO ARTISDBA;

- Create Database link on target
CREATE DATABASE LINK SERVICE_NAME CONNECT TO ARTISDBA IDENTIFIED BY "Password123$" USING 'SERVICE_NAME';

- Copy TNSNAMES entry for source  database to target's tnsnames.ora file.

- Create directory in TARGET to use for your logfiles.
CREATE DIRECTORY dumpdir AS '/orabkup/expdp';
GRANT READ,WRITE ON DIRECTORY dumpdir to PUBLIC;

- Run import job on TARGET.  You parallelize the job to make it faster.
impdp parfile=impdp.par

PARFILE contents:

DIRECTORY=dumpdir
NETWORK_LINK=SERVICE_NAME
SCHEMAS='TEST'
PARALLEL=4
LOGFILE=impdp_from_service_name.log

upgrade 10g to 11g

- ORA-06512 executing utlu112i.sql -> Disable source DB flashback.

- The CEP file “/mypath/rdbmsup.sql” does not provide the version directive.

June 20, 2012 by urisnotaword

Trying to run dbua results in:

Could not get the database version from the “Oracle Server” component. The CEP file “/mypath/rdbmsup.sql” does not provide the version directive.

Check that the current db version is supported for direct upgrade to desired version with dbua. (note 870814.1 has the matrix for 11gR2)

 

 

Connecting Oracle with MS-Access

Submitted by admin on Sun, 2005-05-15 00:00

• RDBMS Server

This article shows how Oracle's Heterogeneous Services can be configured to allow a database to connect to a Microsoft Access database using standard databases links. The method described can be used to connect to MS-Access from about any platform - Unix/ Linux or Windows.
MS-Access 2003 and Oracle 10g Release 1 are used to illustrate the concepts. However, this procedure should work with Oracle 8i, 9i and 10g databases, as well as various versions of MS-Access.
Step 1: Prepare the MS-Access environment
If you do not have a MS-Access environment, start by installing the required software and create a test table.

Step 2: Define ODBC connectivity
Use the ODBC Administrator Utility to define a local System DSN that can be used to connect to the Access database (same machine). Ensure that the correct *.MDB database file is selected.

Step 3: Prepare the Oracle Environment
Install the Oracle Database Server software on the same machine where MS-Access is installed.
NOTE: It is not sufficient to only install Client Software, as we require an Oracle Net Listener and the Heterogeneous Services (ORACLE_HOME\hs directory) software to be installed as well.
Step 4: Configure and Start the Oracle Listener
Configure the Oracle Listener on the Windows machine. Here is a sample LISTENER.ORA entry that can be used. Change the HOST, PORT and ORACLE_HOME entries to match your setup. You may also use a different SID_NAME if required.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = my_windows_machine_name)(PORT = 1521)) 
      )
    )
  )
SID_LIST_LISTENER=
 (SID_LIST =
  (SID_DESC =
   (SID_NAME = hsodbc)
   (ORACLE_HOME = c:\Oracle\Ora101)
   (PROGRAM = hsodbc)
  )
)

Stop and start the listener service or from the command line:
C:\> lsnrctl stop
C:\> lsnrctl start
Step 5: Configure Oracle HS:
Edit the ORACLE_HOME\hs\admin\inithsodbc.ora file and add your ODBC System DSN Name (ODBC1 in our case as defined in step 3).

HS_FDS_CONNECT_INFO = odbc1 
HS_FDS_TRACE_LEVEL = off

Note: If you used a custom SID_NAME in step 4, name the file accordingly - INIT.ORA.
Step 6: Configure Oracle connectivity to Windows Machine
From now on we are going to work on the Oracle Server (Unix or whatever you run) add the following TNSNAMES.ORA entry:

access_db.world =
  (DESCRIPTION =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.1.31) (PORT = 1521)
  )
  (CONNECT_DATA =
      (SID = hsodbc)
  )
  (HS=OK)
 )

Ensure you can tnsping the new entry before continuing.
Step 7: Create a database link
Create a database link using the entry defined in step 6.

SQL> CREATE DATABASE LINK access_db USING 'access_db.world';  

Database link created.

The tables in the access database can now be queried from the Oracle environment.

SQL> SELECT * FROM my_access_tab@access_db;

        ID Field1               Field2
---------- -------------------- --------------------
         1 row1col1             row1col2
         2 row2col1             row2col2
         3 row3col1             row3col2

SQL> CREATE TABLE my_oracle_tab AS SELECT * FROM my_access_tab@access_db;

Table created.

Additional Reading:
1. Oracle Heterogeneous Connectivity Administrator's Guide.
2. Oracle Metalink Note 109730.1 - How to setup generic connectivity (Heterogeneous Services) for Windows NT.

»

• admin's blog
• Login to post comments

.:: Blogger Home :: Wi

undo_tablespace block damaged.

SQL> STARTUP MOUNT

SQL> alter database datafile '/data/oradata/VISAOEM/undotbs01.dbf' offline drop;

SQL> alter system set undo_management=manual scope=spfile;

SQL> CREATE UNDO TABLESPACE UNDOTBS3 DATAFILE
  '/data1/oradata/VISAOEM/undotbs02.dbf' SIZE 1120M AUTOEXTEND ON NEXT 200M MAXSIZE 16384M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
SQL> alter system set undo_tablespace=undotbs2 scope=spfile;

SQL> SHUTDOWN

SQL> startup
SQL> show parameter undo_tablespace;
SQL> select owner, segment_name,tablespace_name,status from dba_rollback_segs;

SQL> CREATE PFILE='/tmp/pfilevisaoem.ora' from spfile;


vi /tmp/pfilevisaoem.ora

--------------------------------------------------------------------------------------------------
_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7
$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$
,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$)
--------------------------------------------------------------------------------------------------

SQL> startup restrict pfile='/tmp/pfilevisaoem.ora'

SQL> drop tablespace undotbs1 including contents and datafiles;

SQL> alter system set undo_management=auto scope=spfile;



SQL> SHUTDOWN

SQL> startup

ORA-01045: user lacks CREATE SESSION privilege; logon denied(10.2.0.5)

ALTER USER ABC DEFAULT ROLE CONNECT;

Soha-Tra từ ""

Bạn đang tra từ ở từ điển Anh - Việt.
Có thể từ điển hiện chưa có từ này.

Thêm mới | Thông báo cho chúng tôi!
Hay tìm thêm từ này tại từ điển khác:
Việt - Anh
TratuBookmark cho IE /firefox /Chrome

---

Xem thêm tại: Soha-Tra Từ

ORA-19573: cannot obtain exclusive enqueue for datafile 1

ORA-19573: cannot obtain exclusive enqueue for datafile 1

Posted: March 17, 2013 in StandBy
Tags: exclusive enqueue, Incremental backup apply, ORA-19573

1

I got the following error while applying the incremental backup to the standby database “ORA-19573: cannot obtain exclusive enqueue for datafile 1″. This was very annoying as i have cross checked everything many times. Here is few lines from RMAN sessions from which applying incremental backup:

channel ORA_DISK_4: reading from backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f7o3u54k_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 03/07/2013 12:39:20
ORA-19870: error while restoring backup piece /oraclebackup/PLMQDBS/inc/incr_for_stdby_f4o3u54k_1_1
ORA-19573: cannot obtain exclusive enqueue for datafile 1

Well, error was because of very silly mistake, i forgot to cancel the media recovery process before starting the RMAN incremental apply session. Once i have cancelled the recovery process as below, incremental backup ran fine.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

oracore@myhost $ sqlplus /nolog SQL*Plus: Release 11.2.0.1.0 Production on Thu Mar 7 12:41:16 2013 Copyright (c) 1982, 2009, Oracle.  All rights reserved. SQL> conn sys as sysdba Enter password: Connected. SQL> select name from v$database; NAME --------- PLMQDBS SQL> select open_mode from v$database; OPEN_MODE -------------------- MOUNTED SQL> alter database recover managed standby database cancel; Database altered.

what a small mistake !!!!!!!!

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary? (second choice)

Hi Friends,
I will write about resolving the Primary/Standby log gap in case of we deleted some archive log files from primary. Suppose that we don’t have the backup of the deleted archive files. Normally we (DBAs) should not allow such a situation but such a situation can happen to us. In this case,  we need to learn the current SCN number of Primary and standby databases.
1- let’s learn current SCN number with the following query on the Primary.

SQL> select current_scn from v$database;
CURRENT_SCN
———–
1289504966

2- let’s learn current SCN number with the following query on the Standby

SQL> select current_scn from v$database;
CURRENT_SCN
———–
1289359962

using the function scn_to_timestamp(SCN_NUMBER) you can check the time difference between primary and standby.
3- Stop apply process on the Standby database.

SQL> alter database recover managed standby database cancel;

4- Shutdown the Standby database.

SQL> shutdown immediate;

5- Take incremental backup from the latest SCN number of the Standby database on the Primary database. And copy backup to the standby server.

RMAN> backup incremental from scn 1289359962 database;
# scp /backup_ISTANBUL/dun52q66_1_1 oracle@192.168.2.3:/oracle/ora11g

6- Create new standby control file on the Primary database. And copy this file to standby server.

SQL> alter database create standby controlfile as ‘/oracle/ora11g/standby.ctl’;
# scp /oracle/ora11g/standby.ctl oracle@192.168.2.3:/oracle/ora11g

7- Open the Standby database on NOMOUNT state to learn control files location.

SQL> startup nomount
SQL> show parameter control_files

8- Replace new standby control file with old files.

# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data1/control01.ctl
# cp /oracle/ora11g/standby.ctl /oracle/ora11g/ISTANBUL/data2/control02.ctl

9- Open the Standby database on MOUNT state.

SQL> alter database mount standby database;

10- Connect to the RMAN and register backup to catalog.

# rman target /
RMAN> catalog start with ‘/oracle/ora11g’;

It will ask for confirmation. Click “y” .
11- Now, you can recover the Standby database. Start recover database.

RMAN> recover database;

When recover of database is finished, it searches the latest archive file. And it gives an ORA-00334 error. In this case, don’t worry about it. Exit from RMAN and start apply process on the standby database.

SQL> alter database recover managed standby database disconnect from session;

We solved the Primary/Standby log gap with RMAN incremental backup . When we faced with such a situation we don’t need to think about re-installing standby database. Because time is very valuable for us.
Talip Hakan Öztürk

How to Resolve Primary/Standby Log GAP In Case of Deleting Archivelogs From Primary?

A slightly different approach to roll-forward standby database procedure

Roll-forwarding a physical standby database is a procedure, technique and method typically used to resolve the huge archive gap between the primary and standby database that is caused by various reasons. It’s a faster and recommended approach to resolve the huge gap and make the standby database in line with the primary database in a quick fashion.

Most of the existing material in this context, explains only one approach, i.e. taking a fresh RMAN incremental back up on the primary database and then applying it on a standby database to fill the archive gap. In this article, I will be sharing an interesting scenario that I have confronted while building a standby database of 2TB sized database and how I resolved the issue taking a slight different approach to the problem.

The following is the typical roll-forward method is used to resolve the gap between the primary and standby databases. For example, when a huge gap, let’s say over 500 archives logs, is found on a physical standby database, you will follow the steps mentioned below to resolve the gap:

1. Cancel the automatic media recovery process (MRP) on the standby database, if in progress:
   
   SQL> alter database recover managed standby database cancel;
    
2. Take a note of current System Change Number (SCN) on the standby database using the following SQL statement:
   
   SQL> SELECT current_scn FROM V$DATABASE;
    
3. Perform an incremental RMAN back up and create a new standby control file on the primary database:
   
   $ export ORACLE_SID=<primary database SID>

$ rman target /

RMAN> BACKUP INCREMENTAL FROM SCN <value from the above query> DATABASE FORMAT ‘/u00/tmp/incr_db_%U’;

 --- create a new standby control-file 

RMAN> backup current controlfile for standby format ‘/u00/tmp/standby.ctl’;
 

4. Copy the files generated under the /u00/tmp directory to the same location on the standby database site. You can use any copy/FTP option to transfer the backup files from primary to the standby database site.
    
5. Once files are moved/copied on the standby site, execute the following sequence of commands on the standby database:
   
   $ rman target / 

RMAN> shutdown; 

RMAN> startup nomount;

RMAN> restore standby controlfile from ‘/u00/tmp/standby.ctl’

RMAN> alter database mount;

RMAN> catalog start with ‘+DG_PRIM/STDBY/DATAFILE’;

--- Respond with YES when the following is prompted:Do you really want to catalog the above files (enter YES or NO)? YES
 Note: the above step is necessary as the location of the primary and standby data files are likely to be a different one.

RMAN> switch database to copy; 

RMAN> recover database delete archivelog;
 

6. Once the recovery is completed on the standby database, re-create the standby redo logs and restart the MRP on the standby database using the following SQL statement:
   
   For 10g database:
   SQL> alter database recover managed standby database disconnect from session;
    For 11g database:
   SQL> alter database recover managed standby database using current logfile disconnect from session;