Archive

Author Archive

Oracle Flashback Database

February 11, 2019 Leave a comment

SQL> !date
Tue Feb  5 22:45:23 GMT 2019
SQL> show user
USER is “SYS”
SQL> select name from v$database;
NAME
———
RBTEST

Create a pre-test restore point (not necessary to create a guarantee restore point, a normal one will age out automatically according to your flashback retention policy)

SQL> create restore point flashback_test;
Restore point created.

SQL> SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,        GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM V$RESTORE_POINT;
NAME           SCN         TIME                           DATABASE_INCARNATION# GUA STORAGE_SIZE
————– ———– —————————— ——————— — ————
FLASHBACK_TEST 9.7812E+12  05-FEB-19 23.06.40.000000000   2                     NO  0

SQL> SELECT CURRENT_SCN
FROM   V$DATABASE;
CURRENT_SCN
———–
9.7812E+12

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME
FROM   V$FLASHBACK_DATABASE_LOG;

OLDEST_FLASHBACK_SCN OLDEST_FL
——————– ———
9.7812E+12 01-FEB-19

Create dummy table

SQL> create table test (
emp Number(5) primary key,
ename varchar2(15) not null)
tablespace users
storage (initial 50k);

Table created.

SQL> insert into test values (1, ‘henry’);
1 row created.

SQL> commit;
Commit complete.

Create a restore point

SQL> create restore point before_drop;
Restore point created.

Check restore point is in RMAN

RMAN> list restore point all;
SCN              RSP Time  Type       Time      Name
—————- ——— ———- ——— —-
9781206080522                         05-FEB-19 FLASHBACK_TEST
9781206111481                         06-FEB-19 BEFORE_DROP

Drop table

SQL> drop table test;
Table dropped.

SQL> select * from test;
select * from test
*

ERROR at line 1:
ORA-00942: table or view does not exist

Shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup mount

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.5032E+10 bytes
Fixed Size                  5374736 bytes
Variable Size            3590327536 bytes
Database Buffers         1.1409E+10 bytes
Redo Buffers               28176384 bytes
Database mounted.

Connect to RMAN

RMAN> connect target;
connected to target database: RBTEST (DBID=4170631130, not open)

Flashback the database to restore the table

RMAN> flashback database to restore point before_drop;
Starting flashback at 06-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=331 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=362 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=392 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 8.1.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=422 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 8.1.0.0

starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 06-FEB-19

Open the database in read only mode

SQL> alter database open read only;
Database altered.

Check table restored

SQL> select * from test;

EMP ENAME
———- —————
1 henry

Test worked successfully, now we can restore the database to before the test

Shutdown the database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Startup mount

SQL> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.5032E+10 bytes
Fixed Size                  5374736 bytes
Variable Size            3590327536 bytes
Database Buffers         1.1409E+10 bytes
Redo Buffers               28176384 bytes
Database mounted.

Connect to RMAN

RMAN> connect target;
connected to target database: RBTEST (DBID=4170631130, not open)

Flashback the database to the restore point

RMAN> flashback database to restore point flashback_test;
Starting flashback at 06-FEB-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=331 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=362 device type=DISK
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: SID=392 device type=SBT_TAPE
channel ORA_SBT_TAPE_1: Data Protection for Oracle: version 8.1.0.0
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: SID=422 device type=SBT_TAPE
channel ORA_SBT_TAPE_2: Data Protection for Oracle: version 8.1.0.0

starting media recovery
media recovery complete, elapsed time: 00:00:15
Finished flashback at 06-FEB-19

Open the database in read only mode

SQL> alter database open read only;
Database altered.

Check if table is available

SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-00942: table or view does not exist

Database has been restored to before the test.

Restart the database to put it into read/write mode

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.5032E+10 bytes
Fixed Size                  5374736 bytes
Variable Size            3590327536 bytes
Database Buffers         1.1409E+10 bytes
Redo Buffers               28176384 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SQL> alter database open resetlogs;
Database altered.

Drop flashback test restore points

SQL> drop restore point before_drop;
Restore point dropped.

SQL> drop restore point flashback_test;
Restore point dropped.

Advertisements

RMAN-20001: target database not found in recovery catalog

February 11, 2019 Leave a comment

released channel: oem_sbt_backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 02/11/2019 14:36:05
RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

We were seeing the above error after refreshing the database from the live environment to test.

This is caused by the target database not being registered to the catalog.

To resolve you need to connect to the catalog and register the target database:

RMAN> connect to target;
RMAN> connect catalog;
RMAN> register database;

 

Categories: Errors, RMAN

Perl – Writing to file outputting in UTF-16 instead of UTF-8

September 16, 2011 2 comments

Recently I needed to write a perl script that ran on Cygwin. My default setting means that any files written by perl were being written in UTF-16. This led to what appeared to be a lot of Japanese writing, making it completely illegible and unusable.

After a lot of digging around on the internet, I managed to hack together the following code:

open my $SH, ">>:raw:encoding(UTF16-LE):crlf:utf8", "test1.txt";;
print $SH "\x{FEFF}";
print "Some test writing \n";
close ($SH);

This code tells perl we’re going to pass “characters” to this file handle instead of bytes. Next transform \n into \r\n to give DOS line endings. Next apply the UTF16-LE, so that 0x0A becomes 0x0A 0x00. This stops perl writing a byte order mark (BOM) at the beginning of the file. Finally, the raw:encoding removes the default ctrf so that it is not in the wrong place.

Now the file is being opened with the correct coding, we need to write the BOM to the beginning of the file to tell readers of this file what endianness it is. We do this by printing \x{FFF} to the file.

Oracle – Find consuming sql from process id

September 5, 2011 4 comments

If you find an oracle process is consuming a high amount of CPU, you can find the sql_text that is related to that process id using the following code:

SELECT SQL_TEXT
FROM v$sql
WHERE sql_id = (SELECT sql_id FROM v$session WHERE paddr = (sELECT addr
FROM v$process
WHERE spid = '&process_id'));

You will need to find the process id using either top (linux), topas -P (aix), or task manager (windows). Equally you can use Enterprise Manager to find this information, by looking under the performance monitor and any alerts regarding tuning / high CPU consumption.

Oracle – Disable dbms gather_stats_job or optimizer stats


To disable gather_stats_job in 10g:

BEGIN
DBMS_SCHEDULER.DISABLE('GATHER_STATS_JOB');
END;
/

To disable gather_stats_job (also known as optimizer stats) in 11g:

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;
/

Oracle – RMAN Restore Database


If you have a current controlfile, spfile, but all datafiles have been lost or damaged, then you must restore and recover the entire database.

RMAN> STARTUP MOUNT

You can use the show all command to check which channels are configured for access to backup devices. If none are configured then you will need to manually allocate one or more channels.

RMAN> SHOW ALL

Restore the database using the restore database command:

RMAN> RESTORE DATABASE;

If you have any read-only tablespaces you will need to force RMAN to restore any missing datafiles belonging to them. This is because RMAN typically skips read-only tablespaces on a restore.

To do this you can issue the following instead of the restore command above:

RMAN> RESTORE DATABASE CHECK READONLY;

Recover the database using the recover database command:

RMAN> RECOVER DATABASE;

NB. You can also recover the database appending delete archivelog maxsize <number> this will delete archivelogs after they have been applied and will prevent them from being bigger than the maxsize indicated. It should be noted that if the maxsize of the archivelogs is greater than the size given here, then the statement will give an error and you will need to increase the maxsize in the statement.

Once you have successfully recovered the database, you can open it from RMAN:

RMAN> ALTER DATABASE OPEN;

Further Reading

Basic Database Restore and Recovery Scenarios – Oracle Documentation 10.1

Oracle – Find View Source Code

August 21, 2011 1 comment

If you wish to find the source code for a view, you can look in the user_views table:

SQL> SELECT view_name, text FROM user_views;

You can check the v$fixed_view_definition table to check the definition of fixed views (views starting with v$).

SQL> SELECT view_name, view_definition FROM v$fixed_view_definition;

Further Reading

http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/dynviews_1109.htm