How Grid Repository Stores Data?
The agents upload data at MGMT_METRIC_RAW table which is retained for 7 days.
Raw data are aggregated by Grid Control based on a predefined policy into 1-hour records, stored in MGMT_METRIC_1HOUR.
After 31 days or a month, 1-hour records in MGMT_METRIC_1HOUR are subject for further aggregation that rolls into 1-day records.
These 1-day records are stored in MGMT_METRIC_1Day for 365 days or 1 year before purged by Grid Control.
To view SQL Server error log from SQL Server Enterprise Manager
- Expand a server group, and then expand a server.
- Expand Management, and then expand SQL Server Logs.
- Click the SQL Server Log to view it. Error log information appears in the details pane
Step 1. Find Size of Database Log files
SELECT DB_NAME(database_id) AS DatabaseName,
Name AS Logical_Name,
Physical_Name, (size*8)/1024 SizeMB
WHERE DB_NAME(database_id) like ‘%AdventureWorks%’
and name like ‘%Log%’
order by SizeMB desc
Fixed database roles can be mapped to the more detailed permissions that are included in SQL Server.
perl GetMail_.pl returned
Can’t locate Email/MIME.pm in @INC (@INC contains: C:/Perl64/site/lib C:/Perl64/
lib .) at GetMail_.pl line 6.
BEGIN failed–compilation aborted at GetMail_.pl line 6.
SQL Server comes with pre-defined error messages that can be raised when a particular condition occurs. Built-in error messages are stored in the sysmessages table of the master database. Depending on the severity level, error messages can be returned to the offending connection or could be recorded in the SQL Server error log. The severity level determines the type and seriousness of the problem that caused the error. The following table summarizes various error severity levels: Read more…
SQLDIAG.exe executable can be found in the SQL Server installation directory under the BINN folder. SQLDIAG records the SQL Server configuration, contents of the error logs (current, as well as archived logs), and Windows configuration including registry keys. If SQL Server is running when SQLDIAG is executed, it will record the output of the following routines: Read more…
I have number of SQL server databases and users . I am in a need to create read only access to users who wants to look at data. Read more…
Often I have to copy number of SQL server database structures from prod system for testing. It’s difficult to get rid of all objects within a database manually. Following script will do just about that.
- select @@VERSION” OR
- SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’) ” Or
- Simply look at the value in the Management Studio Explorer
sp_configure is a tool to display and change SQL Server settings. Please be aware that changing these settings can dramatically affect your instance.
If you are looking for a good TSQL packaged scripts which will do usual database maintenance activities then you are on right page. I have detailed 2 such projects which will help you to setup maintenance job in less than an hour.
We can only configure one default instance under SQL server installation. If we need additional instance it should be a named instance.
1. How to locate if I am connected to a SQL server cluster?
The reason for the ORA-24082 error is that a message has been enqueued onto the AQ$_PROP_NOTIFY_<N> queue to notify the job_queue_process associated with the propagation schedule to stop propagating messages and the
job_queue_process has failed to acknowledge that message within a reasonable amount of time. Read more…
The state of a capture process describes what the capture process is doing currently. You can view the state of a capture process by querying the STATE column in the V$STREAMS_CAPTURE dynamic performance view. The following capture process states are possible
|streams$_capture_process||Lists all defined capture processes|
|dba_capture||Basic status, error info|
|v$streams_capture||Detailed status info|
|streams$_propagation_process:||Lists all defined propagate processes|
|dba_propagation||Basic status, error info|
|v$propagation_sender||Detailed status info|
|v$propagation_receiver||Detailed status info|
|streams$_apply_process||Lists all defined apply processes|
|dba_apply||Basic status, error info|
|v$streams_apply_reader||Status of the apply reader|
|v$streams_apply_server||Status of apply server(s)|
|v$streams_apply_coordinator||Overall status, latency info|
|v$buffered_queues||View that displays the current and cumulative number of messages enqueued and spilled, for each buffered queue.|
|sys.streams$_apply_spill_msgs_part||Table that the apply process uses, to “spill” messages from large transactions to disk.|
|system.logmnr_restart_ckpt$||Table that holds capture process “checkpoint” information.|
My Capture, Propagate and apply processes were working fine until the server fial over. After a restart even though capture processes are enabled, they are stalled at status “INITIALIZING”. The Propagate and Apply were working fine.
Cloning is the process of copying an existing Oracle installation to a different location and then updating the copied installation to work in the new environment.
While working with streams 10g RAC, setup went well but source database propagation process won’t work with target database “strmrepl1″ and returned TNS “12514″ error.
There are diferent ways to collect performance related data
- Install Application Workload Repository (AWR) /Active Session History (ASH). Take AWR snapshots an hour or for desired interval
- Install and Configure STRMMON utility if you have 10gR2(Not available in 11g). Use Metalink Article “290605.1″
- Use UTL_SPADV as a alternative to STRMMON for > 11gR1
- Run Streams Healthcheckat each database: before, during , and after performance period of interest, available from Metalink:Article273674.1
I have a 2 node RAC -DG setup between 2 remote data centres. After building DataGuard between them I am now coming across stange latency stats. Read more…
Details below are for configuring a schema level replication using API DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS.
Use SQL Query below at Grid sysman database to locate growth of tablespaces over time. The returned results data could be used under EXCEL to generate graphs. Read more…
I have often used following procedure to create list of roles & corresponding users mapped into it. This was then used to email business owners as well as for user accesss auditing. Read more…
Capture process is an optional Streams client that is an Oracle background process that scans a database redo log to capture DML and DDL changes made to database objects. Read more…