https://dangkyhosting.com/huong-dan-cai-dat-zabbix-3-4-tren-centos7.html
Asia/Ho_Chi_Minh
Oracle – xóa log của job
Tham khảo: http://webgeest.blogspot.com/2015/06/purge-loghistory.html
dbms_scheduler log history – xóa thủ công
Today, I came across a SYSAUX tablespace that was asking for more space. It was about 12Gb, which seems a bit large to me. If you use the$ORACLE_HOME/rdbms/admin/awrinfo.sql , you will get a report of the occupants. Occupant JOB_SCHEDULER took over 11Gb space, which makes you think.
The default out-of-the-box maintainance job, uses the global attribute LOG_HISTORY to remove old dbms_scheduler job logging:
select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;
Now this is default 30 days, and can be set with:
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘log_history’,’15’);
But what if your maintainance jobs are failing for some reason (timeout/bugs?). You may need to clean it manually. The easy way to do this, is to use:
exec DBMS_SCHEDULER.PURGE_LOG(<days>, which_log=>’JOB_LOG’);
so for example:
exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>’JOB_LOG’);
But if the number of logs is very large, you have to do this carefully, step by step:
First determine the distribution of logging you have.
Select count(1) from dba_scheduler_job_log where log_date < sysdate – 100;
Make sure that you get an idea like:
| older than 300 days | 12 rows |
| older than 250 days | 12831 rows |
| older than 200 days | 438121 rows |
Now step by step clean it, by 5 or days at a time:
exec DBMS_SCHEDULER.PURGE_LOG(150,which_log=>’JOB_LOG’);
exec DBMS_SCHEDULER.PURGE_LOG(140,which_log=>’JOB_LOG’);
etc.
Until you can do:
exec DBMS_SCHEDULER.PURGE_LOG(15,which_log=>’JOB_LOG’);
Now you can set:
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘log_history’,’15’);
and watch your maintainance job (PURGE_LOG) over the next days.
What if your PURGE_LOG job doesn’t run? I had this, and a simple disable/enable was needed to get the right NEXT_RUN_DATE in dba_scheduler_jobs:
exec dbms_scheduler.disable(‘PURGE_LOG’);
exec dbms_scheduler.enable(‘PURGE_LOG’);
Now suppose that you cleaned up the job logging. After a few hours work, it’s almost done. Just run:
exec DBMS_SCHEDULER.PURGE_LOG();
Which should be fairly quick (maybe 2 minutes) now.
The tables need a shrink to release the space in the SYSAUX tablespace.
alter table sys.scheduler$_event_log enable row movement;
alter table sys.scheduler$_event_log shrink space cascade;
alter table sys.scheduler$_job_run_details enable row movement;
alter table sys.scheduler$_job_run_details space cascade;
Không truy cập được share folder trên window
So I followed all the advices and voodoo tricks on the thread that did not lead anywhere and found another one that finally fixed it.
My specs and situation: Windows 7 Home Premium cannot access the shared folders on any computer on my home network (same workgroup) especially not the ones off my NAS. Tried sync’ing the clocks, disabling IPv6, removing the 4to6 network adaptors (I had none), messing with secpol.msc (not available on W7 Home), the firewall is off, sharing is allowed and so on…
Finally what fixed it was re-enabling the “Client for Microsoft Networks” in the network adapter settings:
– go to Network Connections, right click the one you use -> “Properties”
– “Install” -> “Client” -> “Add”
click “Microsoft”, click OK, reboot and my shared folders appeared!!
Nguồn:
https://blog10viet.blogspot.com/2017/04/huong-dan-sua-loi-0x80070035-network.html
Bạn phải đảm bảo rằng các dịch vụ sau đây được Enable và để chế độ Automatic để máy tự động Enable vào các lần khởi động sau.
1. DCHP Client.
2. Homegroup Listener.
3. Homegroup Provider.
4. Link-Layer Topology Discovery Mapper.
5. NetTcp Port SHaring Service.
6. Network Connections.
7. Network List Service.
8. Network Location Awareness.
9. TCP/IP NetBIOS Helper.
Cài đặt ruby, và chạy project trên github trên centos 7
1. Kiểm tra mạng của centos7.
Gõ nmcli d để kiểm tra nhanh các card mạng
Lệnh nmtui để mở giao diện cấu hình
Sau đó cần restart service:
# systemctl restart network
Hoặc
# service network restart
Lệnh xem IP
# IP addr
2. Cài đặt Ruby
Trước khi cài đặt nên dùng bitvise ssh
a. Chúng ta cần cài đặt toàn bộ các gói cần thiết cho việc cài đặt ruby
# yum install gcc-c++ patch readline readline-devel zlib zlib-devel \ libyaml-devel libffi-devel openssl-devel make \ bzip2 autoconf automake libtool bison iconv-devel sqlite-devel
b. Sau đó chúng ta cài đặt rvm
# curl -sSL https://rvm.io/mpapis.asc | gpg --import - # curl -L get.rvm.io | bash -s stable c. Vào môi trường của rvm
# source /etc/profile.d/rvm.sh # rvm reload # rvm requirements run
d. Cài đặt ruby # rvm install 2.4.2 e. Nếu có nhiều version ruby được cài đặt thì dùng default version: # rvm list
# rvm use 2.4.2 --default
f. kiểm tra phiên bản ruby đã cài # ruby --version g. cài đặt bundler # gem install bundler
3. Cài đặt git
# yum install git
4. Dùng lệnh clone
Trước đó nên dùng lệnh > mkdir để tạo thư mục respo, sau đó cd vào thư mục này
a. Dựa vào docs https://github.com/lord/slate ta cài như sau:
#git clone https://github.com/lord/slate.git
b. Sau khi clone xong cd vào thư mục slate
# bundle install
c. chỉnh sửa port để chạy thử ứng dụng
# vi config.rb
d. Lưu ý để chạy ứng dụng web, cần load được thêm js runtime, do đó cần cài đặt nodejs
# yum install nodejs
e. Lưu ý cần mở firewall cho port ở centos 7 nếu muốn truy cập từ máy khác
# firewall-cmd --get-active-zones
# firewall-cmd --zone=dmz --add-port=2888/tcp --permanent or # firewall-cmd --zone=public --add-port=2888/tcp --permanent # firewall-cmd --reload
Sử lý sự cố không truy cập share folder ở window 10, do update hỏng
Error: you-cant-access-this-shared-folder-because-your-organizations-security-policies-block-unauthenticated-guest-access
Bị lỗi trên cần chỉnh registry:
Default Registry Value: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters] "AllowInsecureGuestAuth"=dword:0 Configured Registry Value: [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanWorkstation\Parameters] "AllowInsecureGuestAuth"=dword:1
Nếu không được cần chỉnh thêm policy:
Oracle 12c, xử lý sự cố file bị corrupt
Tham khảo:
http://www.dba-oracle.com/t_repair_corrupt_blocks.htm
https://oracle-base.com/articles/misc/detect-and-correct-corruption
https://blog.dbi-services.com/rman-backup-is-failing-due-to-qcorrupt-blocksq/
Thường gặp lỗi này là do ổ đĩa bị lỗi
Oracle cung cấp view đê chúng ta kiểm tra
select * from v$database_block_corruption
Hoặc nếu ta biết chính xác file và block bị lỗi có thể chạy query sau để biết chính xác khối bị lỗi:
select
relative_fno,
owner,
segment_name,
segment_type
from
dba_extents
where
file_id = 6
and
437 between block_id and block_id + blocks – 1;
Lệnh này mapping luôn ra segment bị lỗi
SELECT DISTINCT owner, segment_name
FROM v$database_block_corruption dbc
JOIN dba_extents e ON dbc.file# = e.file_id AND dbc.block# BETWEEN e.block_id and e.block_id+e.blocks-1
ORDER BY 1,2;
Như mô tả của link
RMAN > validate check logical database
Thấy có dòng validate found one or more corrupt blocks
RMAN > blockrecover corruption list
Một lần nữa cùng nguyên nhân trên, ta lặp lại validate check logical database & blockrecover corruption list trong rman để giải quyết vấn đề
Cập nhật quan trọng trong trường hợp không có backup
Ta cần dùng lệnh scan toàn bộ database oracle tìm corrupt:
sau khi thực hiện lệnh này thì lỗi corrupt sẽ được tổng hợp trong view:
select * from V$DATABASE_BLOCK_CORRUPTION
Thường khối corrupt sẽ liên quan đến table hoặc index cụ thể, ta có lệnh sau để tổng hợp ra chúng:
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#
, greatest(e.block_id, c.block#) corr_start_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)
– greatest(e.block_id, c.block#) + 1 blocks_corrupted
, null description
FROM dba_extents e, v$database_block_corruption c
WHERE e.file_id = c.file#
AND e.block_id <= c.block# + c.blocks – 1
AND e.block_id + e.blocks – 1 >= c.block#
UNION
SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file#
, header_block corr_start_block#
, header_block corr_end_block#
, 1 blocks_corrupted
, ‘Segment Header’ description
FROM dba_segments s, v$database_block_corruption c
WHERE s.header_file = c.file#
AND s.header_block between c.block# and c.block# + c.blocks – 1
UNION
SELECT null owner, null segment_type, null segment_name, null partition_name, c.file#
, greatest(f.block_id, c.block#) corr_start_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block#
, least(f.block_id+f.blocks-1, c.block#+c.blocks-1)
– greatest(f.block_id, c.block#) + 1 blocks_corrupted
, ‘Free Block’ description
FROM dba_free_space f, v$database_block_corruption c
WHERE f.file_id = c.file#
AND f.block_id <= c.block# + c.blocks – 1
AND f.block_id + f.blocks – 1 >= c.block#
order by file#, corr_start_block#;
Từ đó ta sẽ cần tạo lại table hoặc index để fix lỗi
Để kiểm tra fix được lỗi hay chưa ta nên lặp lại quy trình check
RMAN> BACKUP CHECK LOGICAL VALIDATE DATABASE;
Thật không may là việc chặn lại không thể sử dụng vì nó đòi hỏi một phiên bản Oracle Enterprise Edition và chúng tôi đang chạy Oracle Standard Edition.
Tôi cũng đã cố gắng để sửa chữa khối này với dbms_repair gói, nhưng dbms_repair không có bất kỳ tùy chọn để sửa chữa một khối bị hỏng có sản phẩm nào.
Sau một số phân tích sâu hơn, tôi quyết định để lại khối trống rỗng bị rạn nứt này vào cơ sở dữ liệu, như một khối không sử dụng bị hỏng không có hại. Khi Oracle sẽ sử dụng lại khối này để gán nó cho một phân đoạn, Oracle sẽ tự động định dạng lại khối này, và vấn đề sẽ được giải quyết.
Sao chép database trong mysql
1) Ở server nguồn:
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysqldump -u root -p -R –databases
dbname > c:\exports\dbname.mysql
Tham số:
-R: export function và procedure
–databases: chỉ only
2) Ở server đích
C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p dbname < “C:\
imports\dbname.mysql”
oracle 12c, khong the su dung system rollback
Tham khảo:
http://dbarohit.blogspot.com/2013/08/ora-01552-cannot-use-system-rollback.html
https://www.akadia.com/services/ora_crash_recovery.html
Lỗi khi chạy rman clone lỗi
ORA-01552: cannot use system rollback segment for non-system tablespace
Recently I have met with an issue where the undo tablespace of the Prod database is got corrupted and we required to create the new undo tablespace coz we had no undo backup. While creating the undo tablespace we got the following error:
SQL> create undo tablespace undotbs2
2 datafile ‘/u01/app/oracle/oradata/db1/undotbs01.dbf’ size 100m;
create undo tablespace undotbs2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace ‘GGATE’
ORA-06512: at line 993
ORA-01552: cannot use system rollback segment for non-system tablespace ‘GGATE’
I have implemented the same test case over my DEV server which was identical in nature with the Production servers.
Following are Environment.
Dev1 database: source.xxx.com
Dev2 database: target.xxx.com
Database Version: 11203
Golden gate Version: 11.1
Golden Gate schema: GGATE
Replication Method: DDL ACTIVE-ACTIVE Replication
I moved the undo datafile at OS level.
[oracle@source ~]$ cd /u01/app/oracle/oradata/db1/
[oracle@source db1]$ ls -al undo*
-rw-r—– 1 oracle oinstall 188751872 Aug 1 11:59 undotbs01.dbf
[oracle@source ~]$mv undotbs01.dbf undotbs01.dbf.bkp
SQL> startup
ORACLE instance started.
Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 1006635164 bytes
Database Buffers 671088640 bytes
Redo Buffers 11636736 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘/u01/app/oracle/oradata/db1/undotbs01.dbf’
Excerpts from alert log.
ALTER DATABASE OPEN
Errors in file /u01/app/oracle/diag/rdbms/db1/db1/trace/db1_dbw0_2626.trc:
ORA-01157: cannot identify/lock data file 3 – see DBWR trace file
ORA-01110: data file 3: ‘/u01/app/oracle/oradata/db1/undotbs01.dbf’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
Now change the undo management parameter to MANUAL and then bounce the database.
SQL> alter system set undo_management=manual scope=spfile;
System altered.
SQL> shut immediate
SQL> startup mount
SQL> select FILE#,NAME,STATUS from v$datafile
2 ;
FILE# NAME STATUS
———- ————————————————– ——-
1 /u01/app/oracle/oradata/db1/system01.dbf SYSTEM
2 /u01/app/oracle/oradata/db1/sysaux01.dbf ONLINE
3 /u01/app/oracle/oradata/db1/undotbs01.dbf ONLINE
4 /u01/app/oracle/oradata/db1/users01.dbf ONLINE
5 /u01/app/oracle/oradata/db1/ggate_01.dbf ONLINE
Lets drop the undo tablespace data file from database level in offline state.
SQL> alter database datafile ‘/u01/app/oracle/oradata/db1/undotbs01.dbf’ offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> create undo tablespace undotbs2
2 datafile ‘/u01/app/oracle/oradata/db1/undotbs02.dbf’ size 100m;
create undo tablespace undotbs2
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace ‘GGATE’
ORA-06512: at line 993
ORA-01552: cannot use system rollback segment for non-system tablespace ‘GGATE’
SQL> drop tablespace undotbs1 including contents;
drop tablespace undotbs1 including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01552: cannot use system rollback segment for non-system tablespace ‘GGATE’
ORA-06512: at line 993
ORA-01552: cannot use system rollback segment for non-system tablespace ‘GGATE’
SQL> !oerr ora 01552
01552, 00000, “cannot use system rollback segment for non-system tablespace ‘%s'”
// *Cause: Tried to use the system rollback segment for operations involving
// non-system tablespace. If this is a clone database then this will
// happen when attempting any data modification outside of the system
// tablespace. Only the system rollback segment can be online in a
// clone database.
// *Action: Create one or more private/public segment(s), shutdown and then
// startup again. May need to modify the INIT.ORA parameter
// rollback_segments to acquire private rollback segment. If this is
// a clone database being used for tablspace point in time recovery
// then this operation is not allowed. If the non-system tablespace
// has AUTO segment space management, then create an undo tablespace.
SQL> select segment_name, status from dba_rollback_segs;
SEGMENT_NAME STATUS
—————————— —————-
SYSTEM ONLINE
_SYSSMU10_2490256178$ OFFLINE
_SYSSMU9_3593450615$ OFFLINE
_SYSSMU8_1909280886$ OFFLINE
_SYSSMU7_1924883037$ OFFLINE
_SYSSMU6_2460248069$ OFFLINE
_SYSSMU5_3787622316$ OFFLINE
_SYSSMU4_1455318006$ OFFLINE
_SYSSMU3_2210742642$ OFFLINE
_SYSSMU2_4228238222$ OFFLINE
_SYSSMU1_3138885392$ OFFLINE
11 rows selected.
SQL> select * from v$rollname;
USN NAME
———- ————————————————–
0 SYSTEM
Solution
To get rid of the above issues we need to disable the system triggers and then create the new undo tablespace. Once the tablespace is created now enable those triggers.
Make sure database is in OPEN mode.
SQL> alter system set “_system_trig_enabled” = FALSE;
System altered.
SQL> alter trigger sys.cdc_alter_ctable_before DISABLE;
Trigger altered.
SQL> alter trigger sys.cdc_create_ctable_after DISABLE;
Trigger altered.
SQL> alter trigger sys.cdc_create_ctable_before DISABLE;
Trigger altered.
SQL> alter trigger sys.cdc_drop_ctable_before DISABLE;
Trigger altered.
SQL> create undo tablespace UNDOTBS2
2 datafile ‘/u01/app/oracle/oradata/db1/undotbs02.dbf’ size 200m;
Tablespace created.
SQL> alter system set undo_tablespace=UNDOTBS2 scope=spfile;
System altered.
SQL> drop tablespace UNDOTBS1 including contents;
Tablespace dropped.
SQL> alter trigger sys.cdc_alter_ctable_before ENABLE;
Trigger altered.
SQL> alter trigger sys.cdc_create_ctable_after ENABLE;
Trigger altered.
SQL> alter trigger sys.cdc_create_ctable_before ENABLE;
Trigger altered.
SQL> alter trigger sys.cdc_drop_ctable_before ENABLE;
Trigger altered.
SQL> alter system set “_system_trig_enabled” = TRUE;
System altered.
SQL> alter system set undo_management=AUTO scope=spfile;
System altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1690705920 bytes
Fixed Size 1345380 bytes
Variable Size 1006635164 bytes
Database Buffers 671088640 bytes
Redo Buffers 11636736 bytes
Database mounted.
Database opened.
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
SQL> select name,status from v$datafile
2 where name like ‘%undo%’;
NAME STATUS
————————————————– ——-
/u01/app/oracle/oradata/db1/undotbs02.dbf ONLINE
oracle 12c duplicate database using rman
Tham khảo:
https://blog.pythian.com/duplicate-from-active-database-using-rman-step-by-step/
https://oracle-base.com/articles/11g/duplicate-database-using-rman-11gr2
*Lưu ý:
1) Database đã chạy ở chế độ archivelog
SQL> startup mount ORACLE instance started. SQL> alter database archivelog; Database altered. SQL> alter database open; Database altered.
2) Nếu database chưa chạy ở chế độ archivelog thì cần tạo ra backup để clone.
Tham khảo: https://www.thegeekstuff.com/2013/08/oracle-rman-backup/
Lệnh backup full:
RMAN> BACKUP AS BACKUPSET DATABASE
RMAN> BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG; (kết hợp archivelog) *Lưu ý trước khi clone lên bật archivelog để khi có thay đổi trong database thì sẽ đẩy sang bản clone.
Dùng rman kết nối tới source db (target) va aulixi…
*Lưu ý:
copy đúng folder to folder
Dùng oradim để new instance…
đặt password sys trùng với database cũ
CMD> orapwd file=[ORACLE_HOME]/dbs/orapw[SID] password=[sys_password] Hoặc chạy mặc định CMD> orapwd password = ""
Sau đó startup nomount
RMAN> DUPLICATE DATABASE TO DB11G
FROM ACTIVE DATABASE
NOFILENAMECHECK;
Done
Nếu như database clone có same name
thì cần chạy kiểu như sau
run {
duplicate database ONETOURP DBID 35608629
to ONETOURP
noredo
nofilenamecheck;
}
*Lưu ý cần chỉnh registry nếu database không tự start
Fix loi window khong vao duoc mang, do virus disable settings proxy
HKEY_LOCAL_MACHINE\Software\Policies\Microsoft\Windows\
CurrentVersion\Internet Settings -> ProxySettingsPerUser
Sống từng ngày https://songtungngay.com 