Mani's Blog

June 9, 2015

mysqldump: Error 2020: Got packet bigger than ‘max_allowed_packet’ bytes when dumping table `content_versions` at row: 0

Filed under: Uncategorized — mani @ 5:11 pm

issue:  While I running mysqldump, I ran into this issue.  I know this DB need bigger ‘max_allowed_packet’ value.  I have bumped the values, no luck.

mysql> show variables like ‘max_allowed_packet’;
+——————–+———–+
| Variable_name | Value |
+——————–+———–+
| max_allowed_packet | 134217728 |
+——————–+———–+
1 row in set (0.00 sec)

mysql> set global max_allowed_packet=536870912;
Query OK, 0 rows affected (0.00 sec)

Soultion: I have to setup the value –max_allowed_packet=1073741824  as a command line parameter to solve this issue.

Following line solved my issue.

mysqldump –max_allowed_packet=1073741824 –all-databases | gzip -c > all-db.sql.gz

Advertisements

May 19, 2015

LOG: restarted WAL streaming at %S on timeline 1

Filed under: Uncategorized — mani @ 4:30 am

Problem:

I had setup like this using streaming replication.  Once I promote Slave1 as Master, the replication on Slave2 is broken with following error messages.

Master –> Slave1 –> Slave2

2015-05-19_01:00:19.55306 2015-05-19 01:00:19.553 UTC: @/555a7720.599a[2986]: LOG: restarted WAL streaming at 449/1E000000 on timeline 1
2015-05-19_01:00:19.55317 2015-05-19 01:00:19.553 UTC: @/555a7720.599a[2987]: LOG: replication terminated by primary server
2015-05-19_01:00:19.55319 2015-05-19 01:00:19.553 UTC: @/555a7720.599a[2988]: DETAIL: End of WAL reached on timeline 1 at 449/1E000090.

Solution:

I had to copy files from pg_xlogs directory from Master to Slave box under pg_xlogs directory.   Added recovery_target_timeline = ‘latest’  in recovery.conf, then restarted the slave DB.

March 30, 2015

MySQL Interview questions

Filed under: Uncategorized — mani @ 9:06 pm

You worked on Oracle, MS SQL and MySQL, What is your strongest Area? Oracle

How would find out the size of the table in MySQL? Oracle DBA_Segments Nope

How do you start or stop MySQL services? ok

What is the default port on 3306?

How would you run multiple mysql instances on the same box? Nope

How do you find your whether the database is fragmented are not? How do you fix it?

What kind of backup tool you have used to backup? How would you schedule daily backup using xtrabackup?

Let us assume that customer calls you and says the database response is very slow? What would be your approach in debugging this issue?
Slow query log.

How do you make Schema changes on on 200GB table with foreign key?

How would you find out list of indexes on the table?

Have you ever done replication?
Replication – Cloning – Streaming.
Row-based and statement based.
How do you make sure Master and Slave database is in sync? If not in sync How would you fix it?

Are you familiar with different Isolation levels?

What happens when the auto increment value reached the maximum value?

How do you check the query performance? What would you do if the index is not being used?

Someone had an access to server, he/she dropped a database. How would you recover to the point in time?

How can you drop the Primary key from the table?

Percona Tool Kit.

How good you are in scripting?

December 3, 2013

[Errno 14] PYCURL ERROR 22 – “The requested URL returned error: 404”

Filed under: Uncategorized — mani @ 10:57 pm

I ran into following issue while I was installing Cloudera Manager 4.x.

Loaded plugins: fastestmirror, security
Loading mirror speeds from cached hostfile
* base: mirrors.syringanetworks.net
* extras: mirrors.kernel.org
* updates: mirror.san.fastserv.com
Setting up Install Process
Resolving Dependencies
–> Running transaction check
—> Package cloudera-manager-server.x86_64 0:5.0.0-0.cm5b1.p0.77.el6 will be installed
–> Processing Dependency: cloudera-manager-daemons = 5.0.0 for package: cloudera-manager-server-5.0.0-0.cm5b1.p0.77.el6.x86_64
–> Running transaction check
—> Package cloudera-manager-daemons.x86_64 0:5.0.0-0.cm5b1.p0.77.el6 will be installed
–> Finished Dependency Resolution

Dependencies Resolved

================================================================================
Package                  Arch   Version                 Repository        Size
================================================================================
Installing:
cloudera-manager-server  x86_64 5.0.0-0.cm5b1.p0.77.el6 cloudera-manager 7.7 k
Installing for dependencies:
cloudera-manager-daemons x86_64 5.0.0-0.cm5b1.p0.77.el6 cloudera-manager 174 M

Transaction Summary
================================================================================
Install       2 Package(s)

Total download size: 174 M
Installed size: 240 M
Downloading Packages:
http://archive.cloudera.com/cm4/redhat/6/x86_64/cm/4/RPMS/x86_64/cloudera-manager-daemons-5.0.0-0.cm5b1.p0.77.el6.x86_64.rpm: [Errno 14] PYCURL ERROR 22 – “The requested URL returned error: 404”
Trying other mirror.
http://archive.cloudera.com/cm4/redhat/6/x86_64/cm/4/RPMS/x86_64/cloudera-manager-server-5.0.0-0.cm5b1.p0.77.el6.x86_64.rpm: [Errno 14] PYCURL ERROR 22 – “The requested URL returned error: 404”
Trying other mirror.

Error Downloading Packages:
cloudera-manager-daemons-5.0.0-0.cm5b1.p0.77.el6.x86_64: failure: RPMS/x86_64/cloudera-manager-daemons-5.0.0-0.cm5b1.p0.77.el6.x86_64.rpm from cloudera-manager: [Errno 256] No more mirrors to try.
cloudera-manager-server-5.0.0-0.cm5b1.p0.77.el6.x86_64: failure: RPMS/x86_64/cloudera-manager-server-5.0.0-0.cm5b1.p0.77.el6.x86_64.rpm from cloudera-manager: [Errno 256] No more mirrors to try.

Solution:  Yum clean all didn’t work.  Previous version file was stuck, I had to delete files under cache yum  “/var/cache/yum/” then all worked well.

September 27, 2012

Oracle Agent stopped working : ORA-12505: TNS:listener does not currently know

Filed under: Uncategorized — mani @ 11:03 pm

Oracle Agent stopped working all of sudden.

Agent stop populating to grid. Stopped, Did clearstate, restarted agent. Nothing worked.

Tried following steps too, but no help.

1 stop the agent
2 delete agntstmp.txt and lastupld.xml (under sysman/emd)
3 Delete all the files under upload and collection directories
4 start the agent
Here it is a real solution.

Go to Grid Control,
Click on Setup
Choose Agents
Choose the Problematic Agent
Click on Unlock.
Resynchronize the agent.

 

 

June 21, 2012

ORA-16610: command “EDIT DATABASE %S SET PROPERTY” in progress

Filed under: Uncategorized — mani @ 10:43 pm

When I try to set up and configure, ran into Warning message.  I was able to solve the issue as below.

DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration
Name:                PRETZEL
Enabled:             YES
Protection Mode:     MaxPerformance
Databases:
XNOPRTZ   – Primary database
XNOPRTZDG – Physical standby database

Fast-Start Failover: DISABLED

Current status for “PRETZEL”:
Warning: ORA-16610: command “EDIT DATABASE XNOPRTZDG SET PROPERTY” in progress

Soultion:  I had to re-start the DG Broker as below.

 

SQL> ALTER SYSTEM SET DG_BROKER_START=FALSE;

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE;

System altered.

SQL>

May 31, 2012

Migrate Oracle RAC database into Single instance

Filed under: Uncategorized — mani @ 10:24 pm

Objective

We had a requirement to Migrate RAC database into Non-RAC single instance.

Assumptions

The RAC Cluster has 2 nodes called xnodbcontr1 and 2, instance names are XNODBCNT1,and XNODBCNT2 and the DB service name is XNODDBCNT. The goal is to remove the 2nd instance and convert the DB into Non-Cluster DB.

Steps involved

Following steps are followed to convert the RAC DB to Non-RAC.

  1. Stop the DB server and remove instance from cluster
    [oracle@xnodbcontr1 ~]$ srvctl status service -d XNODBCNT
    Service xno_contact is running on instance(s) XNODBCNT1, XNODBCNT2
    [oracle@xnodbcontr1 ~]$ srvctl stop database -d XNODBCNT
    }
    # Remove the instance
    {
    [oracle@xnodbcontr1 ~]$ srvctl remove instance -d XNODBCNT -i XNODBCNT2
    Remove instance XNODBCNT2 from the database XNODBCNT? (y/[n]) y
    You have new mail in /var/spool/mail/oracle
    [oracle@xnodbcontr1 ~]$ srvctl remove instance -d XNODBCNT -i XNODBCNT1
    Remove instance XNODBCNT1 from the database XNODBCNT? (y/[n]) y
    PRKP-1075 : Instance XNODBCNT1 is the last preferred instance for service xno_contact.
  2. Reset the cluster database value
    [oracle@xnodbcontr1 ~]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 31 16:57:15 2012
    Copyright (c) 1982, 2008, Oracle. All rights reserved.
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 5.9861E+10 bytes
    Fixed Size 2165248 bytes
    Variable Size 2.5233E+10 bytes
    Database Buffers 3.4360E+10 bytes
    Redo Buffers 266256384 bytes
    Database mounted.
    Database opened.
    SQL> alter system set cluster_database=false scope=spfile;
    System altered.
    SQL> alter system set cluster_database_instances=1 scope=spfile;
    System altered.
    SQL> alter database disable thread 2;
    Database altered.
  3. Drop unnecessary log files
    SQL> select thread#, group# from v$log;
       THREAD# GROUP#
    ---------- ----------
    1 11
    1 12
    1 13
    1 14
    1 15
    2 21
    2 22
    2 23
    2 24
    2 25
    10 rows selected.
    SQL> alter database drop logfile group 21;
    Database altered.
    SQL> alter database drop logfile group 22;
    Database altered.
    SQL> alter database drop logfile group 23;
    Database altered.
    SQL> alter database drop logfile group 24;
    Database altered.
    SQL> alter database drop logfile group 25;
    Database altered.
    SQL>
  4. Remove unnecessary UNDO tablespace.
    SQL> drop tablespace UNDOTBS2 including contents and datafiles;
    Tablespace dropped.
  5. Create pfile and remove parameters refer to 2nd instance.
    SQL> create pfile from spfile;
    File created.
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
    ## Removed the reference to instance2 from pfile.
        
    SQL> create spfile from pfile;
    File created.
  6. Start up the database with new spfile.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 5.9861E+10 bytes
    Fixed Size 2165248 bytes
    Variable Size 2.7917E+10 bytes
    Database Buffers 3.1675E+10 bytes
    Redo Buffers 266256384 bytes
    Database mounted.
    Database opened.
  7. Verified the transaction is flowing in.
    SQL> select max(id) from xn_contacts.profile2;
       MAX(ID)
    ----------
     180701340
    SQL> /
       MAX(ID)
    ----------
     180701413
    SQL>

April 4, 2012

Project Management

Filed under: Uncategorized — mani @ 8:18 pm

Project Management

January 12, 2012

How to view gzipped entry XML in Oracle via Client

Filed under: Uncategorized — mani @ 1:11 am

In our database we store XML data in gzipped binary data format.  Column has been defined as RAW(2000).

Following is one of the ways to unzip the data.

select to_clob(rawtohex(entry_part_1))||to_clob(rawtohex(entry_part_2))
from S_1040485_OXFAMN.shape_user_0
where entry_part_2 is not null

To view as uncompressed XML, use following method.
SQL> create or replace function S_1040485_OXFAMN.decode_entry
(
entry_part_1 raw,
entry_part_2 raw,
entry blob
)
return blob
is
l_blob blob ;
begin

if entry is not null then
return utl_compress.lz_uncompress(entry);
end if;
if entry_part_2 is not null then
l_blob := to_blob(entry_part_1);
dbms_lob.append(l_blob, to_blob(entry_part_2));
return utl_compress.lz_uncompress(l_blob);
end if;
return utl_compress.lz_uncompress(entry_part_1);

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

Function created.

I was able to view unzipped XML data from SQL Developer using below SQL.

select S_1040485_OXFAMN.decode_entry(entry_part_1, entry_part_2, entry) xml
from S_1040485_OXFAMN.shape_user_0
where entry_part_2 is not null;

December 6, 2011

Restore READ ONLY Tablespace using RMAN

Filed under: Uncategorized — mani @ 11:11 pm

Restore READ ONLY Tablespace using RMAN

We have a database with one tablespace in readonly.   Ran into following issue while opening up the database.

Restoring the database:

RMAN> alter database mount;

database mounted

RMAN> run {
allocate channel d1 device type disk;
allocate channel d2 device type disk;
allocate channel d3 device type disk;
allocate channel d4 device type disk;
set until sequence= 11785;
restore database;
}
2> 3> 4> 5> 6> 7> 8>
allocated channel: d1
channel d1: sid=542 devtype=DISK

allocated channel: d2
channel d2: sid=1102 devtype=DISK

allocated channel: d3
channel d3: sid=537 devtype=DISK

allocated channel: d4
channel d4: sid=1099 devtype=DISK

executing command: SET until clause

Starting restore at 06-DEC-11
Starting implicit crosscheck backup at 06-DEC-11
Crosschecked 522 objects
Crosschecked 1 objects
Finished implicit crosscheck backup at 06-DEC-11

Starting implicit crosscheck copy at 06-DEC-11
Finished implicit crosscheck copy at 06-DEC-11

searching for all files in the recovery area
cataloging files…
cataloging done

List of Cataloged Files
=======================
File Name: /local/data/oracle/nfs/DATAMART/autobackup/2011_12_06/o1_mf_s_769191237_7fwht7qw_.bkp

datafile 7 not processed because file is read-only
channel d1: starting datafile backupset restore
channel d1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +XN_DATA/dsp0001/datafile/xn_etl.259.663804029
restoring datafile 00008 to +XN_DATA/dsp0001/datafile/xn_fido.471.677889679
channel d1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp
channel d2: starting datafile backupset restore
channel d2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +XN_DATA/dsp0001/datafile/undotbs1.257.663802853
restoring datafile 00003 to +XN_DATA/dsp0001/datafile/sysaux.256.663802853
channel d2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp
channel d3: starting datafile backupset restore
channel d3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +XN_DATA/dsp0001/datafile/system.258.663802853
restoring datafile 00006 to +XN_DATA/dsp0001/datafile/xn_test_etl.260.663804035
channel d3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp
channel d4: starting datafile backupset restore
channel d4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +XN_DATA/dsp0001/datafile/users.261.663802853
channel d4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp
channel d4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp tag=TAG20111205T235251
channel d4: restore complete, elapsed time: 00:00:01
channel d1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp tag=TAG20111205T235251
channel d1: restore complete, elapsed time: 00:00:26
channel d2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp tag=TAG20111205T235251
channel d2: restore complete, elapsed time: 00:02:11
channel d3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp tag=TAG20111205T235251
channel d3: restore complete, elapsed time: 00:16:17
Finished restore at 06-DEC-11
released channel: d1
released channel: d2
released channel: d3
released channel: d4

Recovering database:

RMAN> recover database;

Starting recover at 06-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=536 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1098 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=535 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=534 devtype=DISK
datafile 7 not processed because file is read-only

starting media recovery

channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_3: starting archive log restore to default destination
channel ORA_DISK_4: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11784
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftqks7j_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=11785
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftynqqx_.bkp
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=11786
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fv5o1fd_.bkp
channel ORA_DISK_4: restoring archive log
archive log thread=1 sequence=11787
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvdmzwf_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftqks7j_.bkp tag=BKP_DATAMART_ARCHL_120611121301
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011784_0001_697357424.oarl thread=1 sequence=11784
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7ftynqqx_.bkp tag=BKP_DATAMART_ARCHL_120611021300
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011785_0001_697357424.oarl thread=1 sequence=11785
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fv5o1fd_.bkp tag=BKP_DATAMART_ARCHL_120611041302
channel ORA_DISK_3: restore complete, elapsed time: 00:00:04
archive log filename=/arch/DATAMART/5641608e-0000011786_0001_697357424.oarl thread=1 sequence=11786
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvdmzwf_.bkp tag=BKP_DATAMART_ARCHL_120611061301
channel ORA_DISK_4: restore complete, elapsed time: 00:00:05
archive log filename=/arch/DATAMART/5641608e-0000011787_0001_697357424.oarl thread=1 sequence=11787
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_2: starting archive log restore to default destination
channel ORA_DISK_3: starting archive log restore to default destination
channel ORA_DISK_4: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11788
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvmo3cv_.bkp
channel ORA_DISK_2: restoring archive log
archive log thread=1 sequence=11789
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvtp2hs_.bkp
channel ORA_DISK_3: restoring archive log
archive log thread=1 sequence=11790
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw1q1d4_.bkp
channel ORA_DISK_4: restoring archive log
archive log thread=1 sequence=11791
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw8r13r_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvmo3cv_.bkp tag=BKP_DATAMART_ARCHL_120611081305
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011788_0001_697357424.oarl thread=1 sequence=11788
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fvtp2hs_.bkp tag=BKP_DATAMART_ARCHL_120611101303
channel ORA_DISK_2: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011789_0001_697357424.oarl thread=1 sequence=11789
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw1q1d4_.bkp tag=BKP_DATAMART_ARCHL_120611121302
channel ORA_DISK_3: restore complete, elapsed time: 00:00:03
archive log filename=/arch/DATAMART/5641608e-0000011790_0001_697357424.oarl thread=1 sequence=11790
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fw8r13r_.bkp tag=BKP_DATAMART_ARCHL_120611021301
channel ORA_DISK_4: restore complete, elapsed time: 00:00:04
archive log filename=/arch/DATAMART/5641608e-0000011791_0001_697357424.oarl thread=1 sequence=11791
channel ORA_DISK_1: starting archive log restore to default destination
channel ORA_DISK_1: restoring archive log
archive log thread=1 sequence=11792
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fwhs19r_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_06/o1_mf_annnn_BKP_DATAMART_ARCHL_1_7fwhs19r_.bkp tag=BKP_DATAMART_ARCHL_120611041300
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/arch/DATAMART/5641608e-0000011792_0001_697357424.oarl thread=1 sequence=11792
archive log filename=/arch/DATAMART/5641608e-0000011793_0001_697357424.oarl thread=1 sequence=11793
unable to find archive log
archive log thread=1 sequence=11794
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2011 17:59:45
RMAN-06054: media recovery requesting unknown log: thread 1 seq 11794 lowscn 1558554671

RMAN>

Issue:

Opening the database:

RMAN> alter database open resetlogs;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of alter db command at 12/06/2011 18:01:05
ORA-01157: cannot identify/lock data file 7 – see DBWR trace file
ORA-01110: data file 7: ‘+XN_DATA/dsp0001/datafile/xn_datamart.273.665597367’

Solution:

Ran the restore again with check readonly option.

RMAN> restore database check readonly;

Starting restore at 06-DEC-11
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00005 to +XN_DATA/datamart/datafile/xn_etl.269.769192545
restoring datafile 00008 to +XN_DATA/datamart/datafile/xn_fido.271.769192545
channel ORA_DISK_1: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp
channel ORA_DISK_2: starting datafile backupset restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
restoring datafile 00002 to +XN_DATA/datamart/datafile/undotbs1.270.769192545
restoring datafile 00003 to +XN_DATA/datamart/datafile/sysaux.263.769192547
channel ORA_DISK_2: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp
channel ORA_DISK_3: starting datafile backupset restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
restoring datafile 00001 to +XN_DATA/datamart/datafile/system.260.769192547
restoring datafile 00006 to +XN_DATA/datamart/datafile/xn_test_etl.272.769192545
channel ORA_DISK_3: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp
channel ORA_DISK_4: starting datafile backupset restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
restoring datafile 00004 to +XN_DATA/datamart/datafile/users.261.769192545
restoring datafile 00007 to +XN_DATA/dsp0001/datafile/xn_datamart.273.665597367
channel ORA_DISK_4: reading from backup piece /local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbo69_.bkp tag=TAG20111205T235251
channel ORA_DISK_1: restore complete, elapsed time: 00:00:28
channel ORA_DISK_2: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbnr7_.bkp tag=TAG20111205T235251
channel ORA_DISK_2: restore complete, elapsed time: 00:02:23
channel ORA_DISK_3: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbn7q_.bkp tag=TAG20111205T235251
channel ORA_DISK_3: restore complete, elapsed time: 00:16:29
channel ORA_DISK_4: restored backup piece 1
piece handle=/local/data/oracle/nfs/DATAMART/backupset/2011_12_05/o1_mf_nnndf_TAG20111205T235251_7ftpbmz3_.bkp tag=TAG20111205T235251
channel ORA_DISK_4: restore complete, elapsed time: 01:48:60
Finished restore at 06-DEC-11

RMAN>
RMAN>

RMAN> recover database;

Starting recover at 06-DEC-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=537 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=1098 devtype=DISK
allocated channel: ORA_DISK_3
channel ORA_DISK_3: sid=542 devtype=DISK
allocated channel: ORA_DISK_4
channel ORA_DISK_4: sid=1103 devtype=DISK
datafile 7 not processed because file is read-only

starting media recovery

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/06/2011 21:28:05
RMAN-06053: unable to perform media recovery because of missing log
RMAN-06025: no backup of log thread 1 seq 11793 lowscn 1558553689 found to restore

RMAN> alter database open resetlogs;

database opened

RMAN>

Next Page »

Create a free website or blog at WordPress.com.