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: , , , , , ,

Flush Single SQL statement from shared pool
by vazha mantua Monday, September 16, 2013 4:04 PM

Sometime on database we observed SQL statements which change SQL_Plans, cause of this may be various situation, for example recalculation  table statistics, or bind mismatch, etc. Sometime we simple want to delete bad SQL_Plan from shared pool, of course we can do it with flushing whole shared pool, but the best way is flushing single SQL statement.

First of all we should find address and hash value of statement, with this command:

select address||','||hash_value
  from v$sqlarea
  where sql_id like '1u1hcw5c5t8r3';

after that, we copy results to argument to procedure sys.dbms_shared_pool.purge

Example

begin
   sys.dbms_shared_pool. purge('00000004FA8BA948,1482466019','C',1);
end;  

That’s all.

Tags:

ORA-39002: invalid operation ORA-39070: Unable to open the log file
by vazha mantua Tuesday, December 18, 2012 2:30 PM

Hello All,

 

Today we resolved problem with error

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 536
ORA-29283: invalid file operation

This error we got when use ASM file system as directory. expdp can’t create log file on ASM disk storage.

For avoiding creating expdp log file on ASM use second directory with standard  file system

Solution :

create directory EXPDP_LOG as '/u0/app/oracle/logs'

and use expdp parameter LOGFILE=EXPDP_LOG :exp_dbh.log

Deploy jboss application server on oracle enterprise manager
by vazha mantua Wednesday, October 10, 2012 10:57 AM

Good Day,

Today we will observe how can we add jboss applications on OEM targets. Beginning with Grid Control OMS 10.2.0.3 and Grid Control Agent 10.2.0.3, the Third Party Plug-in for JBoss monitoring is available.

Supported version of jboss - JBoss are 4.0.x and above:  Supported version of OEM agent: 10.2.0.3 or higher, it will be perfect if you use version 11g

So what we did:

1. Install OEM agent 11g on target server , where is installed jboss service

2. Open  JNDI Port of JBoss Application Server on  the Agent Host. JNDI port is 1099

3.The Management ejb (MEJB) must have been deployed to JBoss Application Server, for this task you should find ejb-management.jar file under <JBoss_HOME>/docs/examples/jmx and  copy the jar file to the deploy folder of the running JBoss Application Server.

After this procedure go to EOM on Target Page<Middleware. and chose Jboss Apliication Server and fill following form:

jboss

 

You should fill server host,Install home and JNDI port(1099). After that you should add credential of server and OEM automatic find appropriate jboss service.

 

Most popular issue after deploying is metric error like this : Java Virtual Machine context could not be obtained. Cause of this issues is  memory parameters are not enough for resource usage.

Solution:

1.Stop OEM agent

2.Backup and edit agent11g/sysman/config/emd.properties file

3.Change from :
                           agentJavaDefines=-Doracle.dms.refresh.wait.time=1000 -DUrlTiming.UseJSSE=true-Dnetworkaddress.cache.ttl=1800 -Djava.awt.headless=true
                           to:
                           agentJavaDefines=-Xms256m -Xmx512m -Doracle.dms.refresh.wait.time=1000-DUrlTiming.UseJSSE=true -Dnetworkaddress.cache.ttl=1800-Djava.awt.headless=true

4. Start OEM agent

 

And Enjoy with OEM!

 

 

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!

Filter by APML

Calendar

<<  September 2016  >>
MoTuWeThFrSaSu
2930311234
567891011
12131415161718
19202122232425
262728293012
3456789

View posts in large calendar

TextBox