OCP Blog
Super DBA
Table level recovery in Oracle 12c
by Vazha Mantua Monday, October 7, 2013 5:44 PM

Good Day,

One major new features in Oracle 12c in new RMAN feature named table level recovery . With 12c R1, you can recover a particular table or partition to a point-in-time or SCN from RMAN backups in the event of a table drop or truncate.

I test new feature on Linux x64_86 platform.  As I understood procedure of restore is

1. Backup database

2. Create an auxiliary database and recovers the specified tables or table partitions, until the specified point in time, into this auxiliary database

3. Make export of table from auxiliary database with data pump.

4. Make import to Source DB with data pump

5. Drop temporary auxiliary database .

-----------------------------------------------------------------------------------------------------

Let’s Begin, we have  made this example for : Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production, Single instance, archive mode.

set DB_RECOVERY_FILE_DEST parameter:

SQL> alter system set DB_RECOVERY_FILE_DEST=’/u1/oradata/backup’ scope=both;

create table, which we should restore from RMAN backup:

SQL> create table vazha.clients_12c as select * from dba_users; (table should not be in system and sysaux TBS!)

now we should make backup of DB:

export ORACLE_SID=ORA12C

rman target /

RMAN> backup database;

After finishing backup you can drop table from source DB ORA12C with command

SQL> drop table vazha.clients_12c;

 

-----------------------------------------------------------------------------------------------------

So now we can restore table from RMAN backup:

find last SCN which is on RMAN backup, you can find it with command list backup

RMAN> List backup;

List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    1.20G      DISK        00:01:32     07-OCT-13     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20131007T165002
        Piece Name: /u1/oradata/backup/ORA12C/backupset/2013_10_07/o1_mf_nnndf_TAG20131007T165002_955cj05c_.bkp
  List of Datafiles in backup set 1
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/system01.dbf
  3       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/sysaux01.dbf
  4       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/undotbs01.dbf
  6       Full 1735782    07-OCT-13 /u1/oradata/ORA12C/users01.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.64M      DISK        00:00:05     07-OCT-13     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20131007T165002
        Piece Name: /u1/oradata/backup/ORA12C/backupset/2013_10_07/o1_mf_ncsnf_TAG20131007T165002_955cm0jr_.bkp
  SPFILE Included: Modification time: 07-OCT-13
  SPFILE db_unique_name: ORA12C
  Control File Included: Ckp SCN: 1735826      Ckp time: 07-OCT-13

 

So you can restore table until SCN: 1735826.

For restoring table you should do:

export ORACLE_SID=ORA12C

rman target /

RMAN>recover table vazha.clients_12c until scn 1735826 auxiliary destination '/install/oracle/temp';

Oracle OS user should have privileges on directory /install/oracle/temp. on this destination RMAN create temporary database for exporting table.

 

That’s ALL.

Tags: , , ,

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], [] ORA-06553: PLS-801: internal error [56327]
by Vazha Mantua Friday, October 4, 2013 1:24 PM

Good Day,

Today I restored database from 32-bit Linux machine to 64-bit. after opening database , there were a lot of errors like that:

ORA-06544: PL/SQL: internal error, arguments: [56327], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56327].

Solution of this problem is recompiles existing PL/SQL modules in the format required by the new database.

Step by step:

SQL> shutdown immediate;
SQL> startup upgrade;
SQL> @$ORACLE_HOME/rdbms/admin/utlirp;
SQL> shutdown immediate;
SQL> startup;
SQL> @@$ORACLE_HOME/rdbms/admin/utlrp;

Tags: , , , , , ,

Oracle jobs, minimum interval,_job_queue_interval parameter
by Vazha Mantua Wednesday, September 5, 2012 3:21 PM

Good Day all,

Today we resolve problem which appear when you try run oracle job with interval less then 5 second, but unfortunately it run’s in 5 second.

Cause of this is default parameter of job_queue_interval, which is set 5 second. This parameter means wakeup interval in seconds for job queue coordinator , so we got 5 second gap with next job running.

On oracle documentation we read that job_queue_interval is obsolete in oracle 9i version , but there is hidden parameter with same name and it’s still valid, changed only technology , in oracle 8 it was snp background process, now it is job queue coordinator, which is responsible for job running.

So solution of decreasing job running interval is set _job_queue_interval with low value than 5 second.

you can also avoid change oracle hidden parameter, if you use dbms_scheduler.

 

 

Tags: , , , , , , , , , ,

How change DBID and DBNAME for Oracle Database
by Vazha Mantua Monday, June 18, 2012 3:46 PM

 

Main method changing dbname of database is recreating controlfile with set new name condition, but after this operation DBID will not changed.

Oracle Provide very simple tool, which help you change both DBID and DBNAME.

 

When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. After you change the DBID, you must open the database with the RESETLOG option, which re-creates the online redo logs, that’s why you should make a backup of the whole database immediately after changing the DBID. During changing DBID database change DBID for all datafiles and controlfiles.

Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated, additionally You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change,During changing DBNAME database change DBID for only controlfile not datafiles!

Let See example :

1. Ensure that you have validate backup

2.Shutdown database and startup in mount mode

3.To change the database name in addition to the DBID, specify the DBNAME parameter. This example changes the name to NEW_SID, also you should configure net service for connecting DB.

     $ORACLE_HOME/product/11.2.0/bin/nid   TARGET=SYS/password@test DBNAME=NEW_SID

 

4. After changing DBID and DBNAME ,you should change DB_NAME parameter on SPFILE(PFILE), shutdown database, mount it and open with resetlog option.

 

If you want only change DBNAME you should run command:

$ORACLE_HOME/product/11.2.0/bin/nid TARGET=SYS/password@test DBNAME=NEW_SID SETNAME=YES

 

For this case you don’t need open database with resetlog clause, but for both cases you should recreate password file.

 

Observe SSL errors
by Vazha Mantua Saturday, June 16, 2012 9:25 AM

Today We review some errors and solutions for fixing a problems with ssl

 

ORA-29024: Certificate Validation Failure

Reason: The certificate sent by the other side could not be validated. This may occur if the certificate has expired, has been revoked, or is invalid for another reason

Action: Check if certificate has expired or has been revoked , Use Oracle Wallet Manager to import the appropriate trust point into the wallet, ensure that the certificate has not been revoked and that certificate revocation list (CRL) checking is enabled

 

 

ORA-29223: Cannot Create Certificate Chain

Reason: A certificate chain cannot be created with the existing trust points for the certificate being installed.

Action: Install the trust point that signed the certificate with OWM

 

ORA-28885: No certificate with the required key usage found

Reason: Your certificate was not created with the appropriate X.509 Version 3 key usage extension.

Action: Use OWM to check the certificate's key usage

 

ORA-28759: Failure to Open File

Reason: The system could not open the specified file. This error occurs because the Oracle wallet cannot be found or auto login is disable

Action: Ensure that you correct use Oracle Wallet path, enable auto login if it’s possible.

 

ORA-28786: Decryption of Encrypted Private Key Failure

Reason: An incorrect password was used for private key. Cause may be auto login feature

Action: Use OWM to enable the auto login

 

Script generate all user privileges in one report
by Vazha Mantua Monday, May 14, 2012 12:09 PM

Good Day All.

Our task is get all privileges granted for user and create report based on this information. As you know privileges gives on user directly or on his role. Privileges divided by 2 part: system and object.

Let see example, create report for user Scott

1.Create repository table for store data:

create table vm_user_privs(privilege varchar2(100),user_name varchar2(100),object_name varchar2(100));

2. Main script

declare
cursor c_user is
select a.username from dba_users a
where a.username='SCOTT';
p_user varchar2(100);

begin
open c_user;
loop
fetch c_user into p_user;
insert into vm_user_privs
select /*+ rule */ a.privilege,p_user,a.table_name from dba_tab_privs a where a.grantee=p_user
union all
select /*+rule */ b.privilege,p_user,b.table_name from dba_tab_privs b where b.grantee in (select b1.granted_role from dba_role_privs b1 where b1.grantee=p_user)
union all
select /*+ rule */ c.privilege,p_user,null from dba_sys_privs c where c.grantee=p_user
union all
select /*+rule */ d.privilege,p_user,null from dba_sys_privs d where d.grantee in (select b2.granted_role from dba_role_privs b2 where b2.grantee=p_user);
commit;
EXIT WHEN c_user%NOTFOUND;
end loop;
close c_user;
end;

3. See result:

select * from vm_user_privs

Restore database to Asm from type backup to different server
by Vazha Mantua Friday, April 20, 2012 12:04 PM

Today I show you step by step procedure, how we can restore database from UFS file system to ASM file system, if backup was done on type device and in different server. In your example we use VERITAS Netbackup soft.

We should change some parameters in our PFILEor SPFILE  for using ASM .

This parameters are:

control_files='+DG01/orcl1/controlfile/ctl.f' 

db_create_file_dest='+DG01'

You should install VERITAS Netbackup client and configure it for using. Also in your case we use Oracle RDBMS version 11.2. and OS Linux.

Then we should run RMAN script and set appropriate environment for restoring backup set from type.

ORACLE_SID=ORCL1
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NB_ORA_SERV=mastersrv   (This is master server for backuping)
NB_ORA_CLIENT=testmachine (This is source server, from this server we have made backup) 
export ORACLE_SID NLS_LANG NB_ORA_SERV NB_ORA_CLIENT
echo $ORACLE_SID
echo $NLS_LANG
echo $NB_ORA_SERV
echo $NB_ORA_CLIENT

rman target /

---- Startup Database on nomount Stage
run
{  
startup nomount;
}

--- Restore Controlfile from autobackup

SET DBID=295123130 (This is DBID of source database. you can find DBID on v$database view, or on controlfile format in netbackup catalog)

run
{
allocate channel ch1_tape type sbt;
set controlfile autobackup format for device type sbt to '%F';
restore controlfile  from autobackup;
RELEASE CHANNEL ch1_tape;
}

--- Mount database
alter database mount;

--- Restore and Recover database with new ASM Storage.

In our database we have only 4 datafile. for your case you should add all datafiles in this script. In ASM  file system datafiles will be created with automatically generated name

run 
{
allocate channel t1 device type 'sbt_tape';
SET  NEWNAME FOR DATAFILE 1 TO NEW;
SET  NEWNAME FOR DATAFILE 2 TO NEW;
SET  NEWNAME FOR DATAFILE 3 TO NEW;
SET  NEWNAME FOR DATAFILE 4 TO NEW;
restore database;
SWITCH DATAFILE ALL;
recover database;
}

---- Rename Redo log files:
On source database redo logs were in location /u4/oradata/ORCL1.

SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo01.log'' TO ''+DG01/ORCL1/ONLINELOG/redo01.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo02.log'' TO ''+DG01/ORCL1/ONLINELOG/redo02.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo03.log'' TO ''+DG01/ORCL1/ONLINELOG/redo03.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo04.log'' TO ''+DG01/ORCL1/ONLINELOG/redo04.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo05.log'' TO ''+DG01/ORCL1/ONLINELOG/redo05.log'' ";
SQL "ALTER DATABASE RENAME FILE ''/u4/oradata/ORCL1/redo06.log'' TO ''+DG01/ORCL1/ONLINELOG/redo06.log'' ";

 

--- Open database with resetlogs clause and recreate temporary tablespace.

ALTER DATABASE OPEN RESETLOGS;

CREATE TEMPORARY TABLESPACE TEMP1;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP1;
DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;
CREATE TEMPORARY TABLESPACE TEMP;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;
DROP TABLESPACE TEMP1 INCLUDING CONTENTS AND DATAFILES;

In your case we restore database, but not configure it for archiving.

for archiving you should simple set these parameters and then enable it.

alter system set db_recovery_file_dest_size = '400G';
alter system set db_recovery_file_dest = '+DG01' ;

Good Luck!

large number of trace in $ORACLE_BASE/grid/cv/log/
by Vazha Mantua Saturday, April 14, 2012 11:50 AM

Good Day All,

Today searching large directories in Grid home I found a lot of trace files in directory $ORACLE_BASE/grid/cv/log/, cause of creating these files is cluvfy utility tracing, which by default is enabled in 11.2 version.it creates a new cvutrace.log everytime it gets executed.

But when it gets executed? Answer is: EM Grid Control agent collects metrics and needs to verify the status of the cluster, agent run command like this: $ORA_CRS_HOME/bin/cluvfy comp crs -display_status

The workaround is to modify the script, runcluvfy.sh or cluvfy script located in stage location of grid infrastructure to include SRVM_TRACE="false" at top of script, also you simple can delete these files.

 

Tags: , , , , ,

ORA-20222: ORA-24247: network access denied by access control list (ACL)
by Vazha Mantua Tuesday, February 21, 2012 1:14 PM

Good day all,

Let see a issue which appear after migration database from 10g to 11gR2

When executing DBMS_LDAP, UTL_INADDR (or) UTL_HTTP (or) any UTL packages after upgrading to 11gR1, fails with following error.

ORA-20222: ORA-24247: network access denied by access control list (ACL)

Cause of error is new security measure is introduced in Oracle 11g  for the following network-related PL/SQL packages: UTL_TCP, UTL_HTTP, UTL_SMTP, UTL_MAIL,  UTL_INADDR, DBMS_LDAP.By default, the ports are blocked.

We find Solution:

BEGIN
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(
acl => 'http_service.xml',
description => 'HTTP ACL',
principal => 'SCOTT',
is_grant => true,
privilege => 'connect');

DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(
acl => 'http_service.xml',
principal => 'SCOTT',
is_grant => true,
privilege => 'resolve');

DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(
acl => 'http_service.xml',
host => '*');
COMMIT;
END;

In our case user SCOTT has a privileges use http_service. If you want to open port for specific host on assign_acl part use direct IP instead of ‘*”

ORA-01078 ORA-29701 error during startup ASM
by Vazha Mantua Monday, January 16, 2012 11:48 AM

Good Day,

Today we resolve problem with error ORA-01078 and ORA-29701 while starting ASM , Oracle release 11gR2.

-bash-3.2$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 16 11:22:15 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-01078: failure in processing system parameters
ORA-29701: unable to connect to Cluster Synchronization Service

 

1.First of all check services by command

crs_stat –t

Than try start services manually by command:

crsctl start resource ora.cssd

-bash-3.2$ crsctl start resource ora.cssd
CRS-2672: Attempting to start 'ora.cssd' on 'Test'
CRS-2679: Attempting to clean 'ora.diskmon' on 'Test'
CRS-2681: Clean of 'ora.diskmon' on 'Test' succeeded
CRS-2672: Attempting to start 'ora.diskmon' on 'Test'
CRS-2676: Start of 'ora.diskmon' on 'Test' succeeded
CRS-2676: Start of 'ora.cssd' on 'Test' succeeded

2.Now try to start ASM instance

-bash-3.2$ export ORACLE_SID=+ASM
-bash-3.2$ sqlplus / as sysasm

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jan 16 11:26:08 2012

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup
ASM instance started

Total System Global Area 283930624 bytes
Fixed Size 2212656 bytes
Variable Size 256552144 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

3.Finally mount disk group : Find problematic diskgroup with statement:

select name, state, type from v$asm_diskgroup

SQL> alter diskgroup DATA mount;

Filter by APML

Calendar

<<  December 2017  >>
MoTuWeThFrSaSu
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567

View posts in large calendar

TextBox