Restoring, if datafile was added during backup

Backup
STARTED AT: [2013-10-17 06:25:21 UTC]
DONE AT  : [2013-10-17 18:31:21 UTC]


New datafiles added
FILE# CREATION_TIME
162 10/17/2013 3:08:54 PM
161 10/17/2013 2:49:48 PM
160 10/17/2013 2:48:05 PM
159 10/17/2013 2:45:46 PM
158 10/17/2013 2:44:09 PM
157 10/17/2013 2:41:24 PM
156 10/11/2012 6:29:26 PM
155 10/02/2012 7:42:29 PM
154 10/02/2012 7:00:39 PM



The restore datafile from TAG incr0_DBNAME_131017_062729 doesn’t work.

->restore database from tag 'incr0_DBNAME_131017_062729';

Starting restore at 19-OCT-2013 02:32:08

released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/19/2013 02:32:28
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 162 found to restore
RMAN-06023: no backup or copy of datafile 161 found to restore
RMAN-06023: no backup or copy of datafile 160 found to restore
RMAN-06023: no backup or copy of datafile 159 found to restore
RMAN-06023: no backup or copy of datafile 158 found to restore
RMAN-06023: no backup or copy of datafile 157 found to restore



The set until time to end of backup doesn’t work either.

->Set Until Time '17-Oct-2013 18:31:21';
->Restore Database;

Starting restore at 19-OCT-2013 03:20:17

released channel: t1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 10/19/2013 03:20:36
RMAN-06085: must use SET NEWNAME command to restore datafile 157

Recovery Manager complete.




But set until time to begin of backup works.


#use begin backup time
->Set Until Time '17-Oct-2013 06:25:21';
->Restore Database;


… works fine ….

Table Reorganization (reclaim space)

This shows that "alter table x move" reclaims the space.
As seen here in: http://www.dba-oracle.com/t_alter_table_move_shrink_space.htm
It says it doesn't change the table size.


SQL> begin
2 for i in 1..1000000 loop
3 insert into audadm.t1 values (i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> analyze table audadm.t1 compute statistics;

Table analyzed.

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
2276 1819 1500000

SQL> select blocks, extents from dba_segments where segment_name='T1';

BLOCKS EXTENTS
---------- ----------
4096 2

SQL> select sum(bytes) from dba_segments where segment_name = 'T1';

SUM(BYTES)
----------
33554432

SQL> delete from audadm.t1 where mod(x,2)=0;

500000 rows deleted.

SQL> commit;

Commit complete.

SQL> analyze table audadm.t1 compute statistics;

Table analyzed.

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
2276 1819 1000000

SQL> select blocks, extents from dba_segments where segment_name='T1';

BLOCKS EXTENTS
---------- ----------
4096 2

SQL> select sum(bytes) from dba_segments where segment_name = 'T1';

SUM(BYTES)
----------
33554432

SQL> alter table audadm.t1 move;

Table altered.

SQL> analyze table audadm.t1 compute statistics;

Table analyzed.

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T1';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
1520 527 1000000

SQL> select blocks, extents from dba_segments where segment_name='T1';

BLOCKS EXTENTS
---------- ----------
2048 1

SQL> select sum(bytes) from dba_segments where segment_name = 'T1';

SUM(BYTES)
----------
16777216

SQL>

Table Reorganization

SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t
2 as
3 select * from all_objects;

Table created.

SQL> insert into t select * from t;

75458 rows created.

SQL> insert into t select * from t;

150916 rows created.

SQL> commit;

Commit complete.

SQL> create index idx_t on t(OBJECT_ID);

Index created.

Index is valid:

SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';

INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL VALID

SQL> set linesize 200;
SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 632 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 632 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=9)

Note
-----
- dynamic sampling used for this statement (level=2)

SQL> set autotrace off;

SQL> analyze table t compute statistics;

Table analyzed.

Check the HWM:

SQL> select blocks, extents from dba_segments where segment_name='T';

BLOCKS EXTENTS
---------- ----------
4352 49

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832


SQL> delete from t where mod(OBJECT_ID,2)=0;

150920 rows deleted.

Issue the move command to lower high water mark:

SQL> alter table t move;

Table altered.

Index will become unusable, need to rebuild:

SQL>
SQL> select index_name,index_type,status
2 from user_indexes
3 where index_name='IDX_T';

INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
IDX_T NORMAL UNUSABLE

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 1167 (1)| 00:00:15 |
|* 1 | TABLE ACCESS FULL| T | 4 | 400 | 1167 (1)| 00:00:15 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("OBJECT_ID"=9)

SQL> set autotrace off;

High water mark gets lowered:

SQL> select blocks, extents from dba_segments where segment_name='T';

BLOCKS EXTENTS
---------- ----------
2176 32

SQL> select blocks,empty_blocks,num_rows from dba_tables where table_name='T';

BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4292 59 301832

Rebuild index:

SQL> alter index idx_t rebuild;

Index altered.

SQL> set autotrace traceonly explain;
SQL> select * from t where object_id=9;

Execution Plan
----------------------------------------------------------
Plan hash value: 1594971208

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 400 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 400 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T | 4 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("OBJECT_ID"=9)

SQL> set autotrace off;





Aside, when trying to shrink table, it complains that tablespace segment type is wrong.
Looks like it will work if segment type is AUTO.


SQL> alter table t enable row movement ;

Table altered.

SQL> alter table t shrink space cascade;
alter table t shrink space cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> alter table t disable row movement ;

Table altered.

SQL>

Installing Oracle 9.2.0.4 on linux 64-bit problems

While installing the oracle binaries for linux 64-bit, 9.2.0.4, the installer hangs at about 17%.

It hangs on the file: naeet.o

I followed another source from user CharlesC.

---
1. Make sure Java libraries are up to date..
2. Do not start installation from the 9204 base installation- Oracle Universal Installer has problem
2.1 Install first the OUI "only" from the 9206 patchset (p3948480_9206_Linux-x86-64.zip)
using the Oracle_Home youve just decided
2.2 In ../Disk1/install/oraparam.ini edit the Linux value under [Certified Versions]
Linux=redhat-2.1AS,redhat-2.1,redhat-3,redhat-4,redhat-5,UnitedLinux-1.0,SuSE-9,SuSE-
* value must include redhat-5 inorder to suppress the error message for OS version incompat.
2.3 After completion of OUI install, edit again ../product/9204/oui/oraparam.ini as instructed
in step 2.2
2.4 Now install the 9204 base using the OUI from 9206. Run ../product/9204/oui/runInstaller BUT
this time, select the "products.jar" from 9204 base installation set.
(ex: /u01/app/oracle/Disk1/stage/products.jar)
2.5 From here u should be able to install the SOFTWARE ONLY option. DO NOT create a database yet.
---

Another problem I saw relates to launching x-windows.

Initializing Java Virtual Machine from /tmp/OraInstall2011-08-16_02-41-34PM/jre/bin/java. Please wait...
Exception in thread "main" java.lang.UnsatisfiedLinkError: /tmp/OraInstall2011-08-16_02-41-34PM/jre/lib/i386/libawt.so: libXp.so.6: cannot open shared object file: No such file or directory

Solve it be installing following packages:

libX11.i686
libXext.i686
libXt.i686
xorg-x11-deprecated-libs-6.8.1-12.i386.rpm
libXtst.i686

Starting database resource

[grid@rac2 ~]$ crsctl status resource

NAME=ora.hongkong.db
TYPE=ora.database.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on rac1, OFFLINE

[grid@rac2 ~]$ crsctl start resource -all

CRS-2672: Attempting to start 'ora.hongkong.db' on 'rac2'
ORA-01012: not logged on
CRS-2676: Start of 'ora.hongkong.db' on 'rac2' succeeded

[grid@rac2 ~]$ crsctl status resource ora.hongkong.db

NAME=ora.hongkong.db
TYPE=ora.database.type
TARGET=ONLINE , ONLINE
STATE=ONLINE on rac1, ONLINE on rac2

rac2 troubles

I had MANY problems with rac2 and spent hours installing, reinstalling grid. Here are some of the issues I encountered.

After installing the OS, and configuring the storage and asm, make sure you can see the disks after a clean reboot on all nodes. Especially on your rac2.

[root@rac2 crs]# oracleasm listdisks
CRSVOL
DATAVOL
FRAVOL

When grid has been installed, after a reboot, make sure you can see the asm disks (above). And also make sure the ocrcheck is valid.

[root@rac2 bin]# ./ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 3
Total space (kbytes) : 262120
Used space (kbytes) : 2252
Available space (kbytes) : 259868
ID : 2022892366
Device/File Name : +CRSDG
Device/File integrity check succeeded

Device/File not configured

Device/File not configured

Device/File not configured

Device/File not configured

Cluster registry integrity check succeeded

Logical corruption check succeeded

[root@rac2 bin]#



Other issues are as follows.

symptom:
while running root.sh during installation, we get
/u01/app/11.2.0/grid/lib/libclntsh.so.11.1: cannot restore segment prot after reloc: Permission denied

fix:
deconfig, and run root.sh again
su - root (do this even if you are already root)
/u01/app/11.2.0/grid/crs/install/rootcrs.pl -verbose -deconfig -force
chcon -t textrel_shlib_t /u01/app/11.2.0/grid/lib/libclntsh.so.11.1
/u01/app/11.2.0/grid/root.sh


symptom:
after a reboot of rac2,
[root@rac2 alert]# cat /u01/app/grid/diag/asm/+asm/+ASM2/trace/+ASM2_ora_4644.trc

...
----- Error Stack Dump -----
ORA-27154: post/wait create failed
ORA-27300: OS system dependent operation:semget failed with status: 28
ORA-27301: OS failure message: No space left on device
ORA-27302: failure occurred at: sskgpsemsper

fix:
vi /etc/sysctl.conf
modify: kernel.sem = 250 100 -> kernel.sem = 250 32000 100
sysctl -p
reboot

stopping CRS

rac1

[root@rac1 bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac1'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac1'
CRS-2673: Attempting to stop 'ora.rac1.vip' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac1'
CRS-2673: Attempting to stop 'ora.CRSDG.dg' on 'rac1'
CRS-2677: Stop of 'ora.rac1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'rac1'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac1'
CRS-2677: Stop of 'ora.scan1.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.rac2.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.CRSDG.dg' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'rac1'
CRS-2673: Attempting to stop 'ora.eons' on 'rac1'
CRS-2677: Stop of 'ora.ons' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac1'
CRS-2677: Stop of 'ora.net1.network' on 'rac1' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac1' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac1' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac1'
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac1'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac1'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac1'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac1'
CRS-2673: Attempting to stop 'ora.asm' on 'rac1'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac1' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac1'
CRS-2677: Stop of 'ora.cssd' on 'rac1' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac1'
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac1'
CRS-2677: Stop of 'ora.gipcd' on 'rac1' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'rac1' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac1' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rac1 bin]#





rac2

[root@rac2 bin]# ./crsctl stop crs
CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.crsd' on 'rac2'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'rac2'
CRS-2673: Attempting to stop 'ora.CRSDG.dg' on 'rac2'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'rac2'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.rac2.vip' on 'rac2'
CRS-2677: Stop of 'ora.rac2.vip' on 'rac2' succeeded
CRS-2672: Attempting to start 'ora.rac2.vip' on 'rac1'
CRS-2676: Start of 'ora.rac2.vip' on 'rac1' succeeded
CRS-2677: Stop of 'ora.CRSDG.dg' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.eons' on 'rac2'
CRS-2673: Attempting to stop 'ora.ons' on 'rac2'
CRS-2677: Stop of 'ora.ons' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'rac2'
CRS-2677: Stop of 'ora.net1.network' on 'rac2' succeeded
CRS-2677: Stop of 'ora.eons' on 'rac2' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'rac2' has completed
CRS-2677: Stop of 'ora.crsd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'rac2'
CRS-2673: Attempting to stop 'ora.cssdmonitor' on 'rac2'
CRS-2673: Attempting to stop 'ora.ctssd' on 'rac2'
CRS-2673: Attempting to stop 'ora.evmd' on 'rac2'
CRS-2673: Attempting to stop 'ora.asm' on 'rac2'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'rac2'
CRS-2677: Stop of 'ora.cssdmonitor' on 'rac2' succeeded
CRS-2677: Stop of 'ora.gpnpd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.evmd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.asm' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'rac2'
CRS-2677: Stop of 'ora.cssd' on 'rac2' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'rac2'
CRS-2673: Attempting to stop 'ora.gipcd' on 'rac2'
CRS-2677: Stop of 'ora.gipcd' on 'rac2' succeeded
CRS-2677: Stop of 'ora.diskmon' on 'rac2' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'rac2' has completed
CRS-4133: Oracle High Availability Services has been stopped.
[root@rac2 bin]#