[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/product/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Creating trace directory
/u01/app/grid/product/11.2.0/grid/bin/clscfg.bin: error while loading shared libraries: libcap.so.1: cannot open shared object file: No such file or directory
Failed to create keys in the OLR, rc = 127, Message:
Failed to write the checkpoint:'' with status:FAIL.Error code is 256
Failed to create keys in the OLR at /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_lib.pm line 7497.
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed
[root@vmxdb05b mnt]# lsb_release -a
LSB Version: :core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS Linux release 6.0 (Final)
Release: 6.0
Codename: Final
[root@vmxdb05b mnt]# yum search libcap
Loaded plugins: fastestmirror, refresh-packagekit
Loading mirror speeds from cached hostfile
* base: mirror.averse.net
* extras: mirror.averse.net
* updates: mirror.averse.net
=========================================================== Matched: libcap ============================================================
libcap-ng-devel.i686 : Header files for libcap-ng library
libcap-ng-devel.x86_64 : Header files for libcap-ng library
libcap-ng-python.x86_64 : Python bindings for libcap-ng library
libcap-devel.i686 : Development files for libcap
libcap-devel.x86_64 : Development files for libcap
libcap-ng.i686 : An alternate posix capabilities library
libcap-ng.x86_64 : An alternate posix capabilities library
libcap-ng-utils.x86_64 : Utilities for analysing and setting file capabilities
compat-libcap1.i686 : Library for getting and setting POSIX.1e capabilities
compat-libcap1.x86_64 : Library for getting and setting POSIX.1e capabilities
libcap.i686 : Library for getting and setting POSIX.1e capabilities
libcap.x86_64 : Library for getting and setting POSIX.1e capabilities
[root@vmxdb05b mnt]# yum install libcap.x86_64
[root@vmxdb05b mnt]# yum install compat-libcap1.x86_64 -y
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/product/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Improper Oracle Grid Infrastructure configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Grid Infrastructure
run '/u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig'
to configure existing failed configuration and then rerun root.sh
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Oracle Restart stack is not active on this node
Restart the SIHA stack (use /u01/app/grid/product/11.2.0/grid/bin/crsctl start has) and retry
Failed to write the checkpoint:'' with status:FAIL.Error code is 256
Failed to verify HA resources
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/product/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
Improper Oracle Grid Infrastructure configuration found on this host
Deconfigure the existing cluster configuration before starting
to configure a new Grid Infrastructure
run '/u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig'
to configure existing failed configuration and then rerun root.sh
/u01/app/grid/product/11.2.0/grid/perl/bin/perl -I/u01/app/grid/product/11.2.0/grid/perl/lib -I/u01/app/grid/product/11.2.0/grid/crs/install /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl execution failed
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/crs/install/roothas.pl -deconfig -force
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Stop failed, or completed with errors.
CRS-4639: Could not contact Oracle High Availability Services
CRS-4000: Command Delete failed, or completed with errors.
CRS-4544: Unable to connect to OHAS
CRS-4000: Command Stop failed, or completed with errors.
Failure in execution (rc=-1, 0, No such file or directory) for command /etc/init.d/ohasd deinstall
Successfully deconfigured Oracle Restart stack
[root@vmxdb05b mnt]# /u01/app/grid/product/11.2.0/grid/root.sh
Performing root user operation for Oracle 11g
The following environment variables are set as:
ORACLE_OWNER= grid
ORACLE_HOME= /u01/app/grid/product/11.2.0/grid
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /u01/app/grid/product/11.2.0/grid/crs/install/crsconfig_params
LOCAL ADD MODE
Creating OCR keys for user 'grid', privgrp 'oinstall'..
Operation successful.
LOCAL ONLY MODE
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
CRS-4664: Node vmxdb05b successfully pinned.
Adding Clusterware entries to upstart
vmxdb05b 2011/11/25 00:13:25 /u01/app/grid/product/11.2.0/grid/cdata/vmxdb05b/backup_20111125_001325.olr
Successfully configured Oracle Grid Infrastructure for a Standalone Server
Friday, November 25, 2011
Sunday, November 13, 2011
Query table status (num of rows, total size, used_size, etc)
select p.*, a.* from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
and p.object_id=object_id('Person.Password');
select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number;
select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by total_pages_mb desc;
-- order by p.rows desc;
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
and p.object_id=object_id('Person.Password');
select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number;
select s.name [schema],
t.name [table name],
i.name [index name],
i.index_id,i.type_desc,p.partition_number,p.rows,
cast(sum(a.total_pages)*1.0*8/1024 as decimal(6,2)) total_pages_mb,
cast(sum(a.used_pages)*1.0*8/1024 as decimal(6,2)) used_pages_mb,
cast(sum(a.data_pages)*1.0*8/1024 as decimal(6,2)) data_pages_mb
from
sys.partitions p
inner join sys.allocation_units a
on p.partition_id=a.container_id
inner join sys.tables t
on p.object_id=t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
inner join sys.indexes i
on p.object_id=i.object_id
and p.index_id=i.index_id
where 1=1
--and p.object_id=object_id('Person.Password')
group by
s.name,t.name,i.name,i.index_id,i.type_desc,p.partition_number,p.rows
order by total_pages_mb desc;
-- order by p.rows desc;
Working with SQL Server fragmentation
use AdventureWorks2008R2;
go
select * from
(select * from sys.dm_db_index_physical_stats(db_id('AdventureWorks2008R2'),null,null,null,null)) t
where t.avg_fragmentation_in_percent> 30
GO
use AdventureWorks2008R2;
go
select * from sys.dm_db_index_physical_stats(db_id(),414624520,null,null,null)
GO
select * from sys.indexes where object_id=414624520 and index_id=1;
select * from sys.tables where object_id=414624520;
select * from sys.schemas where schema_id=9;
alter index PK_SpecialOfferProduct_SpecialOfferID_ProductID on Sales.SpecialOfferProduct rebuild with (ONLINE=on);
GO
go
select * from
(select * from sys.dm_db_index_physical_stats(db_id('AdventureWorks2008R2'),null,null,null,null)) t
where t.avg_fragmentation_in_percent> 30
GO
use AdventureWorks2008R2;
go
select * from sys.dm_db_index_physical_stats(db_id(),414624520,null,null,null)
GO
select * from sys.indexes where object_id=414624520 and index_id=1;
select * from sys.tables where object_id=414624520;
select * from sys.schemas where schema_id=9;
alter index PK_SpecialOfferProduct_SpecialOfferID_ProductID on Sales.SpecialOfferProduct rebuild with (ONLINE=on);
GO
Tuesday, November 8, 2011
T-SQL code to enable SQL Logins to create agent job
USE [msdb]
GO
CREATE USER [agentoper] FOR LOGIN [agentoper] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [agentoper]
GO
GO
CREATE USER [agentoper] FOR LOGIN [agentoper] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [agentoper]
GO
Saturday, November 5, 2011
Get help to use DBCC in SQL server
dbcc help ('?');
go
================================================================================================
checkalloc
checkcatalog
checkconstraints
checkdb
checkfilegroup
checkident
checktable
cleantable
dbreindex
dropcleanbuffers
free
freeproccache
freesessioncache
freesystemcache
help
indexdefrag
inputbuffer
opentran
outputbuffer
pintable
proccache
show_statistics
showcontig
shrinkdatabase
shrinkfile
sqlperf
traceoff
traceon
tracestatus
unpintable
updateusage
useroptions
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc help ('checkdb');
go
================================================================================================
dbcc checkdb
(
{ 'database_name' | database_id | 0 }
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)
[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
[ , [ DATA_PURITY ] ]
[ , [ EXTENDED_LOGICAL_CHECKS ] ]
}
]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc help (sqlperf);
go
================================================================================================
dbcc sqlperf ( LOGSPACE ) [ WITH NO_INFOMSGS ]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc sqlperf ( LOGSPACE )
================================================================================================
Database Name Principal Filegroup Name Log Size (MB) Log Space Used (%) Status
-------------------------- -------------------------- ------------- ------------------ -----------
master PRIMARY 0.7421875 61.57895 0
tempdb PRIMARY 0.9921875 51.08268 0
model PRIMARY 0.7421875 46.05263 0
msdb PRIMARY 0.7421875 58.28947 0
ReportServer$PROD PRIMARY 6.867188 19.17662 0
ReportServer$PRODTempDB PRIMARY 1.054688 37.03704 0
AdventureWorks2008R2 PRIMARY 1.492188 39.39791 0
AdventureWorksDW2008R2 PRIMARY 0.484375 80.04032 0
TESTDB PRIMARY 0.9921875 37.45079 0
(9 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
http://msdn.microsoft.com/en-us/library/ms176040.aspx
go
================================================================================================
checkalloc
checkcatalog
checkconstraints
checkdb
checkfilegroup
checkident
checktable
cleantable
dbreindex
dropcleanbuffers
free
freeproccache
freesessioncache
freesystemcache
help
indexdefrag
inputbuffer
opentran
outputbuffer
pintable
proccache
show_statistics
showcontig
shrinkdatabase
shrinkfile
sqlperf
traceoff
traceon
tracestatus
unpintable
updateusage
useroptions
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc help ('checkdb');
go
================================================================================================
dbcc checkdb
(
{ 'database_name' | database_id | 0 }
[ , NOINDEX
| { REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST
| REPAIR_REBUILD
} ]
)
[ WITH
{
[ ALL_ERRORMSGS ]
[ , [ NO_INFOMSGS ] ]
[ , [ TABLOCK ] ]
[ , [ ESTIMATEONLY ] ]
[ , [ PHYSICAL_ONLY ] ]
[ , [ DATA_PURITY ] ]
[ , [ EXTENDED_LOGICAL_CHECKS ] ]
}
]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc help (sqlperf);
go
================================================================================================
dbcc sqlperf ( LOGSPACE ) [ WITH NO_INFOMSGS ]
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
================================================================================================
dbcc sqlperf ( LOGSPACE )
================================================================================================
Database Name Principal Filegroup Name Log Size (MB) Log Space Used (%) Status
-------------------------- -------------------------- ------------- ------------------ -----------
master PRIMARY 0.7421875 61.57895 0
tempdb PRIMARY 0.9921875 51.08268 0
model PRIMARY 0.7421875 46.05263 0
msdb PRIMARY 0.7421875 58.28947 0
ReportServer$PROD PRIMARY 6.867188 19.17662 0
ReportServer$PRODTempDB PRIMARY 1.054688 37.03704 0
AdventureWorks2008R2 PRIMARY 1.492188 39.39791 0
AdventureWorksDW2008R2 PRIMARY 0.484375 80.04032 0
TESTDB PRIMARY 0.9921875 37.45079 0
(9 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
http://msdn.microsoft.com/en-us/library/ms176040.aspx
Friday, November 4, 2011
Different termination options to set sql server in single_user/offline mode
ALTER DATABASE [TestDB] SET
SINGLE_USER
-- RESTRICTED_USER
-- OFFLINE
WITH
-- ROLLBACK AFTER 5 SECONDS
ROLLBACK IMMEDIATE
-- NO_WAIT;
SINGLE_USER
-- RESTRICTED_USER
-- OFFLINE
WITH
-- ROLLBACK AFTER 5 SECONDS
ROLLBACK IMMEDIATE
-- NO_WAIT;
Subscribe to:
Posts (Atom)